formating query with empty parameter

Tim Chase python.list at tim.thechases.com
Mon May 25 18:16:48 CEST 2009


>> To stave off this problem, I often use:
>>
>>    values = [
>>     data['a'],
>>     data['b'],
>>     data['c'],
>>     data['d'],
>>     data['e'],
>>     data['f'],
>>     data['g'],
>>     ]
>>    params = ', '.join('%s' for _ in values)
>>    query = """
>>      BEGIN;
>>        INSERT INTO table
>>          (a,b,c,d,e,f,g)
>>        VALUES (%s);
>>      COMMIT;
>>      """ % params
>>    self.db.execute(query, values)
>>
> 
> Why do you pass values to execute() if you already have your query
> formatted?

The "params" might be better named "placeholders".  So after the

    query = "..." % params

the query looks like your original (go ahead and print "query" to 
see), only the number of placeholders ("%s") is guaranteed to 
match the number of values you pass in during the execute() call. 
  The second iteration I gave goes one step further to ensure 
that the "(a,b,c,d,e,f,g)" portion also matches in count to the 
number of values and place-holders to be used.

Once you have a SQL query that matches what you plan to pass 
(based on your initial data-structure:  a list/tuple or a 
dictionary), then you call execute(query, values) to have the 
database then associate the parameter-placeholders ("%s") with 
the corresponding value from "values".

-tkc





More information about the Python-list mailing list