DBI cursor behaviour with multiple statements?

[Henrik Weber]

paul at boddie.net (Paul Boddie) wrote in message news:<23891c90.0210020458.e69b4fd at posting.google.com>...
> Henrik.Weber at sys.aok.de (Henrik Weber) wrote in message news:<7e964d1d.0210010652.5b8768de at posting.google.com>...
> > paul at boddie.net (Paul Boddie) wrote in message news:<23891c90.0209300554.5b37c195 at posting.google.com>...
[...] 
> 
> > 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.
> 
> Indeed, and many database systems accept placeholders/parameters
> because they do understand them.
> 
> > > 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.
> 
> You could support paramstyle reconfiguration at connection time. This
> would require you to substitute the parameters used for those employed
> by the database system, however.

In the last few days I have made some experiments with ADO and the Jet
OLE DB provider. They seem to understand several syntax styles for
parameters more or less directly. For the rest I think it is possible
to reformat the query string. Alright, I'll make the paramstyle user
configurable.

> 
> > 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.
> 
> Yes, but then you have to do the quoting of the supplied values on
> behalf of the caller - it's not up to them to do this.
> 
> > 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.
> 
> No, but the use of apostrophes in the values suggested that you
> equated placeholders/parameters with string
> substitution/interpolation/replacement, when they're clearly not the
> same thing. If you weren't under that illusion then I apologise now.

No reason to apologise. Basically I was under that illusion. I hadn't
given much thought to parameters when I posted my first message. Well,
I got answers for two questions by posting only one. That's pretty
efficient ;-)

> 
> > >   "SELECT * FROM table WHERE column IN %s"
> 
> [...]
> 
> > > 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.
> 
> Consider the alternative notation:
> 
>   SELECT * FROM table WHERE column IN :1

Would you also expect the database module to know to place parentheses
around the placeholder? The Jet Provider complains about them missing,
so the user at least has to write:

SELECT * FROM table WHERE column IN (:1)

> 
> I would definitely expect a list/tuple/sequence to be supplied as the
> value that binds to the parameter. Indeed, the psycopg developers
> appear to agree.
> 
> [...]
> 
> > 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.
> 
> You're right in the sense that some database modules don't need to
> look at the query string at all. If the underlying database system
> supports placeholder notation, you can risk sending the query string
> to the database system...
> 
>   some_api_query("SELECT * FROM SOME_TABLE WHERE SOME_COLUMN = :1")
> 
> ...and then traverse the list of values, binding each one as you go:
> 
>   for i in range(0, len(values)):
>       if some_api_bind(i, values[i]) == FAILED:
>           raise SomeException, "Could not bind all values."
> 
> If the database system doesn't like it, you'll get told about it, and
> you would pass this failure condition back in some meaningful way.

The Jet OLE DB provider does not like lists or tuples as parameters
directly. What might work is to try to bind the parameter. When the
attempt fails determine if the parameter is a sequence and if so
replace the placeholder with a number of new placeholders depending on
the length of the sequence. Then the elements of the sequence could be
bound one by one to the new placeholders.
 
> On the issue of syntax, the only thing the user needs to guarantee is
> the syntax of the query string, *not* whether the query string with
> "pasted in" values is also syntactically correct (especially since a
> simplistic string replacement is *not* what goes on). Moreover, with
> some database modules, the user doesn't really need to check the types
> of the values very carefully either, but that's another story.
> 
> Paul

Thanks for your input. It will help me address some issues I'm just
now starting to come across.

--Henrik