[DB-SIG] cx_Oracle 2.5

[Anthony Tuininga]

On Wed, 2002-07-17 at 08:08, M.-A. Lemburg wrote:
> Anthony Tuininga wrote:
> > On Wed, 2002-07-17 at 02:06, M.-A. Lemburg wrote:
> > 
> >>Anthony Tuininga wrote:
> >>
> >>>What is cx_Oracle?
> >>>
> >>> 7) Implemented cursor method executemany() which expects a list of
> >>>    dictionaries for the arguments
> >>
> >>As per DB API this should be a sequence of sequences. Dictionaries
> >>are not sequences.
> > 
> > 
> > The one problem with passing a list of sequences (lists or tuples) is
> > that it would mean that I would have to use bind by position in this
> > case when I am using bind by name in all other cases. That would seem
> to
> > me to be inconsistent with the execute() method which expects bind by
> > name, don't you think? To me, it would make more sense that execute()
> > and executemany() would use the same method of binding; otherwise, if
> I
> > want to use executemany() I have to completely change my SQL statement
> > and my bound variables!!? Comments?
> 
> Of course, both APIs expose the same interface, that is .execute()
> expects a sequence as argument as well. Where did you get the idea
> that according to the DB API a dictionary can be passed to
> .execute() ?

>From the following statement in the DB API 2.0 document. It immediately
follows the syntax declaration for the cursor method execute(). Note
that it explicitly states that a sequence or mapping can be used
depending on the module's paramstyle attribute -- for my module, that
paramstyle is "named", which implies a dictionary. Thus my thought that
executemany(), which is just an extension of execute() ought to work the
same way.

-------------------------------- QUOTE ---------------------------------
 Prepare and execute a database operation (query or command). Parameters
may be provided as sequence or mapping and will be bound to variables in
the operation. Variables are specified in a database-specific notation
(see the module's paramstyle attribute for details). [5]
-------------------------------- QUOTE ---------------------------------

> >>>11) Added cursor method executemanyprepared() which is identical to
> >>
> >>the
> >>
> >>>    method executemany() except that it takes a single argument which
> >>
> >>is
> >>
> >>>    the number of times to execute a previously prepared statement
> and
> >>>    it assumes that the bind variables already have their values set;
> >>>    used for high performance only
> >>
> >>Such a method is not needed if you implement the .execute()
> >>caching of commands as described in the DB API. .prepare()
> >>can also nicely hook into this scheme if you expose the prepared
> >>command:
> >>
> >>c.prepare('stuff')
> >>c.executemany(c.command, list_of_data_tuples)
> > 
> > 
> > Not true.
> 
> That's how mxODBC works, but maybe I'm missing some
> magic here. Could it be that you are passing the data
> in via C arrays rather than Python objects ?

Actually, I am not passing anything at all which is the reason for the
performance gain. Normally, fetchone(), fetchmany() or fetchall() create
a tuple or sequence of tuples. The method I employed -- fetchraw() --
does nothing of the sort. Normally, executemany() expects a list of
sequences (or dictionaries in my opinion if "named" paramstyle is used)
so that also needs to be created and passed to the function. the method
I employed -- executemanyprepared() -- does not have that requirement.
As I stated before, this allows for significant performance benefits at
the expense of portability. I only use this in cases where it is
imperative that the program perform as fast as it can. BTW, with these
methods my Python programs now outperform my C++ programs that I wrote
earlier.

> > I support the following construct:
> > 
> > defined_vars = c1.execute(some_select_statement)
> 
> According to the DB API 2.0 the .execute() return value
> should not be used anymore (in DB API 1.0 it was used
> to return the number of affected rows).

Actually, it states that the return value is undefined. If you want to
follow the API, ignore the return value; otherwise, I am telling you
what the return value is so that you can use it if you don't care about
portability because you want high performance.

> > # insert code to transform defined_vars (sequence) to bind_vars
> > (dictionary)
> > c2.setinputsizes(bind_vars)
> 
> Again, .setinputsizes() should accept a sequence, not
> a dictionary as per the DB API.

Again, setinputsizes() ought to follow the same paramstyle as execute()
and executemany(), otherwise what point would there be in having
setinputsizes()? Are you suggesting that there be some automagical
method of transforming lists into dictionaries? Or are you suggesting
that the "named" paramstyle ought to be banned from the DB API?

> > c2.prepare(some_insert_statement)
> > c1.fetchraw(num_rows)
> > c2.executemanyprepared(num_rows)
> 
> How does the data from cursor c1 get to cursor c2 ?

I have bound the variable that is being populated on the select
statement directly to the variable that is being bound on the insert
statement. Again, this violates the DB API as it introduces a new type
for variables but you don't have to use it; you just won't get the
performance.

> > This code permits a straight copy from the fetch of one statement to
> the
> > input of another. Please keep in mind that this is intended __ONLY__
> for
> > high performance code as the database API is more than adequate in
> terms
> > of features for handling this stuff -- it just performs at about half
> to
> > one third of the speed!
> 
> I see, so it's just an extension for performance reasons.

BINGO! I generally don't use these extensions but they are handy when
needed.

> > BTW, I do support the prepare() method but I always pass None to the
> > execute() method to indicate that the previously prepared statement
> > ought to be used, rather than passing c.command.
> 
> That's tricky... c.command would be more explicit and is
> also inline with the specification w/r to the documented
> caching mechanism for .execute() et al. Anyway, just a
> suggestion.

I don't think of it as tricky -- but then I've been using it for quite
some time already.... :-) BTW, I didn't see anything PEP 249 with
respect to the cursor attribute "command". Did I miss something?

> -- 
> Marc-Andre Lemburg
> CEO eGenix.com Software GmbH
> _______________________________________________________________________
> eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
> Python Consulting:                               http://www.egenix.com/
> Python Software:                    http://www.egenix.com/files/python/
-- 
Anthony Tuininga
anthony@computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com