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

[M.-A. Lemburg]

Hi Cliff,

On 2008-04-16 17:14, Cliff Xuan wrote:
> Hi Marc-Andre,
> 
> Thanks very much for your reply.
> 
> I had a go with the simpler first approach and it worked quite well even though a bit slow because I'm using the tuple many times.

If you're using the same query (with the embedded tuple) over and
over again, it's faster to assign the statement to a variable.

Provided you use a dedicated cursor for the query, mxODBC will then
reuse the already prepared statement for subsequent queries:

stmt = "select * from customer where firstname in %(s)" % repr(names)[1:-1]
...
cur.execute(stmt)
...
cur.execute(stmt)
...
cur.execute(stmt)
...
cur.execute(stmt)

(only the first .execute() call will run through the prepare step,
the subsequent calls will reuse the already prepared access plan)

Note that for MS SQL Server, we have found that using cur.executedirect()
often results in better performance. You might want to experiment with
that as well.

> I'm sure you know this little trick I found but just to make life easier for other people reading the email, I found it's safer to use the following:
>>>>> names = ('Andy','Bob', 'Charlie')
>>>> cur.execute("select * from customer where firstname in %(s)" % repr(names)[1:-1])
> 
> It handles tuple with a trailing comma, for example:
>>>> names = ('Andy','Bob','Charlie',)
> 
> Thanks
> Cliff
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal at egenix.com]
> Sent: Tue 4/15/2008 4:44 PM
> To: Cliff Xuan
> Cc: egenix-users at egenix.com
> Subject: Re: [egenix-users] How to do "where foo in ?" clause in mxodbc?
>  
> 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 16 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