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

[Gerhard Häring]

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? Other than that,
> it is pretty easy to understand. Now - how do I escape the string for
> characters that might break the script e.g. [' " \ ) ( ...]? [...]

You don't need to escape the strings, because the example code uses the 
parametrized form of the cursor.execute statement, and the DB-API module 
will just do the right thing.

The example code will maybe not work like this, because IIRC MySQLdb 
uses paramstyle = "format", and not paramstyle = "qmark". I. e. you will 
have to use %s as placeholders in the query, and not ?. So you would 
have to replace the ",".join(["?"]*len(vals)) with
",".join(["%s"]*len(vals)).

-- Gerhard