Fastest way to dump Python object (dict) into MySQL table?

Question

I have a dict object. I dumped data using this:

for alldata in data: # print all data to screen print data[alldata] 

Each field has brackets [] and "None" for NULLS and date.datetime for date values.

How do I reset this dict table in MySQL? Thanks!

print data displays something like the following:

 {'1': ['1', 'K', abc, 'xyz', None, None, None], '2': ['2', 'K', efg, 'xyz', None, None, None], '3': ['3', 'K', ijk, 'xyz', None, None, None]} 

How to insert this data into MySQL?

Answer 1

Assuming you installed MySQLdb (mysql-python):

 sql = "INSERT INTO mytable (a,b,c) VALUES (%(qwe)s, %(asd)s, %(zxc)s);" data = {'qwe':1, 'asd':2, 'zxc':None} conn = MySQLdb.connect(**params) cursor = conn.cursor() cursor.execute(sql, data) cursor.close() conn.close() 

Answer 2

this example gives a very good example and more compatible. http://code.activestate.com/recipes/457661-generate-sql-for-insertation-into-table-from-dicti/

Answer 3

Is this a possible duplicate or response to this post Using a Python Statement for an SQL INSERT Statement

However, to answer your question, I posted this on another question, and I am also retelling it here:

 def ins_query_maker(tablename, rowdict): keys = tuple(rowdict) dictsize = len(rowdict) sql = '' for i in range(dictsize) : if(type(rowdict[keys[i]]).__name__ == 'str'): sql += '\'' + str(rowdict[keys[i]]) + '\'' else: sql += str(rowdict[keys[i]]) if(i< dictsize-1): sql += ', ' query = "insert into " + str(tablename) + " " + str(keys) + " values (" + sql + ")" print(query) # for demo purposes we do this return(query) #in real code we do this 

For dictionary

 tab = {'idnumber': 1, 'fname': 'some', 'lname': 'dude', 'dob': '15/08/1947', 'mobile': 5550000914, 'age' : 70.4} 

we get the result as shown in the screenshot below. Please note that this is an example of bare-bones and needs performance checks, and can also be changed for bulk updates (using dict of dicts), etc.