[Baypiggies] newbie troubles with dbapi parameter formatting

Chris Clark Chris.Clark at ingres.com
Fri Mar 24 21:12:22 CET 2006

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.
>> 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)



More information about the Baypiggies mailing list