Insert Python List (JSON or otherwise) into MySQL databse

Question

So I have a bunch of array data in Python. Well, rather, I have a list of lists. I'm trying to store this array into a single cell in a MySQL database. I've attempted to use JSON to serialize my data, but perhaps I do not understand how JSON works.

So after connecting to my database: (I've tried LONGTEXT and LONGBLOB datatypes for Upstream and Downstream

cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS 963168MBV17A(Id INT AUTO_INCREMENT PRIMARY KEY, Rev INT, Part VARCHAR(15), SN INT(7), Date DATE, Time TIME, Iterations INT(3), Upstream LONGBLOB, Downstream LONGBLOB, ResultList LONGTEXT, Result CHAR(1), Report LONGBLOB)")

I take my list of lists called upstream_data and downstream_data and do:

export_upstream = json.dumps(upstream_data)
export_downstream = json.dumps(downstream_data)

Then I execute the SQL commands:

cur = con.cursor()    
sql_input = "INSERT INTO 963168MBV17A(Rev, Part, SN, Iterations, Date, Time, Upstream, Downstream, ResultList, Result, Report) VALUES('503', '100-120970-0031', '1594539', '%s', '%s', '%s', '%s', '%s', 0, P, 0" %(export_date, export_time, export_numtests, export_upstream, export_downstream)
cur.execute(sql_input)

referencing an answer by Mordi (http://stackoverflow.com/questions/4251124/inserting-json-into-mysql-using-python), I even tried:

export_upstream = json.dumps(json.dumps(upstream_data))
export_downstream = json.dumps(json.dumps(downstream_data))

But regardless I end up with the error:

Traceback (most recent call last):
  File "P:\Projects\testing database\scrap\test.py", line 83, in <module>
    cur.execute(sql_input)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (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 '' at line 1")

Moreover, when I do a

print "about to execute(%s)" % sql_input

I see that the JSON object is appearing as a long string with lots of single quotes all over the place (for the list, and on the outside to represent a string). When I did the json.dumps(json.dumps(upstream_data)), the inside quotations become double quotes "" and preceded by \ characters. Still, though, I get the same error.

Any ideas? If not, any better way to store Python array/list data into a single MySQL cell?

output here

Answer 1

You need to let the MySQL library do the parameter handling for you; this has the added benefit of letting MySQL prepare your statement, making repeated inserts faster too:

cur = con.cursor()    
sql_input = "INSERT INTO 963168MBV17A(Rev, Part, SN, Iterations, Date, Time, Upstream, Downstream, ResultList, Result, Report) VALUES('503', '100-120970-0031', '1594539', ?, ?, ?, ?, ?, 0, P, 0"
cur.execute(sql_input, (export_date, export_time, export_numtests, export_upstream, export_downstream))

See the Python DB API 2.0 spec for (some) more details on parametrized SQL. The exact supported parameter formats are documented per database adapter, so check that too. The MySQLdb module, for example, mimics the python string formatting syntax, and uses %s as placeholders:

sql_input = "INSERT INTO 963168MBV17A(Rev, Part, SN, Iterations, Date, Time, Upstream, Downstream, ResultList, Result, Report) VALUES('503', '100-120970-0031', '1594539', %s, %s, %s, %s, %s, 0, P, 0"

Other possible parameter options are numbers (:1, :2, etc.), names (:foo, :bar) or the other form of python string formatting, named format specifiers: (%(foo)s, %(bar)s).