错误1064（42000）：SQL语法错误。。。接近“

Question

我被困了几天，在这个网站上尝试了对类似问题的大部分答案

在开始之前，我想指出我是通过ssh工作的，只能通过nano编辑代码（不是我的选择…）

我的问题如下：

我正在使用MariaDB存储raspberry pi CPU温度以及摄像头温度，以绘制温度演变图。尝试在数据库中插入值时出错

我在cpu_tempDB中有下表

MariaDB [cpu_tempDB]> show columns from CPU_TEMP_TABLE;
+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| ID          | bigint(20)   | NO   | PRI | NULL                | auto_increment |
| CPU_TEMP    | decimal(6,2) | NO   |     | NULL                |                |
| CREATED     | timestamp    | NO   |     | current_timestamp() |                |
| CAMERA_TEMP | decimal(6,2) | NO   |     | 0.00                |                |
+-------------+--------------+------+-----+---------------------+----------------+

在python代码中，我使用以下函数：

from gpiozero import CPUTemperature
import mysql.connector as mariadb

# get CPU temperature in Celsius
cpu = CPUTemperature()
cpu_temp = cpu.temperature

# get Camera temperature in Celsius
tempfile = open("/home/pi/allsky/temperature.txt","r")
cam_temp = tempfile.read().rstrip()

# make a new mariaDB entry and retrieve old values
try:
   # open connection
   mariadb_connection = mariadb.connect(host= "localhost", user="pi", password="--Sensored--", database="cpu_tempDB")
   cursor = mariadb_connection.cursor()
   # upload
   sql = "insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP) values (%s, %s)"
   args = cpu_temp, float(cam_temp)
   cursor.execute(sql,args)
   mariadb_connection.commit()
   # fetch
   cursor.execute("select * from CPU_TEMP_TABLE where CPU_TEMP_TABLE.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY")
   records = cursor.fetchall()
except mariadb.Error as e:
   print("Error writing cpu temp to mariaDB:",e)
finally:
   mariadb_connection.close()
   cursor.close()
# store data in lists
time_list = []
cpu_temp_list = []

for row in records:
  cpu_temp_list.append(row[1])
  time_list.append(row[2])

# declare date formatter for plot
myFmt = mdates.DateFormatter('%H:%M')

# generate plot
mpl.use('Agg')


plt.plot(time_list, cpu_temp_list, 'b-')
plt.xlabel('Time')
plt.ylabel('CPU Temperature [°C]')
plt.title('CPU Temperature evolution over the last 48 hours')
plt.gca().xaxis.set_major_formatter(myFmt)

# save plot
plt.savefig('cpu_temp.png')

我得到以下错误：

Error writing cpu temp to mariaDB: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
Traceback (most recent call last):
  File "CPU_temp.py", line 45, in <module>
    for row in records:
NameError: name 'records' is not defined

编辑： 我在错误之前添加了一个打印（sql，args），这是控制台打印

insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP) values (%s, %s) (64.27, 58.2)

编辑2：添加了代码的其余部分，因为有些人提到错误警告显示在显示的代码之外

编辑3:从0重新启动，现在可以工作。。。很遗憾，我不能删除这个帖子

Answer 1

此查询中存在问题：

select *
from CPU_TEMP_TABLE 
where CPU_TEMP_TABLE.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY

DATE_SUB函数的括号不是闭合的。解决方法是添加右括号：

select * 
from CPU_TEMP_TABLE 
where CPU_TEMP_TABLE.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)

不幸的是，错误信息是如此难以理解

Answer 2

您需要将参数放入元组：

sql = "insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP) values (%s, %s)"
args = (cpu_temp, float(cam_temp),)
cursor.execute(sql,args)

或在适当位置构造字符串：

sql = "insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP)
    values (%s, %s)" % (cpu_temp, float(cam_temp),)
cursor.execute(sql)

Answer 3

我从头重写了所有内容，定义了两个不同的游标，它在某种程度上起了作用

谢谢你的帮助

以下是工作代码：

from gpiozero import CPUTemperature
from time import sleep, strftime, time
import mysql.connector as mariadb
import matplotlib.pyplot as plt
import matplotlib as mpl
import datetime
import matplotlib.dates as mdates

# get CPU temperature in Celsius
cpu = CPUTemperature()
cpu_temp = cpu.temperature

# get Camera temperature in Celsius
tempfile = open("/home/pi/allsky/temperature.txt","r")
cam_temp = tempfile.read().rstrip()

# make a new mariaDB entry and retrieve old values
try:
   mariadb_connection = mariadb.connect(user='pi',password='...',database='cpu_tempDB')
   cursor1 = mariadb_connection.cursor()
   sql1 = "insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP) values (%s, %s)"
   args = (cpu_temp, float(cam_temp))
   cursor1.execute(sql1,args)
   mariadb_connection.commit()

   cursor2 = mariadb_connection.cursor()
   sql2 = "select * from CPU_TEMP_TABLE where CREATED > DATE_SUB(NOW(), INTERVAL 5 DAY)"
   cursor2.execute(sql2)
   records = cursor2.fetchall()
except mariadb.Error as e:
   print("Error mariaDB:",e)
finally:
   mariadb_connection.close()
   cursor1.close()
   cursor2.close()
# store data in lists
time_list = []
cpu_temp_list = []
cam_temp_list = []

for row in records:
  cpu_temp_list.append(row[1])
  time_list.append(row[2])
  cam_temp_list.append(row[3])

# declare date formatter for plot
myFmt = mdates.DateFormatter('%H:%M')

# generate plot
mpl.use('Agg')


plt.plot(time_list, cpu_temp_list, 'b-')
plt.xlabel('Time')
plt.ylabel('CPU Temperature [°C]')
plt.title('CPU Temperature evolution over the last 48 hours')
plt.gca().xaxis.set_major_formatter(myFmt)

# save plot
plt.savefig('cpu_temp.png')