How do I insert data from a Python dictionary to MySQL?
I manipulated some data f开发者_C百科rom MySQL and the resulting dictionary "data" (print data) displays something like this :
{'1': ['1', 'K', abc, 'xyz', None, None, None, datetime.date(2009, 6, 18)],
'2': ['2', 'K', efg, 'xyz', None, None, None, None],
'3': ['3', 'K', ijk, 'xyz', None, None, None, datetime.date(2010, 2, 5, 16, 31, 2)]}
How do I create a table and insert these values in a MySQL table? In other words, how do I dump them to MySQL or CSV? Not sure how to deal with datetime.date and None values. Any help is appreciated.
Here is some basic code to create a MySQL database, and insert some data.
import MySQLdb
import datetime
THEHOST="localhost"
THEUSER="user"
THEPASSWD="passwd"
THEDB="database"
connection=MySQLdb.connect(
host=THEHOST,user=THEUSER,passwd=THEPASSWD,db=THEDB)
cursor=connection.cursor()
abc,efg,ijk=1,2,3
data={'1': ['1', 'K', abc, 'xyz', None, None, None, datetime.date(2009, 6, 18)],
'2': ['2', 'K', efg, 'xyz', None, None, None, None],
'3': ['3', 'K', ijk, 'xyz', None, None, None,
datetime.datetime(2010, 2, 5, 16, 31, 2)]}
sql='''\
CREATE TABLE IF NOT EXISTS temp (id int auto_increment primary key,
field1 varchar(8),
field2 int,
field3 varchar(8),
field4 bool,
field5 varchar(8),
field6 varchar(8),
field7 datetime )'''
cursor.execute(sql)
sql='''\
INSERT INTO temp (id, field1, field2, field3, field4, field5, field6, field7)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
'''
cursor.executemany(sql, data.values())
datetime.date
matches DATE
fields, and None
becomes NULL
. Use .executemany()
in conjunction with dict.values()
in order to perform the INSERT.
Most dbapi-compliant connectors to MySQL will automatically convert Python's None
to SQL's NULL
and Python's datetime
objects to SQL TIMESTAMP
s.
Really, you just need to open a connection to your database, get a cursor, and iterate over the dictionary doing the insert. Without knowing your schema, it's impossible to give you sample code, though.
精彩评论