Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to store python dictionary in to mysql DB through python

I am trying to store the the following dictionary into mysql DB by converting the dictionary into a string and then trying to insert, but I am getting following error. How can this be solved, or is there any other way to store a dictionary into 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0' at line 1")

Any suggestions or help please?

like image 382
Vb407 Avatar asked Oct 31 '13 09:10

Vb407


People also ask

Can you store a dictionary in MySQL?

The data dictionary schema stores dictionary data in transactional ( InnoDB ) tables. Data dictionary tables are located in the mysql database together with non-data dictionary system tables. Data dictionary tables are created in a single InnoDB tablespace named mysql. ibd , which resides in the MySQL data directory.

Can you store a dictionary in a file Python?

The pickle module may be used to save dictionaries (or other objects) to a file. The module can serialize and deserialize Python objects. In Python, pickle is a built-in module that implements object serialization.

Can I connect Python to MySQL database?

Python needs a MySQL driver to access the MySQL database. In this tutorial we will use the driver "MySQL Connector". We recommend that you use PIP to install "MySQL Connector". PIP is most likely already installed in your Python environment.


2 Answers

First of all, don't ever construct raw SQL queries like that. Never ever. This is what parametrized queries are for. You've asking for an SQL injection attack.

If you want to store arbitrary data, as for example Python dictionaries, you should serialize that data. JSON would be good choice for the format.

Overall 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()
like image 139
vartec Avatar answered Oct 17 '22 03:10

vartec


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 )   
like image 1
Ali SAID OMAR Avatar answered Oct 17 '22 03:10

Ali SAID OMAR