如何传递 SELECT 查询的 IN 子句的参数来检索 pandas DataFrame？

发布于 2025-01-13 15:44:06 字数 632 浏览 0 评论 0原文

尝试将参数传递给简单的 SELECT 查询：

query = """SELECT * from tbl_tab where name in {};"""

我通过这段代码来操作我的数据帧，其想法是能够将 1 到 n 参数传递到我的查询中：

conn = pyodbc.connect(conx_string)
t = tuple(["N","M"])
crsr = conn.cursor()
data = crsr.execute(query.format(t))
rows = [list(x) for x in data]
columns = [column[0] for column in crsr.description]
df = pd.DataFrame(rows, columns=columns)

我得到了预期的结果。但是，当将单个参数传递给 t 时：有 t = tuple(["N"])

我收到错误 编程错误：('42000'，“[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]')'附近的语法不正确。(102) (SQLExecDirectW)")

有什么想法吗？

原文

Trying to pass params to a simple SELECT query :

query = """SELECT * from tbl_tab where name in {};"""

I went through this code to manipulate my dataframe and the idea is to be able to pass 1 to n params into my query :

conn = pyodbc.connect(conx_string)
t = tuple(["N","M"])
crsr = conn.cursor()
data = crsr.execute(query.format(t))
rows = [list(x) for x in data]
columns = [column[0] for column in crsr.description]
df = pd.DataFrame(rows, columns=columns)

And I got the expected result. However, when passing a single param to t : having t = tuple(["N"])

I got the error
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW)")

Any idea please ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

奶茶白久 2025-01-20 15:44:06

您正在使用元组的字符串表示形式注入到SQL语句中。它适用于tuple(["N","M"])，因为你得到… IN ('N', 'M')。但是，tuple(["N"]) 会失败，因为结果是 … IN ('N',) 并且尾随逗号不是有效的 SQL 语法。考虑一种避免 SQL 注入并使用参数化查询的方法。

首先，如果您将 pandas 与 SQLite 以外的任何数据库一起使用，则应该使用 SQLAlchemy。当您将 select() 对象传递给 pandas .read_sql_query() 方法时，SQLAlchemy 将自动构建所需的 SQL 语句：

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199",
)
tbl_tab = sa.Table("tbl_tab", sa.MetaData(), autoload_with=engine)

# full contents of table:
with engine.begin() as conn:
    print(conn.execute(sa.select(tbl_tab)).fetchall())
    # [(1, 'Alicia'), (2, 'Brandon'), (3, 'Candace')]

# retrieve subset of rows into a DataFrame
name_list = ["Alicia", "Brandon"]
my_select = sa.select(tbl_tab).where(tbl_tab.c.name.in_(name_list))
df = pd.read_sql_query(my_select, engine)
print(df)
"""
   id     name
0   1   Alicia
1   2  Brandon
"""

You are using the string representation of a tuple to inject into your SQL statement. It works for tuple(["N","M"]) because you get … IN ('N', 'M'). However, it fails for tuple(["N"]) because the result is … IN ('N',) and the trailing comma is not valid SQL syntax. Consider an approach that avoids SQL injection and uses a parameterized query.

First, if you are using pandas with any database other than SQLite you should be using SQLAlchemy. SQLAlchemy will automatically build the required SQL statement when you pass the select() object to pandas .read_sql_query() method:

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199",
)
tbl_tab = sa.Table("tbl_tab", sa.MetaData(), autoload_with=engine)

# full contents of table:
with engine.begin() as conn:
    print(conn.execute(sa.select(tbl_tab)).fetchall())
    # [(1, 'Alicia'), (2, 'Brandon'), (3, 'Candace')]

# retrieve subset of rows into a DataFrame
name_list = ["Alicia", "Brandon"]
my_select = sa.select(tbl_tab).where(tbl_tab.c.name.in_(name_list))
df = pd.read_sql_query(my_select, engine)
print(df)
"""
   id     name
0   1   Alicia
1   2  Brandon
"""

回复收藏 0 原文

~没有更多了~