如何在SQLAlchemy和Firebird中的自定义查询中将Python列表作为参数绑定? [英] How can I bind a Python list as a parameter in a custom query in SQLAlchemy and Firebird?

问题描述

我正在使用带有SQLAlchemy的Firebird数据库作为ORM包装器.

I am using Firebird database with SQLAlchemy as ORM wrapper.

我知道通过使用in_可以在IN子句中传递sales_id列表并获得结果.

I know that by using in_ it is possible to pass the sales_id list in IN clause and get the result.

我有一个用例，其中必须使用文本sql .

I have a use case where I must use textual sql.

这是我的摘录，

conn.execute('select * from sellers where salesid in (:sales_id)', sales_id=[1, 2, 3] ).fetchall()

这总是抛出token unknown error

我所需要做的就是传递sales_id([1, 2, 3])的列表以绑定参数(:sales_id)并获取结果集.

All I need is to pass the list of sales_id ([1, 2, 3]) to bind parameter (:sales_id) and get the result set.

推荐答案

如果使用的DB-API驱动程序不提供对元组和列表的特殊处理以生成用于行构造函数和IN谓词的表达式，则可以使用 bindparam提供的某种新功能扩展" :

If using a DB-API driver that does not provide special handling of tuples and lists for producing expressions for row constructors and IN predicates, you can use the somewhat new feature "expanding" provided by bindparam:

stmt = text('select * from sellers where salesid in :sales_id') 
stmt = stmt.bindparams(bindparam('sales_id', expanding=True))

conn.execute(stmt, sales_id=[1, 2, 3]).fetchall()

这将在每次查询的基础上用所需的占位符替换占位符sales_id，以适应用作参数的序列.

This will replace the placeholder sales_id on a per query basis by required placeholders to accommodate the sequence used as the parameter.

这篇关于如何在SQLAlchemy和Firebird中的自定义查询中将Python列表作为参数绑定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！