python+mysql库+json,将Python清单（JSON或其他方式）插入MySQL资料库

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

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

解决方案

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.