formating query with empty parameter

[Pet]

On May 25, 3:26 pm, Tim Chase <python.l... at tim.thechases.com> wrote:
> >>>> 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)
>

Why do you pass values to execute() if you already have your query
formatted?

> 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