我正在制作数据库以用变量填写表格

Question

import pymysql
from datetime import datetime

db = pymysql.connect(host = "localhost", user = "root", password = "mariadb", charset = "utf8");
cursor = db.cursor();
nm = 'park dong ju'
temp = 36.5
n_route = '->podium',

if nm != "" and temp != 0:
cursor.execute("USE SD;")
select_name ="SELECT name FROM PI WHERE name = '%s'"
select_route = "SELECT route FROM PI WHERE name = '%s'"
cursor.execute(select_name,(nm,))
PI_name = cursor.fetchone()
cursor.execute(select_route,(nm,))
PI_route = cursor.fetchone()
db.commit()
str_route = str(PI_route)
route = str_route + n_route
current_time = datetime.now()
insert_er = "INSERT INTO ER(name,temp,route,time) VALUES('%s',%.2f,'%s','%s')"
cursor.execute(insert_er,(nm,tmep,route,current_time))
name = ""
temp = 0

db.commit()
db.close()

这是我的代码

pymysql.err。ProgrammingError:(1064， "你的SQL语法有一个错误;查看与您的MariaDB服务器版本对应的手册，以获取在第1行")

处'park_dong_ju " '附近使用的正确语法。

代码

错误

Answer 1

当你使用MySql占位符时，你不需要格式化它们，也不需要使用引号。MySql游标将尝试转换您的数据类型。您可以将查询更改为:

insert_er = "INSERT INTO ER(name,temp,route,time) VALUES(%s,%s,%s,%s)"    
cursor.execute(insert_er,(nm,tmep,route,current_time))

你也可以修改你的第一个查询，并删除引号:

select_name ="SELECT name FROM PI WHERE name = %s"
select_route = "SELECT route FROM PI WHERE name = %s"
cursor.execute(select_name,(nm,))
PI_name = cursor.fetchone()
cursor.execute(select_route,(nm,))