[egenix-users] How to do "where foo in ?" clause in mxodbc?

[M.-A. Lemburg]

On 2008-04-15 17:15, Cliff Xuan wrote:
> Hi there,
> 
> I am trying to do this in mxodbc, but apparently it doesn't work. Would really appreciate if someone can enlighten me.
> 
>>>> names = ('Andy','Bob', 'Charlie')
>>>> sql = "select * from customer where firstname in ?"
>>>> cur.execute(sql, (names,))
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> ProgrammingError: ('37000', 0, '[Microsoft][ODBC SQL Server Driver]Syntax error or access violation', 4504)
> 
> The objective is to be able to use the SQL "in" clause, e.g. select * from customer where firstname in ('Andy','Bob', 'Charlie').

The binding parameters only allow scalar values to be passed to the
database. Sequences are not supported by ODBC.

You have two options:

1. Apply SQL-quoting to the sequence to turn it into a string that
    you can embed into the SQL statement.

    This is easy in most cases, since the Python tuple representation
    is compatible with the SQL dialects of many databases.

    cur.execute("select * from customer where firstname in %r" % names)

    For non-ASCII data, you will have to apply proper SQL quoting.

2. You write the sequence data into a temporary table and reference
    this table in your query.

    This option is better if you have long sequences, since the length
    of the SQL statements that you can send to the database is usually
    limited.

Option 2 is also the faster variant if you use the sequence multiple
times.

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 15 2008)
 >>> 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