我在azure db上使用以下代码时遇到了麻烦,而它在mysql db上使用. 解释为什么会发生这种情况,并帮助解决它将受到赞赏.
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`
生成的mysql查询是:'SELECT count(*) FROM ticker WHERE id in %(ids)s'
参数:{'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