Re: [cx-oracle-users] Help with fetchraw
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Help with fetchraw
|
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
|
