[egenix-users] How to do "where foo in ?" clause in mxodbc?
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
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