DBI cursor behaviour with multiple statements?

Steve Holden sholden at holdenweb.com
Tue Oct 1 12:40:00 EDT 2002


"Henrik Weber" <Henrik.Weber at sys.aok.de> wrote ...
> paul at boddie.net (Paul Boddie) wrote ...
> > Henrik.Weber at sys.aok.de (Henrik Weber) wrote ...
> > > 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.

Bzzt. Don't confuse this use of the % with string formatiing, which it
isn't.

>                                                                         Of
course I
> could place the quotes in the parameters instead:
>
> c.executemany("SELECT * FROM table WHERE column = %s", [["'parm1'"],
> ["'parm2'"], ["'parm3'"]])
>
Have you TRIED this?

> 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.
>
You will find that most DB API-compliant modules will not only do any
necessary string quoting, they will also accept strings containing numeric
values for numeric fields.

[ ... ]
> >
> >   "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.
>
It's not a generally applicable technique, true, though apparently it works
with psycopg. Clearly the parameterization of SQL queries is a grey area.
The section of the DB API specification entitled "Type Objects and
Constructors" is one of the least accessible portions, but it describes
parameter coercion quite well and repays study. Type Objects have a weird
equality relationship, and the API is (potentially, though probably not in
all implementations) more sophisticated than you give it credit for.

regards
-----------------------------------------------------------------------
Steve Holden                                  http://www.holdenweb.com/
Python Web Programming                 http://pydish.holdenweb.com/pwp/
Previous .sig file retired to                    www.homeforoldsigs.com
-----------------------------------------------------------------------






More information about the Python-list mailing list