Stylize Stylize - 1 year ago 97
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 '}
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())


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 Source
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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download