DBI cursor behaviour with multiple statements?

[Henrik Weber]

paul at boddie.net (Paul Boddie) wrote in message news:<23891c90.0209300554.5b37c195 at posting.google.com>...
> Henrik.Weber at sys.aok.de (Henrik Weber) wrote in message news:<7e964d1d.0209270137.568a3f3 at posting.google.com>...
> > Hello,
> > 
> > I'm trying to write a DBI2 compliant module for Microsoft Access
> > databases. Now I'm not quite sure how to interpret the DBI2
> > specification when it comes to the execution of multiple data
> > returning statements with the same cursor.
> > 
> > For example if c is my cursor and I do:
> > c.executemany("SELECT * FROM table WHERE column = '%s'", [["parm1"],
> > ["parm2"], ["parm3"]])
> 
> Note that you probably don't want the quoting inside the SQL
> statement. In other words, it should be...
> 
>   "SELECT * FROM table WHERE column = %s"

That depends on the type of the column. If it is some kind of
character column the quotes are required at some place. Of course I
could place the quotes in the parameters instead:

c.executemany("SELECT * FROM table WHERE column = %s", [["'parm1'"],
["'parm2'"], ["'parm3'"]])

But actually it doesn't matter. DBI is a very thin wrapper around the
native DB API. It doesn't analyze the content of the query, but just
passes it on to the database, maybe replacing the placeholders by
parameter values on the way. However the result of that operation has
to be a SQL statment that the native RDBMS will understand, so it's
not important what the query string looks like as long as the
underlying database will accept it.

> 
> Personally, I'd recommend supporting different parameter notations,
> since this issue always confuses people.

The paramstyle variable is supposed to be a string, so I can't put
several different values in there.

I could use the native parameter notation of the database, which is
qmark in this case. This is not one of the preferred notations
according to the DBI description, so I thought I might use pyformat
instead. Somewhere I have read that preparing a query doesn't make a
difference in performance with Access so I might just as well pass a
new query string to the database for every set of parameters without
losing anything. In the example it would have been possible to pass a
named placeholder (like "%(parm)s") and a sequence of dictionaries
instead without changing the code of the method.

Anyway I haven't yet decided on a quoting style and the style I'm
currently using is different from the one in the example. It was just
that the example was shorter this way and it didn't make a difference
for the question I was trying to have answered.

> > should the result be the union of all three statements or just the
> > result of the first or the last statement? Or should the user switch
> > from one resultset to the next with nextset?
> 
> An interesting, related issue is the treatment of the IN operator, and
> this doesn't necessarily yield uniform treatment across database
> modules, even for the same database system (compare pyPgSQL and
> psycopg on PostgreSQL). For some applications of your query (where you
> want the union), the IN operator is probably what is desired:

I didn't want the union. I wanted to know what to do in executemany if
it is fed a SELECT statement with several parametersets. PEP 249 says
the behaviour is undefined and the method may raise an exception when
it encounters a statement that returns data. That is probably what I'm
going to do.

> 
>   "SELECT * FROM table WHERE column IN %s"

I know I could have done that, but that was not my question.

> This appears highly counter-intuitive, of course, since the
> Python-style %s parameter marker actually represents a list in this
> case - another reason for choosing a better parameter notation.

No, it doesn't represent a list. It represents a string that has been
obtained by converting whatever was passed as parameter to its string
representation with str(). The result is something most databases
would reject with a syntax error. A different parameter notation is
not going to change that.

As I said above a DBI wrapper doesn't parse the query to find out how
to interpret the parameters (at least it would surprise me very much
if it did). It is still the users responsibility to assure that the
queries sent to the database are syntactically correct. Passing lists
as query criteria like in your example above is not going to work.

--Henrik