insert a dictionary into sql data base

[Fredrik Lundh]

David Bear wrote:

> The dictionary keys are the field names. The values are the values to be
> inserted.
>
> I am looking for a good pythonic way of expressing this, but I have a
> problem with the way lists are represented when converted to strings.
>
> Lets say my dictionary is
>
> data = {"fname": "todd", "lname": "Bush"}
> fields = data.keys()
> vals = []
> for v in fields:
>    vals.append(data[v])
>
> sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)
>
> but fields and vals are represented as lists. So, then I need to strip the
> [] from them, but then ... there must be an easier way.
>
> Any advise?

1) use parameters to pass in the values (see
   http://www.python.org/peps/pep-0249.html )

2) use parameters to pass in values

3) use parameters to pass in values

4) here's a simplified version of your code:

data = {"fname": "todd", "lname": "Bush"}

fields = data.keys()
values = data.values()

cursor.execute(
    "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
    *values
    )

(this assumes that your database uses %s for parameters; if it uses
? instead, replace "%%s" with "?".  see the paramstyle documentation
in the db-api docs (pep 249) for more info)

</F>