如何在 sqlalchemy 的自定义查询中将列表绑定到参数? [英] How can I bind a list to a parameter in a custom query in sqlalchemy?

问题描述

我出于性能原因使用此 sql

I am using this sql for performance reason

 sql_tmpl = """delete from Data where id_data in (:iddata) """
 params = {  
                    'iddata':[1, 2,3 4],
                    }
 # session is a session object from   sqlalchemy
 self.session.execute(text(sql_tmpl), params)     

但是我有一个例外

NotSupportedError: (NotSupportedError) ('Python type list not supported.  param=1', 'HY097') 

是否有任何解决方法可以让我将列表绑定到in"子句的参数?

Is there any workaround that can allow me to bind a list to the parameter of the 'in' clause?

推荐答案

一个老问题的新答案，因为自从这个问题/已接受的答案首次发布以来，一些底层功能似乎发生了变化(正如@vicvicvic 所暗示的那样)在@Gary 的回答中，但我觉得这应该是一个提高可见度的答案).

New answer to an old question because it seems like some of the underlying functionality has changed since this question/accepted answer were first posted (as alluded to by @vicvicvic in @Gary's answer, but I feel it should be an answer for better visibility).

psycopg2 现在支持 类型适应，除其他外，它允许将列表传递到查询中的单个参数化值中.这也适用于 SQLAlchemy，至少对于对 postgresql 数据库的原始 SQL 式查询(我无权访问其他数据库类型，所以我不知道 sqlalchemy 是否会尊重这个约定适用于其他数据库，但我倾向于^{需要的引用}是它会起作用).

psycopg2 now supports type adaptation, which allows, among other things, the ability to pass a list into a single parameterized value in the query. This also works in SQLAlchemy, at the very least for raw-SQL-esque queries to a postgresql database (I don't have access to other database types, so I don't know if sqlalchemy will respect this convention for other databases, but my inclination^{citation needed} is that it will work).

some_ids = [1, 2, 3, 4]
query = "SELECT * FROM my_table t WHERE t.id = ANY(:ids);"
conn.execute(sqlalchemy.text(query), ids=some_ids)
## runs just fine

我发现如果没有对 sqlalchemy.text 的包装器调用，它会给出一个 ProgrammingError: syntax error at or near ":".

I found that without the wrapper call to sqlalchemy.text, it gave a ProgrammingError: syntax error at or near ":".

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