时间戳

首页 > 解决方案 > How can I resolve this SQLAlchemy error regarding SQL parameters?

python - How can I resolve this SQLAlchemy error regarding SQL parameters?

问题描述

I am passing a very simple SQL query to a server via Python's SQLAlchemy library. Here is my code:

def query4():
    ed_notes = sa.Table("ED_NOTES_MASTER",metadata,autoload=True,autoload_with=engine)
    note_query = sa.select([ed_notes.columns["PT_FIN"],
        ed_notes.columns["RESULT_TITLE_TEXT"],
        ed_notes.columns["RESULT"],
        ed_notes.columns["RESULT_DT_TM"]]).where(ed_notes.columns["PT_FIN"].in_(unique_fins)).where(start_time<ed_notes.columns["RESULT_DT_TM"]).where(end_time>ed_notes.columns["RESULT_DT_TM"])
    result = connection.execute(note_query)
    resultset = result.all()
    note_data_prelim = pd.DataFrame(resultset)
    return note_data_prelim

the variable "unique_fins" is a list of over 50,000 unique identifiers that I am trying to specifically query. When this query is run, the following error results:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains -11872 parameter markers, but 53664 parameters were supplied', 'HY000')

Any ideas on what is going on? The problem is definitely arising in the ed_notes.columns["PT_FIN"].in_(unique_fins) portion of the query.

Thanks in advance!

标签： pythonsqlsqlalchemyodbcpyodbc

解决方案

in_操作员会将您的列表unique_fins转换为每个项目的单个参数。您可能会达到基础数据库的参数限制。

在这里参考这个答案，你可以做这样的事情；

.where(ed_notes.columns["PT_FIN"] == func.any_(unique_fins))

推荐阅读