How do I insert data from a Python dictionary to MySQL?

Question

I manipulated some data from 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.

Answer 1

datetime.date matches DATE fields, and None becomes NULL. Use .executemany() in conjunction with dict.values() in order to perform the INSERT.

Answer 2

Most dbapi-compliant connectors to MySQL will automatically convert Python's None to SQL's NULL and Python's datetime objects to SQL TIMESTAMPs.

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.

Answer 3

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())