SqlAlchemy bindparam fails on mssql (but works on mysql)

Question

I am having trouble making the following work on an azure db, while it works on mysql db. Explanation why this is happening and help with resolving it would be appreciated.

        with engine.begin() as conn:
            print(f"Running with {engine.name=}")
            sql_q = text("SELECT count(*) FROM ticker WHERE id in :ids")
            sql_q.bindparams(bindparam('ids', expanding=True))

            result = conn.execute(
                sql_q, ids=[300, 400]
            ).scalar_one()
            print(f"{engine.name=}: {result=}")

    Running with engine.name='mysql'
    engine.name='mysql': result=2`

The generated mysql query is: 'SELECT count(*) FROM ticker WHERE id in %(ids)s' with parameters: {'ids': [300, 400]}

# with mssql engine (azure db)
Running with engine.name='mssql'
# ...
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ("A TVP's rows must be Sequence objects.", 'HY000')
[SQL: SELECT count(*) FROM ticker WHERE id in ?]
[parameters: ([300, 400],)]
(Background on this error at: https://sqlalche.me/e/14/f405)

• Python 3.8.10
• SQLAlchemy==1.4.39
• pyodbc==4.0.39

Answer 1

Try sql_q = sql_q.bindparams(bindparam('ids', expanding=True))

bindparams doesn't modify the existing TextClause object in place and you have to reassign it.