I'm trying to execute an insert query. It works when I directly copy and paste it to the mysql command prompt, but fails when I execute it from Python. I'm getting this error with MySQLdb (also tried using _mysql directly and get the same error).
我正在尝试执行插入查询。它直接复制并粘贴到mysql命令提示符时有效,但是当我从Python执行它时失败。我在MySQLdb中遇到此错误(也尝试直接使用_mysql并获得相同的错误)。
The error is the same as this question, but the answer does not work for my problem (my query is on a single line): MySQL the right syntax to use near '' at line 1 error
错误与此问题相同,但答案对我的问题不起作用(我的查询在一行上):MySQL在''第1行错误附近使用正确的语法
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)
Result (it prints the query, which when copied directly into MySQL command prompt executes fine, and then crashes):
结果(它打印查询,当直接复制到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")
I tried turning on profiling because I can't seem be able to access _last_executed from my cursor (something others have suggested). It seems that my cursor does not have this property (was it removed?).
我尝试打开分析,因为我似乎无法从我的光标访问_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()
This shows me an incomplete query:
这显示了一个不完整的查询:
INSERT INTO D02CA10B13E5$accelerometer_X(periodic) VALUES (80.000000) ON DUPLICATE KEY UPDATE periodic=80.000000
(1L, 5.925e-05, 'INSERT INTO D02CA10B13E5')
Some suggest splitting the query into multiple lines (which contradicts the suggestion in the link I posted above). Anyway, it narrows the search to one line (apparently the Python module doesn't like either my table name or column name which are on the third line)
有人建议将查询拆分成多行(这与我上面发布的链接中的建议相矛盾)。无论如何,它将搜索范围缩小到一行(显然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)
Result:
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")
Again, this query executes fine on MySQL, so I'm not sure what's going on here in Python. Any pointers are appreciated. Thanks!
同样,这个查询在MySQL上执行得很好,所以我不确定Python中发生了什么。任何指针都表示赞赏。谢谢!
1 个解决方案
#1
2
Ok so '' in the MySQL error apparently referred to a \0 character that got appended to my table name. This was not visible as you can see for yourself:
好的,所以''在MySQL错误中显然是指一个附加到我的表名的\ 0字符。这是不可见的,因为你可以看到自己:
>>> print chr(0)
>>> print "hello" + chr(0)
hello
The \0 character is completely invisible and doesn't get copied onto the clipboard from the terminal (so it worked when pasted onto the MySQL console). Sneaky!
\ 0字符是完全不可见的,不会从终端复制到剪贴板上(因此粘贴到MySQL控制台时它可以工作)。偷偷摸摸的!
I found this out by comparing string lengths with expected string lengths and finding a difference of 1 character that was invisible to my eyes. The \0 character came about in my Python code when I read the string (sensor ID) from a socket (the application on the other end was a C program that was appending these \0 characters).
我通过将字符串长度与预期的字符串长度进行比较并找出1个字符的差异来发现这一点,这个字符对我来说是不可见的。当我从套接字读取字符串(传感器ID)时,我的Python代码中出现了\ 0字符(另一端的应用程序是附加这些\ 0字符的C程序)。
Hope my answer saves someone else a lot of trouble!
希望我的回答能给别人带来很多麻烦!
#1
2
Ok so '' in the MySQL error apparently referred to a \0 character that got appended to my table name. This was not visible as you can see for yourself:
好的,所以''在MySQL错误中显然是指一个附加到我的表名的\ 0字符。这是不可见的,因为你可以看到自己:
>>> print chr(0)
>>> print "hello" + chr(0)
hello
The \0 character is completely invisible and doesn't get copied onto the clipboard from the terminal (so it worked when pasted onto the MySQL console). Sneaky!
\ 0字符是完全不可见的,不会从终端复制到剪贴板上(因此粘贴到MySQL控制台时它可以工作)。偷偷摸摸的!
I found this out by comparing string lengths with expected string lengths and finding a difference of 1 character that was invisible to my eyes. The \0 character came about in my Python code when I read the string (sensor ID) from a socket (the application on the other end was a C program that was appending these \0 characters).
我通过将字符串长度与预期的字符串长度进行比较并找出1个字符的差异来发现这一点,这个字符对我来说是不可见的。当我从套接字读取字符串(传感器ID)时,我的Python代码中出现了\ 0字符(另一端的应用程序是附加这些\ 0字符的C程序)。
Hope my answer saves someone else a lot of trouble!
希望我的回答能给别人带来很多麻烦!