Thread: Re: [cx-oracle-users] Help with fetchraw
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Chris D. <cdu...@ya...> - 2005-05-13 08:11:37
|
Leith, I didn't mention that for 1) the target table is in a separate database and using database links isn't an option (although I wish it was) due to reasons that are too lengthy to go into here. Hence PL/SQL isn't really viable. So I'd still like to look at cursor.fetchraw() Now for 2) you maybe right that PL/SQL could be used but I'll still have to write out to file using Python as util_file can only write to files that the database server can "see" which isn't always the case for my app. Thanks for the idea, Chris --- Leith Parkin <lei...@gm...> wrote: > This sounds like something better done in PL/SQL? > > Regards, > > Leith > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've > looked > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out. > > If possible I'd like a simple full example to show me the way. The context is: > > > > 1) Retrieve rows from source table and insert into a target table which has exactly the same > > structure. > > > > 2) Retrieve rows from source table, add some column formatting and write out to a file. > > > > Thanks very much, > > > > Chris Dunscombe > > > > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some > thinking > > and work to do on which is best for performance and how best to cope with different datatypes, > > particularly Longs (what a pain they are!!) > > > > __________________________________ > > Yahoo! Mail Mobile > > Take Yahoo! Mail with you! Check email on your mobile phone. > > http://mobile.yahoo.com/learn/mail > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click > > _______________________________________________ > > cx-oracle-users mailing list > > cx-...@li... > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by Oracle Space Sweepstakes > Want to be the first software developer in space? > Enter now for the Oracle Space Sweepstakes! > http://ads.osdn.com/?ad_ids93&alloc_id281&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 |
|
From: Chris D. <cdu...@ya...> - 2005-05-17 09:54:19
|
--- Anthony Tuininga <ant...@gm...> wrote:
> 1) You should be able to do something like this without the need to
> descend into fetchraw().
>
> import cx_Oracle
>
> fromConnection = cx_Oracle.Connection("user/pw@tns")
> toConnection = cx_Oracle.Connection("user/pw@tns")
> fromCursor = fromConnection.cursor()
> fromCursor.arraysize = 250 # set as desired
> toCursor = toConnection.cursor()
> toCursor.arraysize = 250 # set as desired, same as fromCursor
>
> fromCursor.execute("some query")
> toCursor.setinputsizes() # set things up if nulls are allowed
> while True:
> data = fromCursor.fetchmany()
> if not data:
> break
> toCursor.executemany("some insert statement", data)
> toConnection.commit() # if desired
>
> That should do it and should perform quite well. The fetchraw(),
> bindvar.copy() and executemanyprepared() trio were created solely to
> avoid the overhead of creating Python objects for the data. Its more
> complicated than the above code but I can give that as an example as
> well if you really want it.
>
I've done essentially the same as you've got above and yes it works fine with pretty good
performance. I'm just trying to improve it further as there are cases when the app will be running
a number of parallel streams each selecting and inserting millions of rows in a fixed window so
saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd like a
fetchraw example so I can benchmark it against my current approach. I'll then post the results so
everyone can see the general sort of performance differences between the two.
> 2) I'm not sure exactly what you are intending with this so I can't
> see why the simple
>
> for row in cursor:
> process_the_row()
>
> isn't going to work or isn't desirable. Perhaps you could give more
> detail? Thanks.
The "problem" with:
for row in cursor:
process_the_row()
is that process_the_row() is essentially:
for col in row:
rowOut = rowOut.append(format_col(col))
rowOutString = "".join(rowOut)
which creates quite an overhead when the tables have lots of columns (>40).
So I'm looking at anything that could help speed things up (it's quite possible that in this case
fetchraw() won't make much of an impact compared to the process_the_row() code) This is why I'm
looking the None as Null returning "" stuff so as to reduce the complexity of "format_col()" and
hence CPU cost.
BTW I've actually put the process_the_row and format_col() code in-line as per
http://www.python.org/doc/faq/programming.html and associated performance guidelines to avoid the
function call overhead and this reduced the Python CPU usage by almost 20% on my standard perf
test.
Thanks again for your much appreciated help,
Chris
> On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> looked
> > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > If possible I'd like a simple full example to show me the way. The context is:
> >
> > 1) Retrieve rows from source table and insert into a target table which has exactly the same
> > structure.
> >
> > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> >
> > Thanks very much,
> >
> > Chris Dunscombe
> >
> > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> thinking
> > and work to do on which is best for performance and how best to cope with different datatypes,
> > particularly Longs (what a pain they are!!)
> >
> > __________________________________
> > Yahoo! Mail Mobile
> > Take Yahoo! Mail with you! Check email on your mobile phone.
> > http://mobile.yahoo.com/learn/mail
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
|
|
From: Anthony T. <ant...@gm...> - 2005-05-17 13:27:07
|
On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
>=20
> --- Anthony Tuininga <ant...@gm...> wrote:
> > 2) I'm not sure exactly what you are intending with this so I can't
> > see why the simple
> >
> > for row in cursor:
> > process_the_row()
> >
> > isn't going to work or isn't desirable. Perhaps you could give more
> > detail? Thanks.
>=20
> The "problem" with:
>=20
> for row in cursor:
> process_the_row()
>=20
> is that process_the_row() is essentially:
>=20
> for col in row:
> rowOut =3D rowOut.append(format_col(col))
> rowOutString =3D "".join(rowOut)
>=20
> which creates quite an overhead when the tables have lots of columns (>40=
).
You should be able to rewrite "process_the_row()" as the following if
all you are intending to do is replace None with ""
for row in cursor:
rowOutString =3D "".join([v or "" for v in row])
if all you are fetching is strings. There is a flag for turning
numbers into strings in all cases as well. If you need something more
complicated than the above (multiple data types) then I'll need more
information. :-)
> So I'm looking at anything that could help speed things up (it's quite po=
ssible that in this case
> fetchraw() won't make much of an impact compared to the process_the_row()=
code) This is why I'm
> looking the None as Null returning "" stuff so as to reduce the complexit=
y of "format_col()" and
> hence CPU cost.
>=20
> BTW I've actually put the process_the_row and format_col() code in-line a=
s per
> http://www.python.org/doc/faq/programming.html and associated performance=
guidelines to avoid the
> function call overhead and this reduced the Python CPU usage by almost 20=
% on my standard perf
> test.
>=20
> Thanks again for your much appreciated help,
You're welcome. I hope to get the example of fetchraw() to you later
today but I expect to be interrupted shortly with a task that might
take all day so don't be alarmed if you don't hear from me right away.
I haven't forgotten about it. :-)
> Chris
> > On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > I'm looking to use cursor.fetchraw to help improve performance in an =
app I'm writing. I've
> > looked
> > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out=
.
> > > If possible I'd like a simple full example to show me the way. The co=
ntext is:
> > >
> > > 1) Retrieve rows from source table and insert into a target table whi=
ch has exactly the same
> > > structure.
> > >
> > > 2) Retrieve rows from source table, add some column formatting and wr=
ite out to a file.
> > >
> > > Thanks very much,
> > >
> > > Chris Dunscombe
> > >
> > > PS Thanks for all the input re my previous posting on Nulls and None.=
I've still got some
> > thinking
> > > and work to do on which is best for performance and how best to cope =
with different datatypes,
> > > particularly Longs (what a pain they are!!)
> > >
> > > __________________________________
> > > Yahoo! Mail Mobile
> > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > http://mobile.yahoo.com/learn/mail
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id=16344&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Chris D. <cdu...@ya...> - 2005-05-17 15:28:01
|
--- Anthony Tuininga <ant...@gm...> wrote: > On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > --- Anthony Tuininga <ant...@gm...> wrote: > > > 2) I'm not sure exactly what you are intending with this so I can't > > > see why the simple > > > > > > for row in cursor: > > > process_the_row() > > > > > > isn't going to work or isn't desirable. Perhaps you could give more > > > detail? Thanks. > > > > The "problem" with: > > > > for row in cursor: > > process_the_row() > > > > is that process_the_row() is essentially: > > > > for col in row: > > rowOut = rowOut.append(format_col(col)) > > rowOutString = "".join(rowOut) > > > > which creates quite an overhead when the tables have lots of columns (>40). > > You should be able to rewrite "process_the_row()" as the following if > all you are intending to do is replace None with "" > > for row in cursor: > rowOutString = "".join([v or "" for v in row]) > > if all you are fetching is strings. There is a flag for turning > numbers into strings in all cases as well. If you need something more > complicated than the above (multiple data types) then I'll need more > information. :-) There are multiple datatypes typically varchar,number,date and sometimes raw and long. > > So I'm looking at anything that could help speed things up (it's quite possible that in this > case > > fetchraw() won't make much of an impact compared to the process_the_row() code) This is why > I'm > > looking the None as Null returning "" stuff so as to reduce the complexity of "format_col()" > and > > hence CPU cost. > > > > BTW I've actually put the process_the_row and format_col() code in-line as per > > http://www.python.org/doc/faq/programming.html and associated performance guidelines to avoid > the > > function call overhead and this reduced the Python CPU usage by almost 20% on my standard perf > > test. > > > > Thanks again for your much appreciated help, > > You're welcome. I hope to get the example of fetchraw() to you later > today but I expect to be interrupted shortly with a task that might > take all day so don't be alarmed if you don't hear from me right away. > I haven't forgotten about it. :-) No worries, I can wait. Thanks again, Chris > > > Chris > > > On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've > > > looked > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out. > > > > If possible I'd like a simple full example to show me the way. The context is: > > > > > > > > 1) Retrieve rows from source table and insert into a target table which has exactly the > same > > > > structure. > > > > > > > > 2) Retrieve rows from source table, add some column formatting and write out to a file. > > > > > > > > Thanks very much, > > > > > > > > Chris Dunscombe > > > > > > > > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some > > > thinking > > > > and work to do on which is best for performance and how best to cope with different > datatypes, > > > > particularly Longs (what a pain they are!!) > > > > > > > > __________________________________ > > > > Yahoo! Mail Mobile > > > > Take Yahoo! Mail with you! Check email on your mobile phone. > > > > http://mobile.yahoo.com/learn/mail > > > > > > > > ------------------------------------------------------- > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > > Want to be the first software developer in space? > > > > Enter now for the Oracle Space Sweepstakes! > > > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click > > > > _______________________________________________ > > > > cx-oracle-users mailing list > > > > cx-...@li... > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > > > > > > > ------------------------------------------------------- > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > Want to be the first software developer in space? > > > Enter now for the Oracle Space Sweepstakes! > > > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click > > > _______________________________________________ > > > cx-oracle-users mailing list > > > cx-...@li... > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > __________________________________ > > Yahoo! Mail Mobile > > Take Yahoo! Mail with you! Check email on your mobile phone. > > http://mobile.yahoo.com/learn/mail > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click > > _______________________________________________ > > cx-oracle-users mailing list > > cx-...@li... > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by Oracle Space Sweepstakes > Want to be the first software developer in space? > Enter now for the Oracle Space Sweepstakes! > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail |
|
From: Anthony T. <ant...@gm...> - 2005-05-17 16:00:30
|
On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote: >=20 > --- Anthony Tuininga <ant...@gm...> wrote: >=20 > > On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > > > --- Anthony Tuininga <ant...@gm...> wrote: > > > > 2) I'm not sure exactly what you are intending with this so I can't > > > > see why the simple > > > > > > > > for row in cursor: > > > > process_the_row() > > > > > > > > isn't going to work or isn't desirable. Perhaps you could give more > > > > detail? Thanks. > > > > > > The "problem" with: > > > > > > for row in cursor: > > > process_the_row() > > > > > > is that process_the_row() is essentially: > > > > > > for col in row: > > > rowOut =3D rowOut.append(format_col(col)) > > > rowOutString =3D "".join(rowOut) > > > > > > which creates quite an overhead when the tables have lots of columns = (>40). > > > > You should be able to rewrite "process_the_row()" as the following if > > all you are intending to do is replace None with "" > > > > for row in cursor: > > rowOutString =3D "".join([v or "" for v in row]) > > > > if all you are fetching is strings. There is a flag for turning > > numbers into strings in all cases as well. If you need something more > > complicated than the above (multiple data types) then I'll need more > > information. :-) >=20 > There are multiple datatypes typically varchar,number,date and sometimes = raw and long. Ok. So what exactly does the process_row() method do? If you give the full body I might be able to suggest something that would work better. Or if you'd rather play with it on your own, that's fine too. :-) |
|
From: Anthony T. <ant...@gm...> - 2005-05-18 06:08:06
|
On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
>=20
> --- Anthony Tuininga <ant...@gm...> wrote:
> > 1) You should be able to do something like this without the need to
> > descend into fetchraw().
> >
> > import cx_Oracle
> >
> > fromConnection =3D cx_Oracle.Connection("user/pw@tns")
> > toConnection =3D cx_Oracle.Connection("user/pw@tns")
> > fromCursor =3D fromConnection.cursor()
> > fromCursor.arraysize =3D 250 # set as desired
> > toCursor =3D toConnection.cursor()
> > toCursor.arraysize =3D 250 # set as desired, same as fromCursor
> >
> > fromCursor.execute("some query")
> > toCursor.setinputsizes() # set things up if nulls are allowed
> > while True:
> > data =3D fromCursor.fetchmany()
> > if not data:
> > break
> > toCursor.executemany("some insert statement", data)
> > toConnection.commit() # if desired
> >
> > That should do it and should perform quite well. The fetchraw(),
> > bindvar.copy() and executemanyprepared() trio were created solely to
> > avoid the overhead of creating Python objects for the data. Its more
> > complicated than the above code but I can give that as an example as
> > well if you really want it.
> >
>=20
> I've done essentially the same as you've got above and yes it works fine =
with pretty good
> performance. I'm just trying to improve it further as there are cases whe=
n the app will be running
> a number of parallel streams each selecting and inserting millions of row=
s in a fixed window so
> saving 5% - 15% of the Python CPU time within this loop is definately wor=
th doing. I'd like a
> fetchraw example so I can benchmark it against my current approach. I'll =
then post the results so
> everyone can see the general sort of performance differences between the =
two.
The following is the code for using fetchraw(). My own preliminary
results show that in fact it is slower than the original method above
that I showed you and is more complicated to boot. The fact that the
data can be passed from the one cursor to the other directly and that
the bindvar.copy() command must be called for each bind variable for
each row makes the overhead of fetching Python objects less than the
overhead of calling the bindvar.copy() methods. The fetchraw() method
was considerably faster when lists of dictionaries needed to be passed
through to executemany().
import cx_Oracle
sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
sourceCursor =3D sourceConnection.cursor()
sourceCursor.arraysize =3D 250
sourceVars =3D sourceCursor.execute("select * from testdata_source")
targetConnection =3D cx_Oracle.Connection("user/pw@tns")
targetCursor =3D targetConnection.cursor()
targetCursor.execute("truncate table testdata_copy")
targetCursor.arraysize =3D 250
targetVars =3D targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DATET=
IME,
cx_Oracle.NUMBER, cx_Oracle.STRING)
targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
vars =3D zip(sourceVars, targetVars)
indexes =3D range(sourceCursor.arraysize)
while True:
rowsFetched =3D sourceCursor.fetchraw()
print "fetched", rowsFetched, "rows"
if not rowsFetched:
break
if rowsFetched !=3D sourceCursor.arraysize:
indexes =3D range(rowsFetched)
for index in indexes:
for sourceVar, targetVar in vars:
targetVar.copy(sourceVar, index, index)
targetCursor.executemanyprepared(rowsFetched)
targetConnection.commit()
Another method which also works but only when the exact data is being
replicated from one cursor to another is the following. It is faster
than both methods but limited in usefulness of course.
import cx_Oracle
sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
sourceCursor =3D sourceConnection.cursor()
sourceCursor.arraysize =3D 250
sourceVars =3D sourceCursor.execute("select * from testdata_source")
targetConnection =3D cx_Oracle.Connection("user/pw@tns")
targetCursor =3D targetConnection.cursor()
targetCursor.execute("truncate table testdata_copy")
targetCursor.arraysize =3D 250
targetVars =3D targetCursor.setinputsizes(*sourceVars)
targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
while True:
rowsFetched =3D sourceCursor.fetchraw()
print "fetched", rowsFetched, "rows"
if not rowsFetched:
break
targetCursor.executemanyprepared(rowsFetched)
targetConnection.commit()
Hope this helps,
Anthony
|
|
From: Leith P. <lei...@gm...> - 2005-05-18 07:44:46
|
Hi Anthony,
Chris mentioned in an earlier post that the select results are being
used to create and insert into another database, not Oracle, otherwise
you wouldnt even use Python, just insert into select syntax.
The problem as I understand it is that Oracle empty varchar2's are
returned as NULL rather than the python ''. Either way i think Chris
has enough information to do what he has been looking for now.
Regards,
Leith
On 5/18/05, Anthony Tuininga <ant...@gm...> wrote:
> On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> >
> > --- Anthony Tuininga <ant...@gm...> wrote:
> > > 1) You should be able to do something like this without the need to
> > > descend into fetchraw().
> > >
> > > import cx_Oracle
> > >
> > > fromConnection =3D cx_Oracle.Connection("user/pw@tns")
> > > toConnection =3D cx_Oracle.Connection("user/pw@tns")
> > > fromCursor =3D fromConnection.cursor()
> > > fromCursor.arraysize =3D 250 # set as desired
> > > toCursor =3D toConnection.cursor()
> > > toCursor.arraysize =3D 250 # set as desired, same as fromCursor
> > >
> > > fromCursor.execute("some query")
> > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > while True:
> > > data =3D fromCursor.fetchmany()
> > > if not data:
> > > break
> > > toCursor.executemany("some insert statement", data)
> > > toConnection.commit() # if desired
> > >
> > > That should do it and should perform quite well. The fetchraw(),
> > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > avoid the overhead of creating Python objects for the data. Its more
> > > complicated than the above code but I can give that as an example as
> > > well if you really want it.
> > >
> >
> > I've done essentially the same as you've got above and yes it works fin=
e with pretty good
> > performance. I'm just trying to improve it further as there are cases w=
hen the app will be running
> > a number of parallel streams each selecting and inserting millions of r=
ows in a fixed window so
> > saving 5% - 15% of the Python CPU time within this loop is definately w=
orth doing. I'd like a
> > fetchraw example so I can benchmark it against my current approach. I'l=
l then post the results so
> > everyone can see the general sort of performance differences between th=
e two.
>=20
> The following is the code for using fetchraw(). My own preliminary
> results show that in fact it is slower than the original method above
> that I showed you and is more complicated to boot. The fact that the
> data can be passed from the one cursor to the other directly and that
> the bindvar.copy() command must be called for each bind variable for
> each row makes the overhead of fetching Python objects less than the
> overhead of calling the bindvar.copy() methods. The fetchraw() method
> was considerably faster when lists of dictionaries needed to be passed
> through to executemany().
>=20
> import cx_Oracle
>=20
> sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
> sourceCursor =3D sourceConnection.cursor()
> sourceCursor.arraysize =3D 250
> sourceVars =3D sourceCursor.execute("select * from testdata_source")
>=20
> targetConnection =3D cx_Oracle.Connection("user/pw@tns")
> targetCursor =3D targetConnection.cursor()
> targetCursor.execute("truncate table testdata_copy")
> targetCursor.arraysize =3D 250
> targetVars =3D targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DAT=
ETIME,
> cx_Oracle.NUMBER, cx_Oracle.STRING)
> targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
>=20
> vars =3D zip(sourceVars, targetVars)
> indexes =3D range(sourceCursor.arraysize)
> while True:
> rowsFetched =3D sourceCursor.fetchraw()
> print "fetched", rowsFetched, "rows"
> if not rowsFetched:
> break
> if rowsFetched !=3D sourceCursor.arraysize:
> indexes =3D range(rowsFetched)
> for index in indexes:
> for sourceVar, targetVar in vars:
> targetVar.copy(sourceVar, index, index)
> targetCursor.executemanyprepared(rowsFetched)
> targetConnection.commit()
>=20
> Another method which also works but only when the exact data is being
> replicated from one cursor to another is the following. It is faster
> than both methods but limited in usefulness of course.
>=20
> import cx_Oracle
>=20
> sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
> sourceCursor =3D sourceConnection.cursor()
> sourceCursor.arraysize =3D 250
> sourceVars =3D sourceCursor.execute("select * from testdata_source")
>=20
> targetConnection =3D cx_Oracle.Connection("user/pw@tns")
> targetCursor =3D targetConnection.cursor()
> targetCursor.execute("truncate table testdata_copy")
> targetCursor.arraysize =3D 250
> targetVars =3D targetCursor.setinputsizes(*sourceVars)
> targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
>=20
> while True:
> rowsFetched =3D sourceCursor.fetchraw()
> print "fetched", rowsFetched, "rows"
> if not rowsFetched:
> break
> targetCursor.executemanyprepared(rowsFetched)
> targetConnection.commit()
>=20
> Hope this helps,
>=20
> Anthony
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Chris D. <cdu...@ya...> - 2005-05-18 08:17:01
|
--- Leith Parkin <lei...@gm...> wrote:
> Hi Anthony,
>
> Chris mentioned in an earlier post that the select results are being
> used to create and insert into another database, not Oracle, otherwise
> you wouldnt even use Python, just insert into select syntax.
Sorry but I didn't make myself totally clear. I am inserting into another database but it is still
Oracle. However I can't make use of insert into select syntax because as I'd said earlier I can't
use database links for lots of reasons I can't go into here. (I'd really like to use insert into
select as it would make life much easier and faster, but I can't, never mind.)
>
> The problem as I understand it is that Oracle empty varchar2's are
> returned as NULL rather than the python ''. Either way i think Chris
> has enough information to do what he has been looking for now.
Yes I have enough info now, so thanks very much.
Chris
>
> Regards,
>
> Leith
>
> On 5/18/05, Anthony Tuininga <ant...@gm...> wrote:
> > On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> > >
> > > --- Anthony Tuininga <ant...@gm...> wrote:
> > > > 1) You should be able to do something like this without the need to
> > > > descend into fetchraw().
> > > >
> > > > import cx_Oracle
> > > >
> > > > fromConnection = cx_Oracle.Connection("user/pw@tns")
> > > > toConnection = cx_Oracle.Connection("user/pw@tns")
> > > > fromCursor = fromConnection.cursor()
> > > > fromCursor.arraysize = 250 # set as desired
> > > > toCursor = toConnection.cursor()
> > > > toCursor.arraysize = 250 # set as desired, same as fromCursor
> > > >
> > > > fromCursor.execute("some query")
> > > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > > while True:
> > > > data = fromCursor.fetchmany()
> > > > if not data:
> > > > break
> > > > toCursor.executemany("some insert statement", data)
> > > > toConnection.commit() # if desired
> > > >
> > > > That should do it and should perform quite well. The fetchraw(),
> > > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > > avoid the overhead of creating Python objects for the data. Its more
> > > > complicated than the above code but I can give that as an example as
> > > > well if you really want it.
> > > >
> > >
> > > I've done essentially the same as you've got above and yes it works fine with pretty good
> > > performance. I'm just trying to improve it further as there are cases when the app will be
> running
> > > a number of parallel streams each selecting and inserting millions of rows in a fixed window
> so
> > > saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd like
> a
> > > fetchraw example so I can benchmark it against my current approach. I'll then post the
> results so
> > > everyone can see the general sort of performance differences between the two.
> >
> > The following is the code for using fetchraw(). My own preliminary
> > results show that in fact it is slower than the original method above
> > that I showed you and is more complicated to boot. The fact that the
> > data can be passed from the one cursor to the other directly and that
> > the bindvar.copy() command must be called for each bind variable for
> > each row makes the overhead of fetching Python objects less than the
> > overhead of calling the bindvar.copy() methods. The fetchraw() method
> > was considerably faster when lists of dictionaries needed to be passed
> > through to executemany().
> >
> > import cx_Oracle
> >
> > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > sourceCursor = sourceConnection.cursor()
> > sourceCursor.arraysize = 250
> > sourceVars = sourceCursor.execute("select * from testdata_source")
> >
> > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > targetCursor = targetConnection.cursor()
> > targetCursor.execute("truncate table testdata_copy")
> > targetCursor.arraysize = 250
> > targetVars = targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DATETIME,
> > cx_Oracle.NUMBER, cx_Oracle.STRING)
> > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> >
> > vars = zip(sourceVars, targetVars)
> > indexes = range(sourceCursor.arraysize)
> > while True:
> > rowsFetched = sourceCursor.fetchraw()
> > print "fetched", rowsFetched, "rows"
> > if not rowsFetched:
> > break
> > if rowsFetched != sourceCursor.arraysize:
> > indexes = range(rowsFetched)
> > for index in indexes:
> > for sourceVar, targetVar in vars:
> > targetVar.copy(sourceVar, index, index)
> > targetCursor.executemanyprepared(rowsFetched)
> > targetConnection.commit()
> >
> > Another method which also works but only when the exact data is being
> > replicated from one cursor to another is the following. It is faster
> > than both methods but limited in usefulness of course.
> >
> > import cx_Oracle
> >
> > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > sourceCursor = sourceConnection.cursor()
> > sourceCursor.arraysize = 250
> > sourceVars = sourceCursor.execute("select * from testdata_source")
> >
> > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > targetCursor = targetConnection.cursor()
> > targetCursor.execute("truncate table testdata_copy")
> > targetCursor.arraysize = 250
> > targetVars = targetCursor.setinputsizes(*sourceVars)
> > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> >
> > while True:
> > rowsFetched = sourceCursor.fetchraw()
> > print "fetched", rowsFetched, "rows"
> > if not rowsFetched:
> > break
> > targetCursor.executemanyprepared(rowsFetched)
> > targetConnection.commit()
> >
> > Hope this helps,
> >
> > Anthony
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id344&opclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
|
|
From: Chris D. <cdu...@ya...> - 2005-05-26 10:59:18
|
My results based on selecting 100k rows (via a join with another table) from a 13 col table into
an identical table are (CPU figures only, elapsed times have too much "noise" due to disk
performance etc):
1. Original using fetchmany()
Python CPU: 12.1 secs
Oracle CPU: 6.8 secs
2. New approach using fetchraw() [special restricted fast method (see below) that happens to be
what I need - YES!]
Python CPU: 1.8 secs
Oracle CPU: 7.0 secs
So as you can see fetchraw() is vastly superior.
Test environment:
Athlon AMD XP2200+
Suse 8.2 with 2.4.22 kernel
Oracle 9.2
Python 2.3.4
cx_Oracle 4.1
Thanks very much for your help Anthony and the examples, they really helped
Chris
--- Chris Dunscombe <cdu...@ya...> wrote:
> > > > --- Anthony Tuininga <ant...@gm...> wrote:
> > > > > 1) You should be able to do something like this without the need to
> > > > > descend into fetchraw().
> > > > >
> > > > > import cx_Oracle
> > > > >
> > > > > fromConnection = cx_Oracle.Connection("user/pw@tns")
> > > > > toConnection = cx_Oracle.Connection("user/pw@tns")
> > > > > fromCursor = fromConnection.cursor()
> > > > > fromCursor.arraysize = 250 # set as desired
> > > > > toCursor = toConnection.cursor()
> > > > > toCursor.arraysize = 250 # set as desired, same as fromCursor
> > > > >
> > > > > fromCursor.execute("some query")
> > > > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > > > while True:
> > > > > data = fromCursor.fetchmany()
> > > > > if not data:
> > > > > break
> > > > > toCursor.executemany("some insert statement", data)
> > > > > toConnection.commit() # if desired
> > > > >
> > > > > That should do it and should perform quite well. The fetchraw(),
> > > > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > > > avoid the overhead of creating Python objects for the data. Its more
> > > > > complicated than the above code but I can give that as an example as
> > > > > well if you really want it.
> > > > >
> > > >
> > > > I've done essentially the same as you've got above and yes it works fine with pretty good
> > > > performance. I'm just trying to improve it further as there are cases when the app will be
> > running
> > > > a number of parallel streams each selecting and inserting millions of rows in a fixed
> window
> > so
> > > > saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd
> like
> > a
> > > > fetchraw example so I can benchmark it against my current approach. I'll then post the
> > results so
> > > > everyone can see the general sort of performance differences between the two.
> > >
> > > Another method which also works but only when the exact data is being
> > > replicated from one cursor to another is the following. It is faster
> > > than both methods but limited in usefulness of course.
> > >
> > > import cx_Oracle
> > >
> > > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > > sourceCursor = sourceConnection.cursor()
> > > sourceCursor.arraysize = 250
> > > sourceVars = sourceCursor.execute("select * from testdata_source")
> > >
> > > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > > targetCursor = targetConnection.cursor()
> > > targetCursor.execute("truncate table testdata_copy")
> > > targetCursor.arraysize = 250
> > > targetVars = targetCursor.setinputsizes(*sourceVars)
> > > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> > >
> > > while True:
> > > rowsFetched = sourceCursor.fetchraw()
> > > print "fetched", rowsFetched, "rows"
> > > if not rowsFetched:
> > > break
> > > targetCursor.executemanyprepared(rowsFetched)
> > > targetConnection.commit()
> > >
> > > Hope this helps,
> > >
> > > Anthony
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_idt12&alloc_id344&opclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
>
>
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&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
|
|
From: Leith P. <lei...@gm...> - 2005-05-14 02:48:40
|
Hi Chris, No worries. We have a very similair setup at work, transferring summarised data from oracle into MySQL. Depending on the database you are transferring too, couldnt you set a trigger, inserting NULL (eg, a python None from Oracle) into the table generates an empty string? Failing that, subclassing cx_Oracles cursor object to return a the empty string when the column type is textual and the value is null might be another option. Good luck, Leith On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > Leith, >=20 > I didn't mention that for 1) the target table is in a separate database a= nd using database links > isn't an option (although I wish it was) due to reasons that are too leng= thy to go into here. > Hence PL/SQL isn't really viable. So I'd still like to look at cursor.fe= tchraw() >=20 > Now for 2) you maybe right that PL/SQL could be used but I'll still have = to write out to file > using Python as util_file can only write to files that the database serve= r can "see" which isn't > always the case for my app. >=20 > Thanks for the idea, >=20 > Chris >=20 >=20 > --- Leith Parkin <lei...@gm...> wrote: > > This sounds like something better done in PL/SQL? > > > > Regards, > > > > Leith > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > > I'm looking to use cursor.fetchraw to help improve performance in an = app I'm writing. I've > > looked > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out= . > > > If possible I'd like a simple full example to show me the way. The co= ntext is: > > > > > > 1) Retrieve rows from source table and insert into a target table whi= ch has exactly the same > > > structure. > > > > > > 2) Retrieve rows from source table, add some column formatting and wr= ite out to a file. > > > > > > Thanks very much, > > > > > > Chris Dunscombe > > > > > > PS Thanks for all the input re my previous posting on Nulls and None.= I've still got some > > thinking > > > and work to do on which is best for performance and how best to cope = with different datatypes, > > > particularly Longs (what a pain they are!!) > > > > > > __________________________________ > > > Yahoo! Mail Mobile > > > Take Yahoo! Mail with you! Check email on your mobile phone. > > > http://mobile.yahoo.com/learn/mail > > > > > > ------------------------------------------------------- > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > Want to be the first software developer in space? > > > Enter now for the Oracle Space Sweepstakes! > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick > > > _______________________________________________ > > > cx-oracle-users mailing list > > > cx-...@li... > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick > > _______________________________________________ > > cx-oracle-users mailing list > > cx-...@li... > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > >=20 > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by Oracle Space Sweepstakes > Want to be the first software developer in space? > Enter now for the Oracle Space Sweepstakes! > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > |
|
From: Chris D. <cdu...@ya...> - 2005-05-15 08:13:19
|
Leith, Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give me a code example that would be much appreciated. Thanks very much, Chris --- Leith Parkin <lei...@gm...> wrote: > Hi Chris, > > No worries. We have a very similair setup at work, transferring > summarised data from oracle into MySQL. > > Depending on the database you are transferring too, couldnt you set a > trigger, inserting NULL (eg, a python None from Oracle) into the table > generates an empty string? > > Failing that, subclassing cx_Oracles cursor object to return a the > empty string when the column type is textual and the value is null > might be another option. > > Good luck, > > Leith > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > Leith, > > > > I didn't mention that for 1) the target table is in a separate database and using database > links > > isn't an option (although I wish it was) due to reasons that are too lengthy to go into here. > > Hence PL/SQL isn't really viable. So I'd still like to look at cursor.fetchraw() > > > > Now for 2) you maybe right that PL/SQL could be used but I'll still have to write out to file > > using Python as util_file can only write to files that the database server can "see" which > isn't > > always the case for my app. > > > > Thanks for the idea, > > > > Chris > > > > > > --- Leith Parkin <lei...@gm...> wrote: > > > This sounds like something better done in PL/SQL? > > > > > > Regards, > > > > > > Leith > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've > > > looked > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out. > > > > If possible I'd like a simple full example to show me the way. The context is: > > > > > > > > 1) Retrieve rows from source table and insert into a target table which has exactly the > same > > > > structure. > > > > > > > > 2) Retrieve rows from source table, add some column formatting and write out to a file. > > > > > > > > Thanks very much, > > > > > > > > Chris Dunscombe > > > > > > > > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some > > > thinking > > > > and work to do on which is best for performance and how best to cope with different > datatypes, > > > > particularly Longs (what a pain they are!!) > > > > > > > > __________________________________ > > > > Yahoo! Mail Mobile > > > > Take Yahoo! Mail with you! Check email on your mobile phone. > > > > http://mobile.yahoo.com/learn/mail > > > > > > > > ------------------------------------------------------- > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > > Want to be the first software developer in space? > > > > Enter now for the Oracle Space Sweepstakes! > > > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click > > > > _______________________________________________ > > > > cx-oracle-users mailing list > > > > cx-...@li... > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > > > > > > > ------------------------------------------------------- > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > Want to be the first software developer in space? > > > Enter now for the Oracle Space Sweepstakes! > > > http://ads.osdn.com/?ad_ids93&alloc_id281&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 > > > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click > > _______________________________________________ > > cx-oracle-users mailing list > > cx-...@li... > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by Oracle Space Sweepstakes > Want to be the first software developer in space? > Enter now for the Oracle Space Sweepstakes! > http://ads.osdn.com/?ad_ids93&alloc_id281&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 |
|
From: Leith P. <lei...@gm...> - 2005-05-15 23:18:44
|
Hi Chris,
This is a quick example i knocked togethor ages ago to return a dict
result set instead of tuples. In retrospect fetchall() would probably
be better writtern as a generator under the newer python stuff.
eg r =3D db.fetchone()
r['FIELDNAME']
Prehaps Anthony could give some feedback as to the "correctness" of this.
--- SNIP ---
from cx_Oracle import *
# DictCursor for Oracle, returns dicts instead of tuples
class DictConnect(Connection):
def cursor(self):
return DictCursor(self)
class DictCursor(Cursor):
def fetchall(self):
r =3D Cursor.fetchall(self)
result =3D []
for l in r:
result.append( dict([(a[0],b) for a,b in
zip(self.description, l)]) )
return result
def fetchone(self):
r =3D Cursor.fetchone(self)
if r:
return dict([(a[0],b) for a,b in zip(self.description, r)])
else:
return None
# factory class
def connect(**kw):
return DictConnect(**kw)
---- SNIP ----
On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote:
> Leith,
>=20
> Thanks, I like the sub-classing idea but my OO Python isn't that good so =
if you could give me a
> code example that would be much appreciated.
>=20
> Thanks very much,
>=20
> Chris
> --- Leith Parkin <lei...@gm...> wrote:
>=20
> > Hi Chris,
> >
> > No worries. We have a very similair setup at work, transferring
> > summarised data from oracle into MySQL.
> >
> > Depending on the database you are transferring too, couldnt you set a
> > trigger, inserting NULL (eg, a python None from Oracle) into the table
> > generates an empty string?
> >
> > Failing that, subclassing cx_Oracles cursor object to return a the
> > empty string when the column type is textual and the value is null
> > might be another option.
> >
> > Good luck,
> >
> > Leith
> >
> >
> > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > Leith,
> > >
> > > I didn't mention that for 1) the target table is in a separate databa=
se and using database
> > links
> > > isn't an option (although I wish it was) due to reasons that are too =
lengthy to go into here.
> > > Hence PL/SQL isn't really viable. So I'd still like to look at curso=
r.fetchraw()
> > >
> > > Now for 2) you maybe right that PL/SQL could be used but I'll still h=
ave to write out to file
> > > using Python as util_file can only write to files that the database s=
erver can "see" which
> > isn't
> > > always the case for my app.
> > >
> > > Thanks for the idea,
> > >
> > > Chris
> > >
> > >
> > > --- Leith Parkin <lei...@gm...> wrote:
> > > > This sounds like something better done in PL/SQL?
> > > >
> > > > Regards,
> > > >
> > > > Leith
> > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote:
> > > > > I'm looking to use cursor.fetchraw to help improve performance in=
an app I'm writing. I've
> > > > looked
> > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work it=
out.
> > > > > If possible I'd like a simple full example to show me the way. Th=
e context is:
> > > > >
> > > > > 1) Retrieve rows from source table and insert into a target table=
which has exactly the
> > same
> > > > > structure.
> > > > >
> > > > > 2) Retrieve rows from source table, add some column formatting an=
d write out to a file.
> > > > >
> > > > > Thanks very much,
> > > > >
> > > > > Chris Dunscombe
> > > > >
> > > > > PS Thanks for all the input re my previous posting on Nulls and N=
one. I've still got some
> > > > thinking
> > > > > and work to do on which is best for performance and how best to c=
ope with different
> > datatypes,
> > > > > particularly Longs (what a pain they are!!)
> > > > >
> > > > > __________________________________
> > > > > Yahoo! Mail Mobile
> > > > > Take Yahoo! Mail with you! Check email on your mobile phone.
> > > > > http://mobile.yahoo.com/learn/mail
> > > > >
> > > > > -------------------------------------------------------
> > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > > Want to be the first software developer in space?
> > > > > Enter now for the Oracle Space Sweepstakes!
> > > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > > > _______________________________________________
> > > > > cx-oracle-users mailing list
> > > > > cx-...@li...
> > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > > Want to be the first software developer in space?
> > > > Enter now for the Oracle Space Sweepstakes!
> > > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > > > _______________________________________________
> > > > 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
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Anthony T. <ant...@gm...> - 2005-05-16 23:02:22
|
On 5/15/05, Leith Parkin <lei...@gm...> wrote: > Hi Chris, >=20 > This is a quick example i knocked togethor ages ago to return a dict > result set instead of tuples. In retrospect fetchall() would probably > be better writtern as a generator under the newer python stuff. Definitely. :-) Especially if you wanted to use it for large result sets. > eg r =3D db.fetchone() > r['FIELDNAME'] >=20 > Prehaps Anthony could give some feedback as to the "correctness" of this. Looks "correct" to me. It works, doesn't it?? :-) As suggested, if you wanted to override the __iter__() method (or the fetchall() as shown below) to replace None with "" for null strings, that would be quite doable. If you need an example of that, I could probably whip something up. As you can see by this example, subclassing to extend or override functionality is actually quite simple. > --- SNIP --- > from cx_Oracle import * >=20 > # DictCursor for Oracle, returns dicts instead of tuples > class DictConnect(Connection): > def cursor(self): > return DictCursor(self) >=20 > class DictCursor(Cursor): > def fetchall(self): > r =3D Cursor.fetchall(self) > result =3D [] > for l in r: > result.append( dict([(a[0],b) for a,b in > zip(self.description, l)]) ) >=20 > return result >=20 > def fetchone(self): > r =3D Cursor.fetchone(self) > if r: > return dict([(a[0],b) for a,b in zip(self.description, r)= ]) > else: > return None >=20 > # factory class > def connect(**kw): > return DictConnect(**kw) >=20 > ---- SNIP ---- >=20 > On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote: > > Leith, > > > > Thanks, I like the sub-classing idea but my OO Python isn't that good s= o if you could give me a > > code example that would be much appreciated. > > > > Thanks very much, > > > > Chris > > --- Leith Parkin <lei...@gm...> wrote: > > > > > Hi Chris, > > > > > > No worries. We have a very similair setup at work, transferring > > > summarised data from oracle into MySQL. > > > > > > Depending on the database you are transferring too, couldnt you set a > > > trigger, inserting NULL (eg, a python None from Oracle) into the tabl= e > > > generates an empty string? > > > > > > Failing that, subclassing cx_Oracles cursor object to return a the > > > empty string when the column type is textual and the value is null > > > might be another option. > > > > > > Good luck, > > > > > > Leith > > > > > > > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > Leith, > > > > > > > > I didn't mention that for 1) the target table is in a separate data= base and using database > > > links > > > > isn't an option (although I wish it was) due to reasons that are to= o lengthy to go into here. > > > > Hence PL/SQL isn't really viable. So I'd still like to look at cur= sor.fetchraw() > > > > > > > > Now for 2) you maybe right that PL/SQL could be used but I'll still= have to write out to file > > > > using Python as util_file can only write to files that the database= server can "see" which > > > isn't > > > > always the case for my app. > > > > > > > > Thanks for the idea, > > > > > > > > Chris > > > > > > > > > > > > --- Leith Parkin <lei...@gm...> wrote: > > > > > This sounds like something better done in PL/SQL? > > > > > > > > > > Regards, > > > > > > > > > > Leith > > > > > On 5/13/05, Chris Dunscombe <cdu...@ya...> wrote: > > > > > > I'm looking to use cursor.fetchraw to help improve performance = in an app I'm writing. I've > > > > > looked > > > > > > at CopyData from cx_OracleTools but I'm afraid I couldn't work = it out. > > > > > > If possible I'd like a simple full example to show me the way. = The context is: > > > > > > > > > > > > 1) Retrieve rows from source table and insert into a target tab= le which has exactly the > > > same > > > > > > structure. > > > > > > > > > > > > 2) Retrieve rows from source table, add some column formatting = and write out to a file. > > > > > > > > > > > > Thanks very much, > > > > > > > > > > > > Chris Dunscombe > > > > > > > > > > > > PS Thanks for all the input re my previous posting on Nulls and= None. I've still got some > > > > > thinking > > > > > > and work to do on which is best for performance and how best to= cope with different > > > datatypes, > > > > > > particularly Longs (what a pain they are!!) > > > > > > > > > > > > __________________________________ > > > > > > Yahoo! Mail Mobile > > > > > > Take Yahoo! Mail with you! Check email on your mobile phone. > > > > > > http://mobile.yahoo.com/learn/mail > > > > > > > > > > > > ------------------------------------------------------- > > > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > > > > Want to be the first software developer in space? > > > > > > Enter now for the Oracle Space Sweepstakes! > > > > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick > > > > > > _______________________________________________ > > > > > > cx-oracle-users mailing list > > > > > > cx-...@li... > > > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------- > > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > > > Want to be the first software developer in space? > > > > > Enter now for the Oracle Space Sweepstakes! > > > > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick > > > > > _______________________________________________ > > > > > 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 > > > > > > > > > > > > ------------------------------------------------------- > > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > > Want to be the first software developer in space? > > > > Enter now for the Oracle Space Sweepstakes! > > > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick > > > > _______________________________________________ > > > > cx-oracle-users mailing list > > > > cx-...@li... > > > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > > > > > > > > > > > > ------------------------------------------------------- > > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > > Want to be the first software developer in space? > > > Enter now for the Oracle Space Sweepstakes! > > > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick > > > _______________________________________________ > > > 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 > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by Oracle Space Sweepstakes > > Want to be the first software developer in space? > > Enter now for the Oracle Space Sweepstakes! > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick > > _______________________________________________ > > cx-oracle-users mailing list > > cx-...@li... > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by Oracle Space Sweepstakes > Want to be the first software developer in space? > Enter now for the Oracle Space Sweepstakes! > http://ads.osdn.com/?ad_ids93&alloc_id=16281&opclick > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > |
|
From: Chris D. <cdu...@ya...> - 2005-05-17 09:15:14
|
See in-line --- Anthony Tuininga <ant...@gm...> wrote: > On 5/15/05, Leith Parkin <lei...@gm...> wrote: > > Hi Chris, > > > > This is a quick example i knocked togethor ages ago to return a dict > > result set instead of tuples. In retrospect fetchall() would probably > > be better writtern as a generator under the newer python stuff. > > Definitely. :-) Especially if you wanted to use it for large result sets. > > > eg r = db.fetchone() > > r['FIELDNAME'] > > > > Prehaps Anthony could give some feedback as to the "correctness" of this. > > Looks "correct" to me. It works, doesn't it?? :-) As suggested, if you > wanted to override the __iter__() method (or the fetchall() as shown > below) to replace None with "" for null strings, that would be quite > doable. If you need an example of that, I could probably whip > something up. As you can see by this example, subclassing to extend or > override functionality is actually quite simple. Anthony, If you could supply a simple example that would be much appreciated. Thanks, Chris > > > --- SNIP --- > > from cx_Oracle import * > > > > # DictCursor for Oracle, returns dicts instead of tuples > > class DictConnect(Connection): > > def cursor(self): > > return DictCursor(self) > > > > class DictCursor(Cursor): > > def fetchall(self): > > r = Cursor.fetchall(self) > > result = [] > > for l in r: > > result.append( dict([(a[0],b) for a,b in > > zip(self.description, l)]) ) > > > > return result > > > > def fetchone(self): > > r = Cursor.fetchone(self) > > if r: > > return dict([(a[0],b) for a,b in zip(self.description, r)]) > > else: > > return None > > > > # factory class > > def connect(**kw): > > return DictConnect(**kw) > > > > ---- SNIP ---- > > > > On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote: > > > Leith, > > > > > > Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give > me a > > > code example that would be much appreciated. > > > > > > Thanks very much, > > > > > > Chris > > > --- Leith Parkin <lei...@gm...> wrote: > > > > > > > Hi Chris, > > > > > > > > No worries. We have a very similair setup at work, transferring > > > > summarised data from oracle into MySQL. > > > > > > > > Depending on the database you are transferring too, couldnt you set a > > > > trigger, inserting NULL (eg, a python None from Oracle) into the table > > > > generates an empty string? > > > > > > > > Failing that, subclassing cx_Oracles cursor object to return a the > > > > empty string when the column type is textual and the value is null > > > > might be another option. > > > > > > > > Good luck, > > > > > > > > Leith > > > > __________________________________ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs |
|
From: Chris D. <cdu...@ya...> - 2005-05-17 01:17:58
|
Leith, Thanks very much, Chris --- Leith Parkin <lei...@gm...> wrote: > Hi Chris, > > This is a quick example i knocked togethor ages ago to return a dict > result set instead of tuples. In retrospect fetchall() would probably > be better writtern as a generator under the newer python stuff. > > eg r = db.fetchone() > r['FIELDNAME'] > > Prehaps Anthony could give some feedback as to the "correctness" of this. > > --- SNIP --- > from cx_Oracle import * > > # DictCursor for Oracle, returns dicts instead of tuples > class DictConnect(Connection): > def cursor(self): > return DictCursor(self) > > class DictCursor(Cursor): > def fetchall(self): > r = Cursor.fetchall(self) > result = [] > for l in r: > result.append( dict([(a[0],b) for a,b in > zip(self.description, l)]) ) > > return result > > def fetchone(self): > r = Cursor.fetchone(self) > if r: > return dict([(a[0],b) for a,b in zip(self.description, r)]) > else: > return None > > # factory class > def connect(**kw): > return DictConnect(**kw) > > ---- SNIP ---- > > On 5/15/05, Chris Dunscombe <cdu...@ya...> wrote: > > Leith, > > > > Thanks, I like the sub-classing idea but my OO Python isn't that good so if you could give me > a > > code example that would be much appreciated. > > > > Thanks very much, > > > > Chris > > --- Leith Parkin <lei...@gm...> wrote: > > > > > Hi Chris, > > > > > > No worries. We have a very similair setup at work, transferring > > > summarised data from oracle into MySQL. > > > > > > Depending on the database you are transferring too, couldnt you set a > > > trigger, inserting NULL (eg, a python None from Oracle) into the table > > > generates an empty string? > > > > > > Failing that, subclassing cx_Oracles cursor object to return a the > > > empty string when the column type is textual and the value is null > > > might be another option. > > > > > > Good luck, > > > > > > Leith __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X