[Baypiggies] newbie troubles with dbapi parameter formatting

[Chris Clark]

Shannon -jj Behrens wrote:
> On 3/24/06, Aahz <aahz at pythoncraft.com> wrote:
>   
>> 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.
..snip
>> 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...
>>     
>
> Also be sure to protect yourself against SQL injection attacks using
> whatever is appropriate to escape stuff in the a_list.
>
>   
Excellent advice from jj, I would go with a hybrid approach (note 
untested!) to create a parameter marker list and let the database driver 
deal with escape stuff:

    ...
    a_list=(1,2,3) ##etc.
   
    var_marker = r'%s'
    #var_marker = '?'
    #var_marker = r'%s'
    # etc.based on whatever driver.paramstyle defines
   
    # not very pythonic, hopefully this is easy to read
    # albeit not efficient
    in_clause = var_marker
    for x in a_list[1:]:
        in_clause = in_clause + ',%s'%(var_marker)
  
    sql = "SELECT * FROM table WHERE column IN (%s)" % in_clause
    cursor.execute(sql, a_list)
    ..

Regards,

Chris