[DB-SIG] trouble with list formatting

[M.-A. Lemburg]

On 2007-06-04 16:46, Roger Doger wrote:
> The following use of the API with MySQL works fine
> 
>> items = [1, 2]
>> execute('SELECT * from table1 WHERE field1 IN %s', (items,))
> resultant query string: SELECT * from table1 WHERE field1 IN ('1', '2')
> 
> By contrast, the following yields the message "error in your SQL syntax"
> 
>> items = [1]
>> execute('SELECT * from table1 WHERE field1 IN %s', (items,))
> resulting query string: SELECT * from table1 WHERE field1 IN ('1',)
> 
> Thus it seems the only way to manage this situation is to use this
> stilted construction:
> 
>> if len(items)==1:
>>     execute('SELECT * from table1 WHERE field1 IN (%s)', items[0])
>> else:
>>     execute('SELECT * from table1 WHERE field1 IN %s', (items,))
> 
> Is this just a shortcoming in how the API handles formatting of lists
> or is there a cleaner way to handle this?

Binding parameter are usually only meant to bind data items
to an SQL statement, e.g. to provide data for INSERT, UPDATE,
etc.

Lists are usually not supported as datatype, only scalars.

The fact that it works in the above case is merely a side effect
of Python using a formatting that is similar to SQL. However,
this won't always work, since Python uses double quotes to
quote strings which include single quotes (the SQL string
literal quoting character):

>>> l = ["'a'", "b"]
>>> print str(l)
["'a'", 'b']

PS: Yet another reason to avoid format param style :-)

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jun 05 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611