cx-oracle-users

[cx-oracle-users] insert using executemanyprepared

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

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/

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
>

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 

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

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

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