试图将SQL插入从python上的MySQLdb更改为pymysql

Question

我为MySQLdb编写了一个具有这种结构的代码，它运行得很好。

sql = """INSERT INTO `x`.`x` (`id`, `user`, `topic`, `message`, `data`) VALUES (null, '""" + lista[1]+ """', '""" + lista[2] + """', '""" + str(msg.payload) + """', CURRENT_TIMESTAMP);"""

然而，现在我需要为Python3编写它，它与MySQLdb不兼容，并且阅读pymysql的文档，我认为我不能这样做，所以我尝试了这样的东西，但不起作用：

sql = """INSERT INTO `x`.`x` (`id`, `user`, `topic`, `message`, `data`) VALUES (null,%s, %s, %s, CURRENT_TIMESTAMP);"""
cursor.execute(sql, lista[1], lista[2],str(msg.payload))

当我评论尝试时的结果

> Traceback (most recent call last):
> 
>   File "/home/pi/Downloads/mqttpi1.py", line 65, in <module>
> 
>     client.loop_forever()
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 1779, in loop_forever
> 
>     rc = self.loop(timeout, max_packets)
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 1181, in loop
> 
>     rc = self.loop_read(max_packets)
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 1572, in loop_read
> 
>     rc = self._packet_read()
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 2310, in _packet_read
> 
>     rc = self._packet_handle()
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 2936, in _packet_handle
> 
>     return self._handle_publish()
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 3216, in _handle_publish
> 
>     self._handle_on_message(message)
> 
>   File
> "/home/pi/.local/lib/python3.7/site-packages/paho/mqtt/client.py",
> line 3444, in _handle_on_message
> 
>     self.on_message(self, self._userdata, message)
> 
>   File "/home/pi/Downloads/mqttpi1.py", line 43, in on_message
> 
>     cursor.execute(sql)
> 
>   File "/usr/local/lib/python3.7/dist-packages/pymysql/cursors.py",
> line 148, in execute
> 
>     result = self._query(query)
> 
>   File "/usr/local/lib/python3.7/dist-packages/pymysql/cursors.py",
> line 310, in _query
> 
>     conn.query(q)
> 
>   File
> "/usr/local/lib/python3.7/dist-packages/pymysql/connections.py", line
> 548, in query
> 
>     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
> 
>   File
> "/usr/local/lib/python3.7/dist-packages/pymysql/connections.py", line
> 775, in _read_query_result
> 
>     result.read()
> 
>   File
> "/usr/local/lib/python3.7/dist-packages/pymysql/connections.py", line
> 1156, in read
> 
>     first_packet = self.connection._read_packet()
> 
>   File
> "/usr/local/lib/python3.7/dist-packages/pymysql/connections.py", line
> 692, in _read_packet
> 
>     packet_header = self._read_bytes(4)
> 
>   File
> "/usr/local/lib/python3.7/dist-packages/pymysql/connections.py", line
> 749, in _read_bytes
> 
>     CR.CR_SERVER_LOST, "Lost connection to MySQL server during query"
> 
> pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server
> during query')

知道吗？谢谢：(

Answer 1

使用类似的光标时，在元组中传递lista[1]、lista[2]、str(消息有效载荷(

cursor.execute(sql, (lista[1], lista[2],str(msg.payload)))

这将解决你的问题。