pyodbc - 如何使用变量作为参数执行选择语句 [英] pyodbc - How to perform a select statement using a variable for a parameter

问题描述

我正在尝试遍历名为吞吐量"的表中的所有行，但针对特定的 DeviceName(我已将其存储在 data['DeviceName'] 中.我尝试了以下操作，但它不起作用:

I'm trying to iterate through all the rows in a table named Throughput, but for a specific DeviceName (which I have stored in data['DeviceName']. I've tried the following, but it doesn't work:

for row in cursor.execute("select * from Throughput where DeviceName=%s"), %(data['DeviceName']):

也试过这个，但它不起作用:

also tried this but it doesn't work:

for row in cursor.execute("select * from Throughput where(DeviceName), values(?)", (data['DeviceName']) ):

我的最终工作代码片段:

A snippet of my final working code:

query = "select * from Throughput where DeviceName = '%s'" % data['Device Name']
      try:
          for row in cursor.execute(query):

推荐答案

您还可以参数化 语句:

...
cursor.execute("select * from Throughput where DeviceName = ?", data['DeviceName'])
...

这是一种更好的方法，原因如下:

This a better approach for the following reasons:

• 防止 SQL 注入(无论使用参数化 SQL 还是动态 SQL，您都应始终验证用户输入)
• 您不必担心用单引号转义 where 子句值，因为参数是单独传递给数据库的
• SQL 准备一次，查询的后续执行使用准备好的语句而不是重新编译

这篇关于pyodbc - 如何使用变量作为参数执行选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！