How to store python dictionary in to mysql DB through python

Question

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?

vartec · Accepted Answer · 2013-10-31 11:50:49Z

29

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

answered Oct 31, 2013 at 11:50

vartec

135k38 gold badges227 silver badges248 bronze badges

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

2 Comments

Vb407 Over a year ago

How to deserialize data at the server other end ?

vartec Over a year ago

@VignanBandi: dic = json.loads(str_from_db)

Ali SAID OMAR · Accepted Answer · 2013-10-31 10:44:02Z

1

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 )

answered Oct 31, 2013 at 10:44

Ali SAID OMAR

6,8629 gold badges42 silver badges57 bronze badges

3 Comments

Vb407 Over a year ago

It still throwing the same error (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 \'192.xxx.x.xxx\'\', \'"{\'component_office\': [\'Word2010SP0\']}"\' at line 1')

Ali SAID OMAR Over a year ago

OK, change d = str(dict) by d = json.dumps(dic).

Vb407 Over a year ago

Thanks for the inputs MySQLdb.escape_string() solves the problem

Kobi K · Accepted Answer · 2013-10-31 10:12:26Z

0

Try this:

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 values you need.

edited Oct 31, 2013 at 10:12

answered Oct 31, 2013 at 9:53

Kobi K

7,9376 gold badges47 silver badges88 bronze badges

5 Comments

Vb407 Over a year ago

by doing this im getting following exception (1054, "Unknown column '192.xx.xx.xxx' in 'field list'")

Kobi K Over a year ago

Change d in the INSERT to dic

Kobi K Over a year ago

I tried the script and it works ok can you print your code and traceback? it's hard to understand where is the error, one more option try only the SQL statement at mysql work-branch.

Vb407 Over a year ago

Thanks for the inputs MySQLdb.escape_string() solves the problem

mbunch Over a year ago

Incase anyone is reading this in not 2013: The error is due to the back ticks around the curly braces. They should be single quotes.

mbunch · Accepted Answer · 2018-10-12 16:45:17Z

0

It is a good idea to sanitize your inputs, and '.format' is useful when needing to use the same variable multiple times within a query. (Not that you to for this example)

dic = {'office': {'component_office': ['Word2010SP0', 'PowerPoint2010SP0']}}
ip = '192.xxx.xx.xx'
with conn.cursor() as cur:
  cur.execute("INSERT INTO `ep_soft`(`ip_address`, `soft_data`) VALUES ({0}, '{1}')".format(cur.escape(ip),json.dumps(event)))
  conn.commit()

If you do not use cur.escape(variable), you will need to enclose the placeholder {} in quotes.

answered Oct 12, 2018 at 16:45

mbunch

5691 gold badge7 silver badges21 bronze badges

Comments

John Drinane · Accepted Answer · 2020-03-25 16:02:21Z

This answer has some pseudo code regarding the connection object and the flavor of mysql is memsql, but other than that it should be straightforward to follow.

import json
#... do something
a_big_dict = getAHugeDict()  #build a huge python dict
conn = getMeAConnection(...)
serialized_dict = json.dumps(a_big_dict) #serialize dict to string
#Something like this to hold the serialization...
qry_create = """
CREATE TABLE TABLE_OF_BIG_DICTS (
ROWID BIGINT NOT NULL AUTO_INCREMENT,
SERIALIZED_DICT BLOB NOT NULL,
UPLOAD_DT TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
KEY (`ROWID`) USING CLUSTERED COLUMNSTORE
);
"""
conn.execute(qry_create)
#Something like this to hold em'
qry_insert = """
INSERT INTO TABLE_OF_BIG_DICTS (SERIALIZED_DICT)
SELECT '{SERIALIZED_DICT}' as SERIALIZED_DICT;
"""
#Send it to db
conn.execute(qry_insert.format(SERIALIZED_DICT=serialized_dict))
#grab the latest
qry_read = """
SELECT a.SERIALIZED_DICT
from TABLE_OF_BIG_DICTS a
JOIN 
(
    SELECT MAX(UPLOAD_DT) AS MAX_UPLOAD_DT
    FROM TABLE_OF_BIG_DICTS
)                           b
ON  a.UPLOAD_DT = b.MAX_UPLOAD_DT
LIMIT 1
"""

#something like this to read the latest dict...
df_dict = conn.sql_to_dataframe(qry_read)
dict_str = df_dict.iloc[df_dict.index.min()][0]

#dicts never die they just get rebuilt
dict_better = json.loads(dict_str)

Collectives™ on Stack Overflow

How to store python dictionary in to mysql DB through python

5 Answers 5

2 Comments

3 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

3 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related