如何在pyodbc中使用executemany运行多个SELECT查询 [英] How to use executemany in pyodbc to run multiple SELECT queries

问题描述

我使用 PYODBC 根据 Pandas 数据框列的值多次查询 SQL DB(如下所示为值列表，因为我使用 ToList() 函数将列转换为列表.

Im using PYODBC to query an SQL DB multiple times based on the values of a pandas dataframe column (seen below as a list of values, since I used the ToList() function to turn the column into a list.

#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: ('Params must be in a list, tuple, or Row', 'HY000')

the executemany function throws an error even though I'm using a list: TypeError: ('Params must be in a list, tuple, or Row', 'HY000')

推荐答案

.executemany 不打算与 SELECT 语句一起使用.如果我们尝试，我们只会得到最后一行:

.executemany is not intended to be used with SELECT statements. If we try, we only get the last row back:

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 子句中使用它，如下所示:

Instead, we need to build a string of parameter placeholders and use it in an IN clause, like this:

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')]
"""

这篇关于如何在pyodbc中使用executemany运行多个SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！