Re: [cx-oracle-users] insert using executemanyprepared

[Chris D. <cdu...@ya...>]

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