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

M.-A. Lemburg mal at egenix.com
Tue Apr 15 18:44:27 CEST 2008

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

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

Marc-Andre Lemburg

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

More information about the egenix-users mailing list