Inserting multiple dictionary objects into a MySQL database using python and MySQLdb

Question

I have been struggling with this for hours and I feel like crying now as I'm unable to fathom out what is happening.

Here is a simplified version of my data:

mydata = [ { 'id': 123, 'thing': 'ghi', 'value': 1 }, { 'id': 456, 'thing': 'xyz', 'value': 0 } ]

This is the code I have:

import MySQLdb as mdb

con = None
con = mdb.connect('localhost', 'testing', 'anothervalue', 'andanother');
cur = con.cursor()

sql = "INSERT INTO `tablename` ( `id`, `thing`, `value` ) VALUES ( %(id)s, %(thing)s, %(value)s )"
cur.executemany( sql, ( mine for mine in mydata ) )
con.close()

What I expected to happen was that 2 rows would be inserted into tablename. What actually happens is that the script executes without any errors and no rows are inserted.

What am I doing wrong? If I do a single INSERT by hand it inserts into the table properly so I think know its not a problem with the MySQL database but rather how I'm passing the variables into the database.

An additional question I have is how to I insert value as a float? At the moment I have the definition of value in the table as TINYTEXT NOT NULL, I would like this to be FLOAT NOT NULL but I'm not sure how to handle the substition above.

mydata is a list of dictionaries (as defined above).

user1464409
– user1464409

2013-02-06 14:22:28 +00:00
Commented Feb 6, 2013 at 14:22 — user1464409
– user1464409, Commented Feb 6, 2013 at 14:22

Martijn Pieters · Accepted Answer · 2013-02-06 14:40:39Z

6

There is no need to use a generator to loop over mydata. Just pass in mydata directly:

cur.executemany(sql, mydata)

The database adapter will loop over mydata for you and a generator expression only inserts a needless extra loop.

If you do not get any error messages but there are no changes either, check the following:

Make sure you commit the transaction; run con.commit() after the .executemany() call.
Tripple-check that mydata is not empty.

The database adapter will correctly handle float values for you; if a column is marked as FLOAT NOT NULL and you pass in a Python float value for that column, Things Just Work. That's what SQL parameters are for, handling quoting of different datatypes correctly.

edited Feb 6, 2013 at 14:40

answered Feb 6, 2013 at 14:18

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1464409 Over a year ago

When I do that nothing happens. The script executes and finishes without an error, no rows are inserted.

Martijn Pieters Over a year ago

@user1464409: Please share what mydata is then.

user1464409 Over a year ago

mydata = [ { 'id': 123, 'thing': 'ghi', 'value': 1 }, { 'id': 456, 'thing': 'xyz', 'value': 0 } ]

Martijn Pieters Over a year ago

@user1464409: I suspect that executemany() does not support dictionaries. Checking the code now.

Martijn Pieters Over a year ago

@user1464409: Are you 100% certain that that list is not empty? The code certainly handles %(name)s and dict interpolation properly for both .execute() and .executemany().

|

Guddu · Accepted Answer · 2013-02-06 16:05:47Z

By default, Auto-Commit is enabled in MYSQL. To check the current configuration in your setup, please connect to your MYSQL instance and execute the following.

mysql> select @@autocommit;    
+--------------+    
| @@autocommit |    
+--------------+    
|            1 |    
+--------------+
1 row in set (0.00 sec)

If the value is 1 then it is enabled...Else not....

To be honest, your original snippet works fine for me. So this could mostly be an auto-commit issue.

Try adding the following line just before con.close() in your original code.

con.commit()

You could still give a try with my snippet posted below if autocommit is enabled...Are you able to change the mydata list members from Dictionary to Tuple type? If yes then please see if the following snippet helps you.

import MySQLdb as mdb

mydata = [ { 'id': 123, 'thing': 'ghi', 'value': 1 }, { 'id': 456, 'thing': 'xyz', 'value': 0 } ]
mydata_modified=[ (123, 'ghi', 1 ), ( 456, 'xyz', 0 ) ]

con = None
con = mdb.connect('localhost', 'testing', 'anothervalue', 'andanother');
cur = con.cursor()

cur.executemany( """INSERT INTO tablename ( id, thing, value ) VALUES ( %s, %s, %s )""", mydata_modified )
con.commit()
con.close()

Matt · Accepted Answer · 2016-01-04 18:15:34Z

0

just an FYI to expand on Guddu, you can convert your dictionary to tuple type by doing something like:

mydata_modified = []
for x in mydata:
   mydata_modified.append(tuple(x.values()))

answered Jan 4, 2016 at 18:15

Matt

431 silver badge7 bronze badges

2 Comments

Sergei Voronezhskii Over a year ago

wrong, because the order of x.values() can be not the same as expected sql query

newBike Over a year ago

so wrong. the order is not gurantteed. I think python is not that dynamic friendly. Just make a tuple and sorted the tuple fields in order.

blueflow · Accepted Answer · 2023-08-21 07:51:00Z

0

tuple_mydata = [tuple(mine.values()) for mine in mydata]
cur.executemany(sql, tuple_mydata)

answered Aug 21, 2023 at 7:51

blueflow

1

Collectives™ on Stack Overflow

Inserting multiple dictionary objects into a MySQL database using python and MySQLdb

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related