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

[Derick van Niekerk]

I have found many posts that deal with writing a dictionary to MySQL in
a blob field - which I can't imagine why anybody would want to do it.

I want to write each element of a dictionary onto a db table. The keys
would match the fieldnames. Is there something that would make this job
easier? i.e. how do I write an entire dictionary into a db table as a
row? I have an idea that it is something very simple - possibly even
one line of code...

-d-

 

[Robin Haswell]

I have found many posts that deal with writing a dictionary to MySQL in
a blob field - which I can't imagine why anybody would want to do it.

I want to write each element of a dictionary onto a db table. The keys
would match the fieldnames. Is there something that would make this job
easier? i.e. how do I write an entire dictionary into a db table as a
row? I have an idea that it is something very simple - possibly even
one line of code...

-d-

Try looking at http://pear.php.net/db , in the documentation, see
DB::autoPrepare and DB::autoExecute.

-Rob

 

[Robin Haswell]

I have found many posts that deal with writing a dictionary to MySQL in
a blob field - which I can't imagine why anybody would want to do it.

I want to write each element of a dictionary onto a db table. The keys
would match the fieldnames. Is there something that would make this job
easier? i.e. how do I write an entire dictionary into a db table as a
row? I have an idea that it is something very simple - possibly even
one line of code...

-d-

Oh crap sorry, ignore my last message - I thought I was still looking at
c.l.php

I can't answer your question in Python, sorry

-Rob

 

[Fredrik Lundh]

I have found many posts that deal with writing a dictionary to MySQL in
a blob field - which I can't imagine why anybody would want to do it.

it might be useful if you have a bunch of unknown properties (e.g. configuration
parameters for some external parameters), and no need to access the properties
as individual parameters via the database.

I want to write each element of a dictionary onto a db table. The keys
would match the fieldnames. Is there something that would make this job
easier? i.e. how do I write an entire dictionary into a db table as a
row? I have an idea that it is something very simple - possibly even
one line of code...

some database drivers have API:s for this, but if you're using a generic
DB-API driver, it's pretty straightforward to generate an appropriate SQL
statement on the fly; e.g.

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))

</F>

 

[Derick van Niekerk]

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))

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. [' " \ ) ( ...]?

Is there a python function to do it? part of the mysql-python module,
maybe?

-d-

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

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))

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

 

[Fredrik Lundh]

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))

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>

 

[Derick van Niekerk]

(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?)

I've never seen it done in this way before, but I do come from a PHP
point of view.

I've only started with python this week, so a lot of the way it does
things are new to me, So far, all of the differences are good.

Thanks for the help

 

[Carsten Haese]

(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?)

Yes, the need to roll queries by inserting parameters directly into the
query string is definitely a PHP misfeature (for versions less than 5),
since the database access modules don't accommodate parametrized
queries. PHP5 finally introduced a standardized database API that allows
parametrized queries. Of course, Python has had this since 1996.

-Carsten