Pyodbc 变量查询

Question

>我正在尝试使用变量进行查询，但字符串变量不起作用。我应该如何格式化查询中的变量PrimaryAxis和SecondaryAxis？我使用了来自 https://github.com/mkleehammer/pyodbc/wiki/Getting-started#parameters 的参考 在页面上使用单引号。我尝试了单引号和双引号，但没有运气。

import pyodbc
# Connect to database
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:TempTestDB.accdb;'
r'Uid=;'
r'Pwd=;'
)
# Make cursor
connection = pyodbc.connect(conn_str)
connection.setencoding('utf-8')
cursor = connection.cursor()
# Create test table
cursor.execute("CREATE TABLE Coordinates (ID integer, X integer, Y integer)")
connection.commit()
# Create test data (Error "Missing semicolon (;)" if multiple values in one insert, thats why multiple insertions... not the main question)
cursor.execute("INSERT INTO Coordinates (ID, X, Y) VALUES (1,10,10);")
cursor.execute("INSERT INTO Coordinates (ID, X, Y) VALUES (2,20,10);")
cursor.execute("INSERT INTO Coordinates (ID, X, Y) VALUES (3,30,10);")
connection.commit()
# Filter parameters
Line = 10
Start = 10
End = 30
# Works
cursor.execute(r"""
SELECT *
FROM Coordinates
WHERE Y = ? AND X BETWEEN ? AND ? """, Line, Start, End )
rows = cursor.fetchall()
for row in rows:
print(row)
# does not work - main question
PrimaryAxis = 'X'
SecondaryAxis = 'Y'
cursor.execute(r"""
SELECT *
FROM Coordinates
WHERE ? = ? AND ? BETWEEN ? AND ? """, SecondaryAxis, Line, PrimaryAxis, Start, End )
rows = cursor.fetchall()
for row in rows:
print(row)

Answer 1

您可以简单地使用数据库占位符：

# Filter parameters
line = 10
start = 10
end = 30
primary_axis = "X"
secondary_axis = "Y"
query = """
SELECT *
FROM Coordinates
WHERE ? = ? AND ? BETWEEN ? AND ?
"""
cursor.execute(query, (secondary_axis, line, primary_axis, start, end))
...