Python pymysql syntax error while inserting a JSON object

Question

I get the following error while inserting into a table of a SQL database used in Python:

pymysql.err.ProgrammingError: (1064, 'You have an error in your SQL 
syntax; check the manual that corresponds to your MariaDB server 
version for the right syntax to use near \'"Black": {"b": "125.98", 
"a": "126.796", "L": "117.245"}, "Pink": {"b": "130.286\' at line 1')

SQL command is:

json1 = json.dumps(meanLAB_vals_dict) # convert python dict to json string 
json2 = json.dumps(deltaE_dict)
sql_command = """INSERT INTO data_integrity_tool VALUES (%d, %d, %s, %s)""" %(i, image_id, json1, json2)

cursor.execute(sql_command)
connection.commit()

While meanLAB_vals_dict is:

{'Black': {'b': '125.98', 'a': '126.796', 'L': '117.245'}, 'Pink': 
{'b': '130.286', 'a': '180.918', 'L': '169.0'}, 'Green': {'b': 
'135.531', 'a': '103.51', 'L': '144.755'}, 'Violet': {'b': '109.878',
'a': '136.653', 'L': '122.02'}, 'Grey': {'b': '123.327', 'a': 
'125.612', 'L': '139.429'}, 'Yellow': {'b': '195.571', 'a': 
'112.612', 'L': '234.694'}, 'Red': {'b': '153.449', 'a': '177.918',
'L': '163.939'}, 'White': {'b': '128.02', 'a': '128.939', 'L': 
'243.878'}, 'Blue': {'b': '84.7551', 'a': '122.98', 'L': '163.673'}}

and deltaE_dict is:

{'Black': '38.5187', 'Pink': '38.6975', 'mean delta E': '28.0643', 
'Green': '42.6365', 'Violet': '35.5018', 'Grey': '19.8903', 'Yellow':
'24.5115', 'Red': '40.0078', 'White': '4.4993', 'Blue': '8.31544'}

While i and image_id are two integers (indices of the iterations). Following is the data_integrity_tool table:

sql_command = """CREATE TABLE IF NOT EXISTS data_integrity_tool (
                 id_              INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  
                 image_id         INTEGER NOT NULL,
                 mean_lab_values  TEXT,
                 delta_e_values   TEXT);"""

I know some similar questions exist already, however, they are for PHP and I have no idea about it and moreover, I am totally new in SQL.

field names/columns are optional. However, I can give try to that. And, result is the same error. Also, with blob. Error remains. — Sanchit, Apr 04 '17 at 08:44
You are passing string values in integer columns `id_` and `image_id`. Also `id_` is autoincrement. — Satish Prakash Garg, Apr 04 '17 at 08:53
@SatishGarg: I did those changes. However, error remains. It seems like there is something in the json objects. However, when I dumped python dictionaries into json objects, I did not get any error. — Sanchit, Apr 04 '17 at 08:56
Yes, query is already in the beginning part of the question: sql_command = """INSERT INTO data_integrity_tool(id_, image_id, mean_lab_values, delta_e_values) VALUES (%d, %d, %s, %s)""" %(i, image_id, json1, json2) — Sanchit, Apr 04 '17 at 08:58
@SatishGarg: I have found a solution which I have answered. Check it out. — Sanchit, Apr 04 '17 at 12:00
Main problem is in the SQL injection. Always provide values in during the execution function not in the insert query itself. Execution function prevents it. And, also always use "%s" for all the fields (doesnt matter what type they are from). Check my answer and it has some interesting links. — Sanchit, Apr 04 '17 at 12:07

score 2 · Answer 1 · answered Apr 04 '17 at 08:51

It seems that you haven't specified the field names upon which data is to be inserted in sql query. Also use blob as data type if you want to insert json data. That is much safer.Also don't cast the no in insert query.

If you consider my answer then your sql statement should be something like this. Table creation command:

sql_command = """CREATE TABLE IF NOT EXISTS data_integrity_tool ( id_ INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, image_id INTEGER NOT NULL, mean_lab_values BLOB, delta_e_values BLOB);"""

Insertion command:

sql_command = """INSERT INTO data_integrity_tool(id_,image_id,mean_lab_values,delta_e_values) VALUES (%d, %d, %s, %s)""" %(i, image_id, json1, json2)

Always use "%s" for all the fields. Check these links: - http://stackoverflow.com/questions/5785154/python-mysqldb-issues-typeerror-d-format-a-number-is-required-not-str - http://mysql-python.sourceforge.net/MySQLdb.html#some-examples — Sanchit, Apr 04 '17 at 11:59

score 0 · Accepted Answer · edited May 23 '17 at 12:10

Here is the answer (first, there is no problem in the JSON objects):

Create table:

sql_command = """CREATE TABLE IF NOT EXISTS data_integrity_tool (
             id_              INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  
             image_id         INTEGER NOT NULL,
             mean_lab_values  TEXT COLLATE utf8_unicode_ci,
             delta_e_values   TEXT COLLATE utf8_unicode_ci);"""

SQL insert query:

json1 = json.dumps(meanLAB_vals_dict)
json2 = json.dumps(deltaE_dict)

sql_command = """INSERT INTO data_integrity_tool(id_, image_id,  
mean_lab_values, delta_e_values) VALUES (%s, %s, %s, %s)""" 

cursor.execute(sql_command, (i, image_id, json1, json2))

NOTE:

In the create table, I added COLLATE to utf8_unicode_ci. If you did not mention, default is latin1_swedish_ci which I don't need.
In the SQL insert query, sql_command doesn't contain the values, rather they are provided during the execution function. This avoids SQL Injection.
Also, in the SQL insert query, always use %s for all the fields.

Interesting links:

Python pymysql syntax error while inserting a JSON object

2 Answers2