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

Answer 2

You are just calling the DB API in an incrorrect form, If you substitute your parameters like that, you would be responsible for escaping quotes and double-quotes in your data yourself.

That not only can give you tehe errors you are having (and lucky you for that), as that also allows dangerous attacks of SQL Injection.

Python's API to databases is desigened from the ground up to avoid the possibility of such attacks, and it does this symply by letting the call to cursor.execute do the string substitution for you. It will then add the necessary escapes to your string. So, instead of doing:

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)

Do

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

--Still, if you need all those crazy hard-coded numbers in your SOURCE file, and not in an auto-generated file, I dare say your project is doomed to fail anyway.

Answer 3

The first problem that I see with your code is this:

sql_input = "INSERT INTO table (column) VALUES('%s');" % ( data )

You should never do that -- you're using string interpolation, which doesn't protect you from SQLinjection or malformed sql.

Most python db apis use a placeholder syntax that is similar to this:

sql = "INSERT INTO table (column) VALUES( %s );"
values = (data,)
cur.execute(sql,values)

Note that you pass in statement and values separately. the API handles escaping and formatting.

some will allow for dicts as well:

sql = "INSERT INTO table (column) VALUES( %(id)s );"
values = { 'id': 1 )
cur.execute(sql,values)

Read up on how to properly use your database api - that is your biggest problem , and probably causing all your other issues.

Answer 4

One thing you might try is to use SQLAlchemy's SQL expression generation which will handle all the escaping and such for you, plus lets you avoid dealing with many security vulnerabilities (at least in terms of inserting into SQL databases and such). It parameterizes queries as opposed to doing inline string interpolation like you are trying to do.

Answer 5

I expect the problem is due to escaping the SQL command, or rather the lack of same.

Never, ever, ever do this;

cursor.execute("INSERT INTO whatever VALUES (%s)" % "foo")

Apart from the problems you're seeing, it's unsafe if you pass user input there (look up "Little Johnny Tables" if you don't know why).

Instead, do this:

cursor.execute("INSERT INTO whatever VALUES (%s)", ["foo"])

And let the MySql interface sort out the escaping.