Re: [Pypgsql-users] How to pass in arguments for 'in' operator

[Gerhard <ger...@gm...>]

* Edmund Lian <el...@in...> [2002-09-16 21:52 -0400]:
> I'm stumped... could somebody please help me? I have a query that uses the
> in operator:
> 
> matchDict = {'my_arg': [1, 2, 3]}
> sql = "select * from my_table where col_name in (%(my_arg)s)"
> c = cursor.execute(sql, matchDict)

The problem is that currently, pyPgSQL escapes tuples and lists as
PostgreSQL ARRAYs, which is nice when you need it, but doesn't help very
much for appropriate quoting for the in-Operator. The plan is to make
quoting for the in-operator the default in pyPgSQL 3.0 and have a
PgArray type (likely just a subclass of list) for people needing ARRAYs.

> What should I do to 'my_arg' in matchDict so that it is correctly
> substituted into the SQL statement by pypgsql? I.e., I need the
> substitution to yield:
> 
> select * from my_table where col_name in (1, 2, 3);

There is a solution in current pyPgSQL, but it isn't very nice: You
can't use DB-API style quoting, but you need to use pyPgSQL's _quote()
function and construct the query string yourself:

sql = "select * from my_table where col_name in (%s)" % \
   ",".join(map(PgSQL._quote,  [3,4,5])) 

If there are any additional query parameters, you can then use DB-API
style quoting for these. This would look like:
    
sql = "select * from my_table where id=%%s or col_name in (%s)" + \
   ",".join(map(PgSQL._quote,  [3,4,5])) 

cursor.execute(sql, (25,))

Note the double percent signs.

> How does the answer change if my_arg is a list of strings ['apple',
> 'orange', 'pear'], and the 'in' operator is applied to a varchar column?
> I.e., the substitution should yield:
> 
> select * from my_table where col_name in ('apple', 'orange', 'pear');

See above, PgSQL._quote will do the right thing.

-- Gerhard