[Baypiggies] newbie troubles with dbapi parameter formatting

[Jimmy Retzlaff]

Ken-ichi wrote:
> I'm new, live up in Berkeley, and am trying to learn Python.

Welcome!

> I'm currently struggling with database interaction, specifically with
> PostgreSQL and the Psycopg2 module, which implements the Python DB API
> spec.  I'm trying to do something like
> 
> cursor.execute('SELECT * FROM table WHERE column IN (%s)', (a_list,))
> 
> where a_list is a list of strings.  I've tried something like this:
> 
> cursor.execute('SELECT * FROM table WHERE column IN (%s)', (',
> '.join(a_list),))
> 
> and
> 
> cursor.execute('SELECT * FROM table WHERE column IN (%s)', ("',
> '".join(a_list),))
> 
> but they don't single-quote the strings properly (it thinks the comma
is
> a separate string, and so encloses it in single-quote again for
> something like ('val1'',''val2'',''val3').
> 
> My working solution is to just format the query before feeding it to
the
> cursor, but I feel like the execute() method should support arbitrary
> lists of parameters.  I just can't find any docs on it.  Does anyone
> know how to do this?  Am I thinking about this in completely the wrong
> way?

I haven't seen a dbapi module that will help with your particular case
directly. The parameter support in execute looks at the query template
to determine how many SQL values to generate, not the value(s) you
supply. In your case the answer is 1 so it tries to turn whatever you
pass in into 1 SQL value. It works great for things like "SELECT * FROM
table WHERE column IN (%s, %s, %s)" when you supply a tuple of 3
parameters, but not for your case. What I do in this case is generate
the SQL template dynamically:

listTemplate = ','.join(['%s']*len(a_list))
sqlTemplate = 'SELECT * FROM table WHERE column IN (%s)' % listTemplate
cursor.execute(sqlTemplate, a_list)

Those 3 lines are untested, but hopefully it's close enough to get you
moving forward.

Jimmy