Dictionary inserts into MySQL (each key in its own field)

[Fredrik Lundh]

Derick van Niekerk wrote:

> [quote]
> d = {"spam": "1", "egg": "2"}
>
> cols = d.keys()
> vals = d.values()
>
> stmt = "INSERT INTO table (%s) VALUES(%s)" % (
>     ",".join(cols), ",".join(["?"]*len(vals))
>     )
>
> cursor.execute(stmt, tuple(vals))
> [/quote]
>
> I will be using the python-mysql API. This looks like what I am looking
> for. I just have a question about the last join statment. In this case
> it would just create a string = '?,?' - wouldn't it?

yup.  those are parameter markers (replace with "%s" if that's what
your database API is using).  each ? (or %s) corresponds to a value
in the tuple argument to execute.  e.g.

    execute("INSERT INTO table (foo, bar) VALUES(?, ?)", ('one', 'two'))

is the same thing as

    execute("INSERT INTO table (foo, bar) VALUES('one', 'two')")

except that the former is 1) more efficient, and 2) safe, since the
parameter values are passed directly to the SQL engine, without
going through the SQL parser.  there's no need to escape them.

> Now - how do I escape the string for characters that might break
> the script e.g. [' " \ ) ( ...]?

you don't -- that's why the values are passed in as a separate argument
to execute.

(just curious, but from where do people get the idea that arbitrary data
just have to be inserted into the the SQL statement text all the time?  is
this some PHP misfeature?)

</F>