473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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-

Jan 27 '06 #1
8 6180
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
Jan 27 '06 #2
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
Jan 27 '06 #3
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>

Jan 27 '06 #4
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-

Jan 27 '06 #5
Derick van Niekerk wrote:
> 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
Jan 27 '06 #6
Derick van Niekerk wrote:
> 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>

Jan 27 '06 #7
(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

Jan 27 '06 #8
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
Jan 27 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default...
7
by: rickle | last post by:
I'm trying to compare sun patch levels on a server to those of what sun is recommending. For those that aren't familiar with sun patch numbering here is a quick run down. A patch number shows...
1
by: JT | last post by:
im trying to create a function that accepts a sql statement as a parameter, makes a db connection, returns a recordset, and inserts all items from the recordset into a data dictionary - then i want...
15
by: barbaros | last post by:
Hello everybody, I need some advice on the following problem. I want to write a program (in C or C++) which will build a huge dictionary (hash array, if you prefer). The keys are strings, the...
27
by: Pom | last post by:
Hello I want to convert a Mysql resulset to a dictionary. I made some code myself, and want to ask you if I do this the right way. def remapmysql(a): return (a, (a)) def test_map():
1
by: Martin P. Hellwig | last post by:
Hi all, I'm thinking to speed up a process, I like to use multiple threads to get data fractions from multiple servers and place those data fragments into a local dictionary for further...
2
by: lazy | last post by:
Hi, I have a dictionary something like this, key1=>{key11=> , key12=> , .... } For lack of wording, I will call outer dictionary as dict1 and its value(inner dictionary) dict2 which is a...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
GTXY20
by: GTXY20 | last post by:
Hi all, I currently have a CSV file like so 1,a 1,b 1,c 2,a 2,b 2,b
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.