Use a python dictionary to insert into mysql
10,196
Solution 1
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)
.
Solution 2
The previous answer doesn't work for non string dictionary value. This one is a revised version.
format_string = ','.join(['%s'] * len(dict))
self.db.set("""INSERT IGNORE INTO listings ({0}) VALUES ({1})""".format(", ".join(dict.keys()),format_string),
(dict.values()))
Author by
Stylize
Updated on June 04, 2022Comments
-
Stylize almost 2 years
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.