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