[DB-SIG] paramstyle specification

[Michael Bayer]

On May 23, 2013, at 10:59 AM, M.-A. Lemburg <mal at egenix.com> wrote:

>> 2. the cursor knows upfront that it will deal with a single "positional" or "named" parameter style.   When execute() is called, it examines the parameter argument to see if it is a sequence or a mapping.  Based on that, it determines to parse for the "positional" or "named" style.   
>> 
>> I'm guessing that #2 is the "magic auto-selection" here.  I don't have a problem with it as I think some DBAPIs already do that, but I don't have a huge problem if we go with #1 either.  
>> 
>> But I would like to get it specified what happens when there are *no* parameters sent, as the DBAPIs behave inconsistently in this regard (some parse the string, others don't).
> 
> Does it matter whether they parse the string or not ? If the SQL
> contains a parameter marker for which no parameter is provided,
> the database will complain, so you'd get an error either from the
> database backend or the database module.

OK well here's where there's a cultural mismatch between different DBAPI authors.  Some DBAPIs need to perform substitutions in the string before they send it off to the server, like psycopg2 does.  Others, like those that work with ODBC (I think) don't have to do as much, if at all.   So I'd say no, the system doesn't "have to" parse the string if that's not already part of its behavior.

>> As a total aside, I also have issues with some DBAPIs that accept *only* a list for the "sequence", others that accept *only* a tuple (this may be only when the parameter list is empty, however).   So specifying the significance of "empty" parameter lists as well as how to test for "sequence" would be nice too, so that I can hit the DBAPI authors with bug reports backed up by the spec.
> 
> The DB-API is already clear on this: any sequence should be
> accepted for .execute() and any sequence of sequences for .executemany().
> 
> Spelling out the special case of passing an empty sequence to
> .execute() and .executemany() would probably be wise. For .execute()
> this would only be valid for a statement that doesn't have parameter
> markers. For .executemany() this would be the same as a no-op (and
> only serve a purpose on the basis that it makes writing algorithms
> easier).

The thing with the empty parameter lists becomes significant when we deal with DBAPIs that change their behavior based on the paramlist being present or not.  This is why it gets really confusing; some DBAPIs don't like an empty parameter list, others will change their string parsing behavior (such as requiring % to be escaped or not) based on whether or not a collection is present.