Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Dictionary inserts into MySQL (each key in its own field)

Reply
Thread Tools

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

 
 
Derick van Niekerk
Guest
Posts: n/a
 
      01-27-2006
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-

 
Reply With Quote
 
 
 
 
Robin Haswell
Guest
Posts: n/a
 
      01-27-2006
On Fri, 27 Jan 2006 00:03:30 -0800, Derick van Niekerk wrote:

> 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
 
Reply With Quote
 
 
 
 
Robin Haswell
Guest
Posts: n/a
 
      01-27-2006
On Fri, 27 Jan 2006 00:03:30 -0800, Derick van Niekerk wrote:

> 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
 
Reply With Quote
 
Fredrik Lundh
Guest
Posts: n/a
 
      01-27-2006
Derick van Niekerk wrote:

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



 
Reply With Quote
 
Derick van Niekerk
Guest
Posts: n/a
 
      01-27-2006
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))
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-

 
Reply With Quote
 
=?ISO-8859-1?Q?Gerhard_H=E4ring?=
Guest
Posts: n/a
 
      01-27-2006
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))
>
>
> 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
 
Reply With Quote
 
Fredrik Lundh
Guest
Posts: n/a
 
      01-27-2006
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))
>
>
> 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>



 
Reply With Quote
 
Derick van Niekerk
Guest
Posts: n/a
 
      01-27-2006
Quote:
(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

 
Reply With Quote
 
Carsten Haese
Guest
Posts: n/a
 
      01-27-2006
On Fri, 2006-01-27 at 05:47, Fredrik Lundh wrote:
> (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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Its a bird, its a plane, no ummm, its a Ruide thunk Ruby 1 03-30-2010 11:10 AM
DBI:mysql how to do multiple inserts/modification? Tomasz Chmielewski Perl Misc 3 02-13-2008 08:08 PM
Dictionary inserts and threads Martin P. Hellwig Python 1 01-27-2007 04:49 PM
perl and mysql: slow inserts with innodb rickyars@gmail.com Perl Misc 7 10-04-2006 09:38 PM
I have built my own (simple) thread manager [TM], but just found java 5 has its own. Saverio M. Java 0 07-03-2006 08:52 AM



Advertisments