How to store python dictionary in mysql DB via python

Question

I am trying to save the next dictionary in mysql DB by converting the dictionary to a string and then trying to insert, but I get the following error. How can this be solved, or is there another way to store the dictionary in mysql DB?

dic = {'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0']}} d = str(dic) # Sql query sql = "INSERT INTO ep_soft(ip_address, soft_data) VALUES ('%s', '%s')" % ("192.xxx.xx.xx", d ) soft_data is a VARCHAR(500) 

Error: execution exception (1064, "You have an error in the SQL syntax, check the manual that matches the version of your MySQL server, for the correct syntax use" office ": {'component_office': ['Word2010SP0', 'PowerPoint2010SP0' on line 1" )

Any suggestions or help please?

Answer 1

First of all, never create raw SQL queries like this. Never. This requires parameterized queries. You are requesting an SQL injection attack.

If you want to store arbitrary data, such as Python dictionaries, you must serialize this data. JSON would be a good choice for the format.

In general, your code should look like this:

 import MySQLdb import json db = MySQLdb.connect(...) cursor = db.cursor() dic = {'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0']}} sql = "INSERT INTO ep_soft(ip_address, soft_data) VALUES (%s, %s)" cursor.execute(sql, ("192.xxx.xx.xx", json.dumps(dic))) cursor.commit() 

Answer 2

Try the following:

 dic = { 'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0'] } } "INSERT INTO `db`.`table`(`ip_address`, `soft_data`) VALUES (`{}`, `{}`)".format("192.xxx.xx.xx", str(dic)) 

Change db and table to the desired values.

Answer 3

Change your code as below:

 dic = {'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0']}} d = str(dic) # Sql query sql = """INSERT INTO ep_soft(ip_address, soft_data) VALUES (%r, %r)""" % ("192.xxx.xx.xx", d )