python - "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"

Question

我正在尝试执行插入查询。 当我直接将它复制并粘贴到 mysql 命令提示符时它有效，但当我从 Python 执行它时失败。我在使用 MySQLdb 时遇到此错误(也尝试直接使用 _mysql 并得到相同的错误).

错误与此问题相同，但答案不适用于我的问题(我的查询在一行上):MySQL the right syntax to use near '' at line 1 error

query = """INSERT INTO %s(%s) VALUES (%f) ON DUPLICATE KEY UPDATE %s=%f"""%(table_name,measurement_type,value,measurement_type,value)
print query 
cur.execute(query)

结果(打印查询，当直接复制到 MySQL 命令提示符时执行正常，然后崩溃):

INSERT INTO D02CA10B13E5$accelerometer_X(periodic) VALUES (79.000000) ON DUPLICATE KEY UPDATE periodic=79.000000
Traceback (most recent call last):
File "collect_data.py", line 145, in <module>
process_data_array(data_bytes[start:start+sensor_packet_size])
File "collect_data.py", line 105, in process_data_array
record_data(MAC,sensor_name,"X",code_name,X,cur)
File "collect_data.py", line 58, in record_data
cur.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.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")

我尝试打开分析，因为我似乎无法从我的光标访问 _last_executed(其他人建议的)。我的游标好像没有这个属性(是不是去掉了？)。

query = """INSERT INTO %s(%s) VALUES (%f) ON DUPLICATE KEY UPDATE %s=%f"""%(table_name,measurement_type,value,measurement_type,value)
print query 
#cur.execute(query)
try:
    cur.execute(query)
except:
    cur.execute('show profiles')
    for row in cur:
        print row
    cur.execute("set profiling = 0")
    exit()

这显示了一个不完整的查询:

INSERT INTO D02CA10B13E5$accelerometer_X(periodic) VALUES (80.000000) ON DUPLICATE KEY UPDATE periodic=80.000000
(1L, 5.925e-05, 'INSERT INTO D02CA10B13E5')

有些人建议将查询分成多行(这与我在上面发布的链接中的建议相矛盾)。无论如何，它将搜索范围缩小到一行(显然 Python 模块不喜欢我在第三行的表名或列名)

query = "INSERT \nINTO \n%s(%s) \nVALUES \n(%f) \nON \nDUPLICATE \nKEY \nUPDATE %s=%f"%(table_name,measurement_type,value,measurement_type,value)
print query 
cur.execute(query)

结果:

INSERT 
INTO 
D02CA10B13E5$accelerometer_X(periodic) 
VALUES 
(80.000000) 
ON 
DUPLICATE 
KEY 
UPDATE periodic=80.000000
Traceback (most recent call last):
File "collect_data.py", line 145, in <module>
process_data_array(data_bytes[start:start+sensor_packet_size])
File "collect_data.py", line 105, in process_data_array
record_data(MAC,sensor_name,"X",code_name,X,cur)
File "collect_data.py", line 58, in record_data
cur.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.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 3")

同样，这个查询在 MySQL 上执行得很好，所以我不确定在 Python 中发生了什么。任何指针表示赞赏。谢谢!

Answer 1

好的，MySQL 错误中的 '' 显然指的是附加到我的表名的\0 字符。正如您自己所见，这是不可见的:

>>> print chr(0)

>>> print "hello" + chr(0)
hello

\0 字符是完全不可见的，不会从终端复制到剪贴板上(因此在粘贴到 MySQL 控制台时它起作用了)。偷偷摸摸的!

我通过将字符串长度与预期字符串长度进行比较并发现我的眼睛看不到 1 个字符的差异来发现这一点。当我从套接字读取字符串(传感器 ID)时，\0 字符出现在我的 Python 代码中(另一端的应用程序是附加这些\0 字符的 C 程序)。

希望我的回答能为其他人省去很多麻烦!