如何在pyodbc中使用executemany运行多个SELECT查询

Question

我使用PYODBC根据一个dataframe列的值多次查询SQL DB（如下所示为一个值列表，因为我使用了ToList（）函数将该列转换为一个列表）

#the connection string
cnxn = pyodbc.connect(driver='{SQL Server}', server = 'NameOfTheServer',autocommit = True,uid ='name',pwd ='password')

cursor = cnxn.cursor()
params = ['3122145', '523532236']
sql = ("""
    SELECT  SO.column
    FROM    table AS SO
    WHERE SO.column = ?
    """)
cursor.executemany(sql, params)
row = cursor.fetchone()

即使我正在使用列表，executemany函数也会引发错误： TypeError:（“参数必须位于列表、元组或行”“HY000”“中）

Answer 1

.executemany不用于SELECT语句。如果我们尝试，我们只能返回最后一行：

cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("CREATE TABLE #tmp (id int primary key, txt varchar(10))")
crsr.execute(
    "INSERT INTO #tmp (id,txt) "
    "VALUES (1,'one'),(2,'two'),(3,'three'),(4,'four'),(5,'five')"
)
print(crsr.execute("SELECT * FROM #tmp").fetchall())
"""console output:
[(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five')]
"""
sql = "SELECT * FROM #tmp WHERE id = ?"
list_of_tuples = [(1,), (3,), (5,)]
crsr.executemany(sql, list_of_tuples)
print(crsr.fetchall())
"""console output:
[(5, 'five')]
"""
try:
    crsr.nextset()
    print(crsr.fetchall())
except pyodbc.ProgrammingError as pe:
    print(pe)
    """console output:
    No results.  Previous SQL was not a query.
    """

相反，我们需要构建一个参数占位符字符串，并在in子句中使用它，如下所示：

tuple_of_scalars = tuple(x[0] for x in list_of_tuples)
sql = f"SELECT * FROM #tmp WHERE id IN ({','.join('?' * len(tuple_of_scalars))})"
print(sql)
"""console output:
SELECT * FROM #tmp WHERE id IN (?,?,?)
"""
crsr.execute(sql, tuple_of_scalars)
print(crsr.fetchall())
"""console output:
[(1, 'one'), (3, 'three'), (5, 'five')]
"""