insert a dictionary into sql data base

[Carsten Haese]

On Mon, 05 Dec 2005 18:00:21 -0700, David Bear wrote
> Fredrik Lundh wrote:
> > DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT.  Use string
> > formatting to insert the field names, but let the database layer deal with
> > the values.
> > 
> > If you want to do things in two steps, do the fields formatting first
> > 
> >     query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))
> > 
> > and pass the query and the values sequence to the database layer:
> > 
> >     cursor.execute(query, values)
> > 
> > The database will take care of the rest.
> > 
> > </F>
> 
> I think I'm missing some important documentation somewhere. Here's 
> what I tried (using both % and $ signs):
> 
> >>> sql
> 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) 
> VALUES
> (%s);'
> 
> >>> sql2
> 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) 
> VALUES
> ($s);'
> >>> values
> ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
> 
> >>> cursor.execute(sql1, values)
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> NameError: name 'sql1' is not defined
> >>> cursor.execute(sql, values)
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
>     self.executemany(operation, (params,))
>   File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
> executemany
>     raise OperationalError, "internal error in '%s': %s" % (sql,err)
> pg.OperationalError: internal error in 'INIT': not all arguments converted
> during string formatting
> 
> I get the same error if using $ sign.
> 
> When I look at the pygresql dbapi official site at
> http://www.pygresql.org/pgdb.html
> 
> "this section needs to be written"...
> 
> I would really appreciate some more examples on using pgdb (pygresql)

It appears that Fredrik gave you good advice but bad example code. The example
he gave you constructs an insert query with only one parameter placeholder.
You'll need as many placeholders as the number of values that are inserted.

The following example should work better:

def insertDict(curs, tablename, data):
  fields = data.keys()
  values = data.values()
  placeholder = "%s"
  fieldlist = ",".join(fields)
  placeholderlist = ",".join([placeholder] * len(fields))
  query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
                                              placeholderlist)
  curs.execute(query, values)

The main thing to note here is that we *are* using string formatting to build
a query that's based on a variable table name and a variable column list, but
we *are not* using string formatting to fill in the values.[*]

On a somewhat related note, it's unfortunate that many database modules use %s
 as parameter placeholders, because it makes it too tempting to write bad code
such as

cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad, uses
vulnerable and error-prone string formatting

instead of

cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good, uses
parameters.

[*] This code blindly trusts that the table name and dictionary keys don't
contain SQL injection attacks. If the source of these is not completely
trustworthy, the code needs to be hardened against such attacks. I'll leave
that as an exercise for the reader.

Hope this helps,

Carsten.