python - 将变量传递给python中的sql查询时出错
问题描述
我在 python 中使用 sql 查询。我想在查询中将一些值作为参数/变量传递。我见过几个相关的问题。我正在关注这些问题,但我不确定我哪里出错了。我的代码
import mysql.connector
floor = 'L2'
cursor.execute ("""SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow , t1.nvo_air_damper_position , t1.nvo_temperature_sensor_pps
FROM
(SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date='2019-12-10' and floor=?1) t1
INNER JOIN
(SELECT date,max(timestamp) as timestamp,vavId from vavData where date='2019-12-10' and floor=?2 group by vavId) t2
ON (t1.timestamp = t2.timestamp) """,(floor,floor))
这给出了错误:
并非所有参数都在 SQL 语句中使用
我也尝试过使用 %s,但该方法也出现错误。有人可以帮助我如何正确传递变量吗?
解决方案
使用%s
参数标记,使您的查询变为
>>> q="""SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow , t1.nvo_air_damper_position , t1.nvo_temperature_sensor_pps
FROM
(SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date='2019-12-10' and floor=%s) t1
INNER JOIN
(SELECT date,max(timestamp) as timestamp,vavId from vavData where date='2019-12-10' and floor=%s group by vavId) t2
ON (t1.timestamp = t2.timestamp) """
>>> cursor.execute(q,('xxxx', 'xxxx'))
现在按预期运行python 3.7
它。mysql.connector.version.VERSION (8, 0, 18, '', 1)
也检查这个答案
推荐阅读
- flutter - Flutter 代码混淆会在流程中剥离代码吗?
- api - 尝试获取令牌时遇到问题“无法解析 JSON 文档”,请放心
- python-3.x - gpiozero 在获取 load_average 时生成 io.TextIOWrapper 'noise'
- linux - 在 GNU - Octave 6.2.0 中安装控制包时出现 pkg 'make' 错误
- javascript - 带有扩展内容的窗口滚动
- typescript - TypeScript 中函数的数据类型
- docker - docker容器中的wkhtmltopdf问题
- android - E/AndroidRuntime: 致命异常: main
- c# - 在 Azure Functions(Azure 门户)中添加 AWS Kinesis Firehose Nuget 包作为依赖项
- google-analytics-api - 通过客户 uid 查询 firebase api