insert a dictionary into sql data base

[Fredrik Lundh]

David Bear wrote

> Fredrik Lundh wrote:
>
> > cursor.execute(
> >     "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
> >     *values
> >     )
>
> Thanks for the hint. However, I don't understand the syntax.
>
> I will be inserting in to postgresql 8.x. I assumed the entire string would
> be evaluated prior to being sent to the cursor.

Looks like you missed advice 1-3.  I'll take it again: DON'T USE STRING
FORMATTING TO INSERT VALUES IN A DATABASE.  Sorry for shouting,
but this is important.  Parameter passing gives you simpler code, and
fewer security holes.

> However, when I attempt to manual construct the sql insert statment
> above I get an error:
>
> >>> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
> *values)
>   File "<stdin>", line 1
>     print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
> *values)
>                                                                       ^
> SyntaxError: invalid syntax

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>