gatorback gatorback - 1 month ago 7
MySQL Question

Python mysql.connector: adding record with timestamp

BACKGROUND



This command successfully added 5 fields (all strings) to a table's record:

import mysql.connector
...
add_scan = ("INSERT INTO tblScanpoint "
"(f1, f2, f3, f4, f5 ) "
"VALUES (%s, %s, %s, %s, %s)")
data_scan = ('AAAabc', 'AA', '4321', 'SEA', '3')
cursor = cnx.cursor()
cursor.execute(add_scan, data_scan)
cnx.commit()
...


The sixth field's datatype is TIMESTAMP.

ERROR MESSAGE



Error from attempting to update all six fields:

>>> cursor.execute(add_scan, data_scan)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 551, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 490, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 395, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'when ) VALUES ('CCCabc', 'AA', '4321', 'SEA', '3', '2016-11-07 20:46:56.35')' at line 1


TIMESTAMP UPLOAD DETAILS



These strings were modified to accomodate sixth TIMESTAMP field:

stamp = '2016-11-07 20:46:56.35'
add_scan = ("INSERT INTO scanpoint "
"(f1, f2, f3, f4, f5, when ) "
"VALUES (%s, %s, %s, %s, %s, %s)")
data_scan = ('CCCabc', 'AA', '4321', 'SEA', '3', stamp)


Is the error thrown because it is fed a string? Seeking corrective-action suggestions. Clever examples are appreciated.

Answer

That's the issue when your column name which is a keyword/reserve-word. You need to escape it using backtique. See below

(f1, f2, f3, f4, f5, when ) 
                      ^.... problematic column name

This should be escaped

(f1, f2, f3, f4, f5, `when` )