[DB-SIG] bind parameters and empty parameter lists

[Chris Clark]

On Fri, 24 May 2013 10:23:41 +0200, M.-A. Lemburg <mal at egenix.com> wrote:
> On 23.05.2013 23:37, Chris Clark wrote:
>> On Thu, 23 May 2013 13:33:20 -0700, Chris Clark <Chris.Clark at actian.com> wrote:
>>> On Thu, 23 May 2013 16:59:27 +0200, M.-A. Lemburg <mal at egenix.com> wrote:
>>>> On 22.05.2013 00:52, Michael Bayer wrote:
>>>>> 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).
>>>>
>>> I'd like to suggest that if the bind parameter is nonzero (i.e. __nonzero__ method call) then it
>>> is assumed there are no params. This way we don't have to get into is it an empty sequence, is it
>>> None, etc.
>> Apologies, I meant the reverse :-(
>>
>> I'd like to suggest that if the bind parameter is nonzero (i.e. __nonzero__ method call returns
>> True) then it is assumed there *are* params. This way we don't have to get into is it an empty
>> sequence, is it None, etc.
> There is no __nonzero__ in Python 3 anymore. The method was renamed
> to __bool__ :-)
>
> I'm also not sure whether we should allow any of these:
>
> .execute(sql, None)
> .executemany(sql, None)

Personally I'd like to allow the above. If a driver has:


     def execute(self, sql_text, params=None):
         if params:
             # process params, more on this later
         # else assume no params

this will be allowed but the param will be ignored

> .executemany(sql, [None])

This is OK too, this drops in the the "we have params logic", and it is 
a single param that is NULL.


> In order to support your suggestion, database module would have to
> call the __nonzero__/__bool__ slot on the parameters, which could
> be an expensive operation with no real value in case the parameters
> are not None,

Do you have an example of then this would be expensive? I do not 
understand the "real value in case the parameters
are not None" comment, could you expand (possibly with a short example) 
on this please?

> or even be impossible in the case of iterators.

An iterator with values left is always going to be true, if empty it is 
false:

 >>> params = xrange(10)
 >>> if params:
...     print True
...
True
 >>> params = xrange(0)
 >>> if params:
...     print True
...
 >>>

this is true for generators too.

> Using the slot, we'd also allow things like these:
>
> .execute(sql, 0)
> .executemany(sql, 0.0)

I'm OK with these being allowed through, they would NOT be treated as 
params. If there are param markers in the sql, a (DBMS) error should be 
raised with "missing bind params" if the driver doe snot then pass in 
params.

> .executemany(sql, [0])

Again, this is valid, a single tuple with an integer value of zero.


> which can hide programming errors.

The dbms should raise an error for this, this shouldn't be hidden.

> Using the __len__ slot would work, though, unless you have an
> iterator...

I'm not keen on that. For instance strings are sequences and if we have:


.execute(sql, 'hello')

it would be valid.

I don't have a good answer for this one, I've been toying with the idea 
of a string check for param but that seems overly excessive.

Coming back to param checks:

     def execute(self, sql_text, params=None):
         if params:
             for param in params:
                 do stuff....
         # else assume no params

I've been toying with the idea of catching TypeErrors to detect cases 
like .execute(sql, 1) in the driver (and wrappers) that I maintain. E.g.:

 >>> for x in 1:
...     print x
...
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
TypeError: 'int' object is not iterable

Is catching a type error a good way to check if something isn't a 
sequence? What are other people doing? This doesn't handle users who 
accidentally pass in a single string (instead of a tuple with a single 
string).


> Which leads to another idea for DB-API 3: I think we should allow
> iterators for the parameter list in .executemany(), and perhaps
> even for the parameter sequence in .execute().

They should be allowed and I think you are correct we should be explicit 
in the spec on this.

I actually have code that relies on passing a generator into executemany 
(the easiest example is to pass a cursor in for instance or an ETL 
function that takes a cursor as a param) .

Chris