[Baypiggies] newbie troubles with dbapi parameter formatting

[Aahz]

On Fri, Mar 24, 2006, Jimmy Retzlaff wrote:
>
> 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)

My preference is to spell out the list explicitly rather than relying on
execute() to handle the interpolation:

in_clause = ','.join(["'%s'" for x in a_list)
sql = "SELECT * FROM table WHERE column IN (%s)" % in_clause
cursor.execute(sql)

Note that I'm always careful to surround SQL queries in double-quotes or
triple-quotes in case I add a manual parameter with a string later...
-- 
Aahz (aahz at pythoncraft.com)           <*>         http://www.pythoncraft.com/

"Look, it's your affair if you want to play with five people, but don't
go calling it doubles."  --John Cleese anticipates Usenet