Stylize Stylize - 3 months ago 11
MySQL Question

Use a python dictionary to insert into mysql

I am trying to take the data from a dictionary (the example is simplified for readability) and insert it into a mysql database.
I have the following piece of code.

import pymysql
conn = pymysql.connect(server, user , password, "db")
cur = conn.cursor()
ORFs={'E7': '562', 'E6': '83', 'E1': '865', 'E2': '2756 '}
table="genome"
cols = ORFs.keys()
vals = ORFs.values()
sql = "INSERT INTO %s (%s) VALUES(%s)" % (
table, ",".join(cols), ",".join(vals))

print sql
print ORFs.values()
cur.execute(sql, ORFs.values())


cur.close()
conn.close()


the
print sql
statement returns

INSERT INTO genome (E7,E6,E1,E2) VALUES(562,83,865,2756 )


when I type this directly into the mysql command line, the mysql command works. But when I run the python script I get an error:

<type 'exceptions.TypeError'>: not all arguments converted during string formatting
args = ('not all arguments converted during string formatting',)
message = 'not all arguments converted during string formatting'


As always, any suggestions would be highly appreciated.

Answer
sql = "INSERT INTO %s (%s) VALUES(%s)" % (
    table, ",".join(cols), ",".join(vals))

This SQL includes values and cur.execute(sql, ORFs.values()) has values, too.

So, it should be cur.execute(sql).