错误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)

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