MySQLdb and executemany error “incomplete format”
Okay, very weird problem today. I am not sure what to make of it… when using the latest version of MySQLdb (1.2.2.final.0) which is the version in Ubuntu 7.10 Gutsy, I am getting an “incomplete format” error when performing an executemany. My variables are presented as a list of dictionaries as I’m using named/mapped variables in the SQL statement. Using the exact same code with MySQLdb (1.2.1.final.2) is fine (the version in Ubuntu Feisty 7.04).
So for completeness sake, I have documented the scenario like this:
On a MySQL server, you just need a simple little table:
CREATE TABLE `test_table` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `test_col_1` varchar(20) NOT NULL, `test_col_2` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
Then the Python code (obviously you’ll need to set the connection stuff appropriately):
import MySQLdb print "-----------------------------------------------------" print MySQLdb.version_info print "-----------------------------------------------------" sqlStringItems = """INSERT INTO test_table (test_col_1,test_col_2) VALUES (%(test1)s, %(test2)s);""" dtlDict = {'test1':'test value 1','test2':'test value 2'} con = MySQLdb.connect(host="localhost",user="myuser",passwd="mypassword",db="test") cur = con.cursor() exitval = cur.executemany(sqlStringItems,[dtlDict]) print "exit value from executemany= " + str(exitval) cur.close()
version (1, 2, 2, ‘final’, 0) yields:
ValueError: incomplete format
Whereas version (1, 2, 1, ‘final’, 2) works fine.
Also, doing the same thing, but with execute instead of executemany works fine (and of course taking the dict out of the list). So this seems to be purely an issue with executemany. Looking at the MySQLdb code, it’s obvious that between the two versions it was pretty radically changed.
I guess I need to report this as a bug, but I’m just finding it hard to believe that I didn’t do something wrong.
Update (2008-06-03)
Back in January, I reported this issue (1874176). Then in late April, Raphael Guillet submitted a possible fix. I haven’t spent much time looking at the right vs. wrong of the fix, but I have tried it and it appears to work. So for those looking, here’s the possible fix which as near as I can tell has still not been cut into the official version.
The change is in the cursors.py file. So you have to hunt that down wherever it is stored in your particular OS version and/or install.
Comment out lines 201 and 202:
#e = m.end(1) #qv = m.group(1)
And then add right after that:
e=len(query) qv = query[p:e]
I’ll try to continue to monitor this.
6 Comments so far
Leave a reply
sqlStringItems = “”"INSERT INTO test_table
(test_col_1,test_col_2)
VALUES
(%s, %s);”"”
#and use tuples
args=[('testcol1 data1','testcol2 data1),('testcol1 data2','testcol2 data2)]
res=cur.executemany(sqlStringItems,args)
#this should work for you if not replace the args list of tuples with a tuple of tuples
garyl, your suggestion would defeat the purpose of using a dict (i.e. a map) for supplying values. While my example is trivial (2 fields)… how about if you had 10 or 20 values to insert?
Hey ScW, just wondering if you found a way around this problem. I’m experiencing the same issue.
results = [{'test1':'test value 1','test2':'test value 2'}{'test3':'test value 3','test4':'test value 4'}]
c.executemany(sqlStringItems,results)
Traceback (most recent call last):
File “”, line 1, in
File “/var/lib/python-support/python2.5/MySQLdb/cursors.py”, line 217, in executemany
self.errorhandler(self, exc, value)
File “/var/lib/python-support/python2.5/MySQLdb/connections.py”, line 35, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: incomplete format
Hi there,
Yep, same problem with me here…
Did you report it as a bug already?
Can’t see what we are doing wrong, and I did spend quite a lot of time on this issue…
Just confirming, we are seeing the same thing here, six months after this was posted. Was this ever reported as a bug? What is the current status?
updated the post with the “quick and dirty” fix presented on the sourceforge site.