formating query with empty parameter

Tim Chase python.list at tim.thechases.com
Mon May 25 15:26:29 CEST 2009


>>>> if one of parameter in values is empty, I'm getting
>>>> TypeError: not enough arguments for format string
>>>> But how to handle such situation? It is ok for DB, that some of values
>>>> are empty.
>>>> def __insert(self, data):
>>>>         query = """
>>>>             BEGIN;
>>>>                 INSERT INTO table
>>>>                     (a,  b,  c,  d,  e,  f,  g)
>>>>                     VALUES
>>>>                     (%s, %s, %s, %s, %s, %s, %s);
>>>>             COMMIT;
>>>>             """
>>>>         values = [
>>>>             data['a'],
>>>>             data['b'],
>>>>             data['c'],
>>>>             data['d'],
>>>>             data['e'],
>>>>             data['f'],
>>>>             data['g']
>>>>             ]
>>>>         self.db.execute(query, *values)
> 
> Sorry, for previous quick post. Actually it works now, I've missed
> some other parameter in list

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)

If the indexes are named the same as the fieldnames, or you have 
a mapping of them, I tend to use something like

   field_map = {
     # dictionary_index: database_fieldname
     # data['a'] -> table.f1
     'a': 'f1',
     'b': 'f2',
     'c': 'f3',
     # ...
     }
   name_value_pairs = (
     (data[k], v)
     for k,v
     in fieldmap.iteritems())
   values, fieldnames = zip(*name_value_pairs)
   # may want to do fieldname escaping here:
   fieldname_string = ', '.join(fieldnames)
   params = ', '.join('%s' for _ in ordering)

   query = """
     BEGIN;
       INSERT INTO table (%s) VALUES (%s);
     COMMIT;
     """ % (fieldname_string, params)
   self.db.execute(query, values)

-tkc







More information about the Python-list mailing list