Re: [cx-oracle-users] Help with fetchraw

[Leith P. <lei...@gm...>]

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
>