Search code examples
pythonmysqljsonpymysql

Python pymysql syntax error while inserting a JSON object

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.

Solution

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

    1. 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);"""

    2. 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: