I have a dict object. I dumped the data using this:
for alldata in data: # print all data to screen
print data[alldata]
Each field had brackets [] and 'None' values for NULLS and date.datetime for date values.
How do I dump this dict to MySQL table? Thank you!
print data displays something like this :
{'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?
Assuming you have MySQLdb (mysql-python) installed:
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()
this one is giving a very nice example and more compatible one. http://code.activestate.com/recipes/457661-generate-sql-for-insertation-into-table-from-dicti/
This is a possible duplicate of, or can be answered by this post Using a Python dict for a SQL INSERT statement
However, to answer your question, I posted this over on the other question and i'm reposting it here as well:
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 a dictionary
tab = {'idnumber': 1, 'fname': 'some', 'lname': 'dude', 'dob': '15/08/1947', 'mobile': 5550000914, 'age' : 70.4}
we get the output as the screenshot below shows. Note this is a bare-bones example and needs sanity checks, and can be modified for bulk updates (using a dict of dicts), etc.
