Python+MySQL "not all arguments converted during string formatting"

[unhappy_mage]

I'm writing a Python program that interfaces with a MySQL database using the standard MySQLdb interface. I construct a list of tuples like this:

[(1L, 115L, 50, 50), (2L, 115L, 44, 44), (3L, 115L, 85, 85), (4L, 115L, 46, 46), (5L, 115L, 81, 81)]

and then pass it to acursor.executemany(), like so:

c.executemany("""INSERT INTO r1r (candidate, vid, score) VALUES
(%s, %s, %s) ON DUPLICATE KEY UPDATE score = %s""", thedata)

and I get an error message like this:

Traceback (most recent call last):
  File "index.cgi", line 60, in <module>
    oldpage.processData(vcode_form)
  File "../vote/Range.py", line 128, in processData
    c.executemany("""INSERT INTO r1r (candidate, vid, score) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE score = %s""", thedata)
  File "/usr/site/lib/python2.5/site-packages/MySQLdb/cursors.py", line 212, in executemany
    self.errorhandler(self, TypeError, msg)
  File "/usr/site/lib/python2.5/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting

If I remove the "ON DUPLICATE KEY" clause, and take the last value out of "thedata", it works fine. Is something wrong with my SQL query? Any ideas what's wrong?

PS: Here's the table definition:

CREATE TABLE r1r (
        vid INT REFERENCES vcodes(id),
        cid INT REFERENCES candidates(id),
        score NUMERIC(2),
        PRIMARY KEY (vid, cid)
) Engine=InnoDB;

and if I substitute the values manually it works fine.

 

[ameoba]

Well... it looks like you're in luck : It looks like a bug in the library.

#!/usr/local/bin/python
import re
insert_values = re.compile(r"\svalues\s*(\(((?<!\\)'.*?\).*(?<!\\)?'|.)+?\))", re.IGNORECASE)
def execute(q,a):
    print "Execute " + q
    print "args" + a
    print "-------------------"

def executemany(query, args):
    m = insert_values.search(query)
    if not m:
        r = 0
        for a in args:
            r = r + execute(query, a)
        return r
    p = m.start(1)
    e = m.end(1)
    qv = m.group(1)
    print "Here's the bits we're trying to do string substitution in : " + qv
    q = [ qv % a for a in args ]
    r = '\n'.join([query[:p], ',\n'.join(q), query[e:]])
    return r

l = [(1L, 115L, 50, 50), (2L, 115L, 44, 44), (3L, 115L, 85, 85), (4L, 115L, 46, 46), (5L, 115L, 81, 81)]
q = """
    INSERT INTO r1r (candidate, vid, score)
    VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE score = %s
    """

print l
print q
executemany(q, l)

I've stripped down cursor.executemany() to be rid of most of the non-essential stuff and it still bombs out. It looks like there's a regular expression that's used to match the '%s'es in the VALUES block. It completely misses the %s in the UPDATE (and would likely bomb out if there's any where clauses). It looks like the bug's already been reported over at sourceforge. If you feel up to the task you might want to try fixing it yourself or just chiming in with a 'me too' (and a test case) on one of these bugs:

http://sourceforge.net/tracker/index.php?func=detail&aid=2137599&group_id=22307&atid=374932
http://sourceforge.net/tracker/index.php?func=detail&aid=1874176&group_id=22307&atid=374932
http://sourceforge.net/tracker/index.php?func=detail&aid=1686298&group_id=22307&atid=374932

They all come down to "broken regex". It just goes to show...

Some people, when confronted with a problem, think
“I know, I'll use regular expressions.” Now they have two problems.

(source)



TLDR; executemany() kinda sucks - loop and use execute instead.

 

[unhappy_mage]

Yep, execute() in a loop works. Thanks for the suggestion.

I don't know how I'd suggest fixing this, other than the really simple implementation:

def executemany(query, list):
    for item in list:
        execute(query, item)

since you have to consider a whole lot of cases for a "real" solution... and I'm not sure there's SQL syntax for some of them. For example, the MySQL manual doesn't seem to mention a way to do something like

INSERT INTO r1r (candidate, vid, score) VALUES
    (1,115,50) on duplicate key set score=50
    (2,115,60) on duplicate key set score=60;

which is essentially what I want.

Oh well, problem solved.

 

[ameoba]

From http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example:

In other words, you should be able to say :

INSERT INTO r1r (candidate, vid, score) VALUES
    (%s,%s,%s) ON DUPLICATE KEY SET score=VALUES(score)

...which would probably work with executemany().

 

[unhappy_mage]

Yeah, it looks like this syntax works:

mysql> INSERT INTO r1r (cid, vid, score) VALUES
    ->     (1,115,50),
    ->     (2,115,60) on duplicate key update score=VALUES(score);

Haven't got a chance to test it with executemany(), but I'll see when I get a chance to work on it again.

Thanks for all your help.