Inserte la lista de Python (JSON o de otra manera) en la base de datos MySQL

Así que tengo un montón de datos de matriz en Python. Bueno, más bien, tengo una lista de listas. Estoy tratando de almacenar esta matriz en una sola celda en una base de datos MySQL. Intenté usar JSON para serializar mis datos, pero tal vez no entiendo cómo funciona JSON.

Entonces, después de conectarme a mi base de datos: (He probado los tipos de datos LONGTEXT y LONGBLOB para Upstream y 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)") 

Tomo mi lista de listas llamadas upstream_data y downstream_data y hago:

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

Luego ejecuto los comandos SQL:

 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) 

Haciendo referencia a una respuesta de Mordi (http://stackoverflow.com/questions/4251124/inserting-json-into-mysql-using-python), incluso intenté:

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

Pero independientemente de que termine con el 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") 

Además, cuando hago una

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

Veo que el objeto JSON aparece como una cadena larga con muchas comillas simples en todo el lugar (para que la lista, y en el exterior, represente una cadena). Cuando hice los json.dumps (json.dumps (upstream_data)), las citas internas se convierten en comillas dobles “” y están precedidas por \ caracteres. Aun así, me sale el mismo error.

¿Algunas ideas? Si no es así, ¿hay alguna mejor manera de almacenar los datos de la lista / matriz de Python en una sola celda MySQL?

salida aquí

Simplemente está llamando a la API de DB en forma incorrecta. Si sustituye sus parámetros de esa manera, sería responsable de eliminar las comillas y las comillas dobles de sus datos.

Eso no solo le puede dar los errores que está teniendo (y le da suerte por eso), ya que también permite ataques peligrosos de inyección SQL.

La API de Python para las bases de datos se diseñó desde cero para evitar la posibilidad de tales ataques, y lo hace de manera simple al permitir que la llamada a cursor.execute haga la sustitución de la cadena por usted. A continuación, agregará los escapes necesarios a su cadena. Entonces, en lugar de hacer:

 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) 

Hacer

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

– Aún así, si necesitas todos esos números locos codificados en tu archivo SOURCE, y no en un archivo generado automáticamente, me atrevo a decir que tu proyecto está condenado a fallar de todos modos.

Debe dejar que la biblioteca MySQL haga el manejo de los parámetros por usted; Esto tiene el beneficio adicional de permitir que MySQL prepare su statement, haciendo que las inserciones repetidas también sean más rápidas:

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

Consulte la especificación de la API de DB de Python para obtener (algunos) más detalles sobre el SQL parametrizado. Los formatos de parámetros admitidos exactos están documentados por adaptador de base de datos, así que verifique eso también. El módulo MySQLdb, por ejemplo, imita la syntax de formato de cadena de python, y usa %s como marcadores de posición:

 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" 

Otras opciones de parámetros posibles son números ( :1, :2 , etc.), nombres ( :foo, :bar ) o la otra forma de formato de cadena de python, especificadores de formato con nombre: ( %(foo)s, %(bar)s ).

El primer problema que veo con tu código es este:

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

Nunca debes hacer eso: estás utilizando la interpolación de cadenas, que no te protege de SQLinjection o sql con formato incorrecto.

La mayoría de las apis db de python usan una syntax de marcador de posición que es similar a esto:

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

Tenga en cuenta que usted pasa la statement y los valores por separado. La API maneja el escape y el formateo.

algunos permitirán también los dictados:

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

Lea cómo usar su api de base de datos correctamente. Ese es su mayor problema, y ​​probablemente esté causando todos sus otros problemas.

Una cosa que podría intentar es usar la generación de expresiones SQL de SQLAlchemy, que se encargará de todo el escape, y le permitirá evitar muchas vulnerabilidades de seguridad (al menos en términos de inserción en bases de datos SQL y demás). Parametriza las consultas en lugar de realizar la interpolación de cadenas en línea como lo está intentando hacer.

Espero que el problema se deba a escapar del comando SQL, o más bien a la falta del mismo.

Nunca, nunca, nunca hagas esto;

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

Aparte de los problemas que está viendo, no es seguro si pasa la entrada del usuario allí (busque “Little Johnny Tables” si no sabe por qué).

En su lugar, haga esto:

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

Y deje que la interfaz de MySql solucione el escape.