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

[Anthony T. <ant...@gm...>]

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
>