How can I resolve this SQLAlchemy error regarding SQL parameters?

Question

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!

What is the underlying database too? possible you've hit the parameter limit. — Peter Henry, Nov 15 '21 at 10:21

score 0 · Answer 1 · answered Nov 15 '21 at 10:41

The in_ operator will convert your unique_fins list into a single parameter per item. You'll likely hit parameter limits on your underlying database.

Referring to this answer here you could do something like this;

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

How can I resolve this SQLAlchemy error regarding SQL parameters?

1 Answers1