Re: [cx-oracle-users] insert using executemanyprepared
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] insert using executemanyprepared
|
From: Chris D. <cdu...@ya...> - 2005-03-21 19:01:28
|
Anthony,
It works a treat.
Thanks very much,
Chris
PS I'll give executemanyprepared a miss for now but I may come back for help later.
--- Chris Dunscombe <cdu...@ya...> wrote:
> Thanks very much for that. I'll give it a go when I get home to-night.
>
> Chris
> --- Anthony Tuininga <ant...@gm...> wrote:
>
> > First, your code demonstrates a problem with cx_Oracle that appears to
> > be rather subtle. I'm not sure exactly what the problem is yet but
> > I'll look into it a bit more yet. However, I do have a solution for
> > you that solves your problem. If you set your arraysize large enough
> > to accomodate the amount of data you are inserting, your problem goes
> > away. In other words
> >
> > cursor.arraysize = len(listDict)
> >
> > BTW, I tried this code with cx_Oracle 4.0.1 and it fails as well but
> > for a different reason (arraysize not large enough) so I'm not sure
> > why it works for you.
> >
> > For performance purposes, executemanyprepared() definitely helps but
> > another solution will get you most of that performance while at the
> > same time making your code much easier to read. Your example can be
> > rewritten as follows:
> >
> > # this part is identical except that the massaging of the list is unnecessary
> > cur1 = db1.cursor()
> > db1 = cx_Oracle.connect("regt/test@sxl920d2")
> > cur1 = db1.cursor()
> > cur1.execute("select * from binary_source")
> > data = cur1.fetchall()
> >
> > # this is the part that differs
> > curInsert = db1.cursor()
> > curInsert.arraysize = len(data) # this shouldn't be necessary but
> > at the moment it is
> > curInsert.prepare("insert into WAREHOUSES_STOCK
> > (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND) values (:1,:2,:3)")
> > curInsert.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.BINARY, cx_Oracle.NUMBER)
> > curInsert.executemany(None, data) # None means use the prepared statement
> > # or you can do curInsert.executemany(curInsert.statement, data),
> > whichever you find more appealing
> >
> > cx_Oracle 4.1 allows binding by position. Thus, you can simply perform
> > the fetch() on the one cursor and the executemany() on the other
> > cursor without having to massage it. This should provide all of the
> > performance you require. If you still think you need help with
> > executemanyprepared() I can try to help you on that front as well.
> >
> > Hope this helps.
> >
> > On Sat, 19 Mar 2005 06:17:17 -0800 (PST), Chris Dunscombe
> > <cdu...@ya...> wrote:
> > > All,
> > >
> > > Upto now I've been using executemany to bulk insert data. Upto 4.0.1 this has been fine but
> > now
> > > with 4.1.0 it doesn't seem to work with RAW columns, see below for a simple example:
> > >
> > > import cx_Oracle
> > > db1 = cx_Oracle.connect("regt/test@sxl920d2")
> > > cur1 = db1.cursor()
> > > cur1.execute("select * from binary_source")
> > > data = cur1.fetchall()
> > > listDict = []
> > > for row in data:
> > > tempDict = {}
> > > x = 1
> > > for item in row:
> > > tempDict["arg" + str(x)] = item
> > > x = x + 1
> > > listDict.append(tempDict)
> > >
> > > curInsert = db1.cursor()
> > > curInsert.setinputsizes( arg1 = cx_Oracle.NUMBER, arg2 = cx_Oracle.BINARY, arg3 =
> > > cx_Oracle.NUMBER)
> > > insStr = "insert into WAREHOUSES_STOCK (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND) values
> > > (:arg1,:arg2,:arg3)"
> > > curInsert.prepare(insStr)
> > > curInsert.executemany(insStr, listDict)
> > >
> > > This gives the following error using 4.1.0, works OK for 4.0.1 (Oracle 9.2 on Linux):
> > >
> > > Traceback (most recent call last):
> > > File "binary2.py", line 19, in ?
> > > curInsert.executemany(insStr, listDict)
> > > cx_Oracle.DatabaseError: ORA-01465: invalid hex number
> > >
> > > As I'm concerned about performance I've look at using executemanyprepared (looked at
> > copydata.py
> > > in cx_OracleTools) but haven't managed to get it to work.
> > >
> > > Pls does someone have a simple step-by-step example of using executemanyprepared with an
> > insert
> > > that could be used in my example above to replace the executemany.
> > >
> > > Thanks very much,
> > >
> > > Chris
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Sports - Sign up for Fantasy Baseball.
> > > http://baseball.fantasysports.yahoo.com/
> > >
> > > -------------------------------------------------------
> > > SF email is sponsored by - The IT Product Guide
> > > Read honest & candid reviews on hundreds of IT Products from real users.
> > > Discover which products truly live up to the hype. Start reading now.
> > > http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real users.
> > Discover which products truly live up to the hype. Start reading now.
> > http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs
|
