Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am trying to store the the following dictionary in to mysql DB by converting the dictionary in to string and then trying to insert but I am getting following error.How to solve this or is any other way to store dictinary 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 ??

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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()
share|improve this answer
    
Thanks It worked!!! –  Vb407 Oct 31 '13 at 12:12
    
How to deserialize data at the server other end ? –  Vb407 Nov 1 '13 at 7:23
1  
@VignanBandi: dic = json.loads(str_from_db) –  vartec Nov 1 '13 at 10:14

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.

share|improve this answer
    
by doing this im getting following exception (1054, "Unknown column '192.xx.xx.xxx' in 'field list'") –  Vb407 Oct 31 '13 at 10:08
    
Change d in the INSERT to dic –  Kobi K Oct 31 '13 at 10:12
    
But error is at this point "192.xxx.xx.xx" –  Vb407 Oct 31 '13 at 10:20
    
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. –  Kobi K Oct 31 '13 at 11:04
    
Thanks for the inputs MySQLdb.escape_string() solves the problem –  Vb407 Oct 31 '13 at 12:06

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 )   
share|improve this answer
    
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') –  Vb407 Oct 31 '13 at 11:24
    
OK, change d = str(dict) by d = json.dumps(dic). –  said omar Oct 31 '13 at 11:45
    
Thanks for the inputs MySQLdb.escape_string() solves the problem –  Vb407 Oct 31 '13 at 12:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.