Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
What is mydata? – Martijn Pieters Feb 6 at 14:17
mydata is a list of dictionaries (as defined above). – user1464409 Feb 6 at 14:22

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
When I do that nothing happens. The script executes and finishes without an error, no rows are inserted. – user1464409 Feb 6 at 14:21
@user1464409: Please share what mydata is then. – Martijn Pieters Feb 6 at 14:22
mydata = [ { 'id': 123, 'thing': 'ghi', 'value': 1 }, { 'id': 456, 'thing': 'xyz', 'value': 0 } ] – user1464409 Feb 6 at 14:23
@user1464409: I suspect that executemany() does not support dictionaries. Checking the code now. – Martijn Pieters Feb 6 at 14:25
@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(). – Martijn Pieters Feb 6 at 14:38
show 5 more comments

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()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.