python - 错误 1064 (42000):您的 SQL 语法错误...在“”附近
问题描述
我被困住了几天,并尝试了这个网站上类似问题的大部分答案。
在开始之前,我想指出我正在通过 ssh 工作,并且只能通过 nano 编辑代码(不是我的选择......)
我的问题如下:
我正在使用 MariaDB 沿相机温度存储树莓派 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
编辑:我在错误之前添加了一个 print(sql, args) ,这是控制台打印
insert into CPU_TEMP_TABLE (CPU_TEMP, CAMERA_TEMP) values (%s, %s) (64.27, 58.2)
编辑2:添加了其余代码,因为你们中的一些人提到了错误警告显示在显示的代码之外
编辑 3:从 0 重新启动,现在它可以工作了......不幸的是,我无法删除这篇文章
解决方案
您需要将参数放入元组中:
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)
推荐阅读
- python - Python __init__ * argument
- selenium - Not able to connect to local workspace with VSTS by TFS in Selenium WebDriver
- c++ - Threads Synchronization Using Mutex
- python - 如何将两个参数项附加到堆栈中(Python3)
- node.js - 无法运行 Node-red 服务器?
- android - 以字母开头的房间数据库查询
- javascript - 在 for 循环中将 let 更改为 const 并使用 i+1 更改 i++ 后出错
- web-scraping - 即使机器人关闭,Wget 也会重定向
- python - Python图像到文本
- mongodb - MongoDB 聚合两个集合,返回附加字段作为计数