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.

Are you missing one None? (use a class...)

kennytm
– kennytm

2010-04-12 16:05:30 +00:00
Commented Apr 12, 2010 at 16:05 — kennytm
– kennytm, Commented Apr 12, 2010 at 16:05
Yep, updated! THanks. This is just sample data. Thanks Ken.

ThinkCode
– ThinkCode

2010-04-12 16:36:44 +00:00
Commented Apr 12, 2010 at 16:36 — ThinkCode
– ThinkCode, Commented Apr 12, 2010 at 16:36

unutbu · Accepted Answer · 2010-04-12 16:18:14Z

1

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

answered Apr 12, 2010 at 16:18

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ThinkCode Over a year ago

Thanks for the solution :) I keep getting this error though : TypeError: not all arguments converted during string formatting. I used this : sql='''\ INSERT INTO delUseThisTable (key, primary) VALUES (%s, %s) ''' cursor.executemany(sql, data.values()) I have 78 columns in this table. I tried with 2 columns for a start and it fails. Ideas? Thank u!

unutbu Over a year ago

@NJTechie: Perhaps try print data.values(). It should be a list of 2-element lists, such as [[1,'K'],[2,'L']]. The inner lists must have exactly 2 elements. Otherwise, you may get the error TypeError: not all arguments converted...

ThinkCode Over a year ago

OMG, sir you are a genius :) I didn't know we have to insert all of them at once rather than just 2 columns! Definitely learnt a thing or 2! Thank you sooo much!

Ignacio Vazquez-Abrams · Accepted Answer · 2010-04-12 16:06:29Z

1

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

answered Apr 12, 2010 at 16:06

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Comments

jemfinch · Accepted Answer · 2010-04-12 16:06:55Z

1

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.

answered Apr 12, 2010 at 16:06

jemfinch

2,91920 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related