python - 无法将记录插入 table_sensor_log 表 - 语法错误 - 无法将记录插入表 - 42000

Question

我正在尝试从 BME280 传感器获取读数，并使用 python 脚本将它们作为一行写入 Mysql 数据库中。我确信答案非常简单，但是多年来我一直在用头撞墙。

当前错误是:无法将记录插入 table_sensor_log 表 1064 (42000):您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在第 2 行的 '09:29:03.708933,3fa924c8-73b1-4986-a9bc-f13fdc15163f,25.7461863119)' 附近使用的正确语法MySQL连接已关闭

我的传感器读数正确。

几乎尝试了我能想到的一切 - 多次重新编写代码。

import smbus2
import bme280

port = 1
address = 0x76
bus = smbus2.SMBus(port)

calibration_params = bme280.load_calibration_params(bus, address)

# the sample method will take a single reading and return a
# compensated_reading object
data = bme280.sample(bus, address, calibration_params)

# the compensated_reading class has the following attributes
timest = data.timestamp
id = data.id
temperature = data.temperature

# there is a handy string representation too
print(data)
import mysql.connector
from mysql.connector import Error
try:
   connection = mysql.connector.connect(host='localhost',
                             database='sensor_log',
# the compensated_reading class has the following attributes
timest = data.timestamp
id = data.id
temperature = data.temperature

# there is a handy string representation too
print(data)
import mysql.connector
from mysql.connector import Error
try:
   connection = mysql.connector.connect(host='localhost',
                             database='sensor_log',
                             user='superadmin',
                             password='Chillipo3')
   sql_insert_query = """ INSERT INTO `table_sensor_log`
                          (`timestamp`, `sensor_id`, `sensor_value`) VALUES (%s,%s,%s);""" % (timest,id,temperature,)
   cursor = connection.cursor()
   result  = cursor.execute(sql_insert_query)
   connection.commit()
   print ("Record inserted successfully into table_sensor_log table")
except mysql.connector.Error as error :
    connection.rollback() #rollback if any exception occured
    print("Failed inserting record into table_sensor_log table {}".format(error))
finally:
    #closing database connection.

读数正常 - 我把它们留在里面是为了证明我的传感器没有问题。

compensated_reading(id=3fa924c8-73b1-4986-a9bc-f13fdc15163f，时间戳=2019-07-27 09:29:03.708933，温度=25.746 °C，压力=1009.16 hPa，湿度=55.00 % rH)无法将记录插入 table_sensor_log 表 1064 (42000):您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在第 2 行的 '09:29:03.708933,3fa924c8-73b1-4986-a9bc-f13fdc15163f,25.7461863119)' 附近使用的正确语法MySQL 连接已关闭2019-07-27 09:29:03.708933

Answer 1

您不应尝试使用 python 格式自行格式化查询。 execute接受第二个参数，它是您想要插入到查询中的变量

cursor.execute("""
    INSERT INTO `table_sensor_log`
        (`timestamp`, `sensor_id`, `sensor_value`)
    VALUES (%s,%s,%s);
""", [timest,id,temperature])

数据库驱动程序将负责转义/引用您传递的变量