How can I bind a list to a parameter in a custom query in SQLAlchemy?
PythonSqlalchemyPython Problem Overview
I am using this SQL for performance reasons:
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)
However, I got an exception:
NotSupportedError: (NotSupportedError) ('Python type list not supported. param=1', 'HY097')
Is there a workaround that can allow me to bind a list to the parameter of the 'in' clause?
Python Solutions
Solution 1 - Python
A new approach to this problem that works for any database (not just relying on psycopg2's type adaptation) uses expanding bind parameters:
sql_tmpl = """delete from Data where id_data in :iddata"""
params = { 'iddata': [1, 2, 3, 4], }
# session is a session object from sqlalchemy
t = text(sql_tmpl)
t = t.bindparams(bindparam('iddata', expanding=True))
self.session.execute(t, params)
Solution 2 - Python
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 inclinationcitation 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
I found that without the wrapper call to sqlalchemy.text
, it gave a ProgrammingError: syntax error at or near ":"
.
Solution 3 - Python
Try it without the parentheses around, :iddata
. That has been working for me.
sql_tmpl = """delete from Data where id_data in :iddata """
Solution 4 - Python
Use a tuple instead of a list and you don't need parentheses around the parameter in the query:
sql_tmpl = "delete from Data where id_data in :iddata"
params = {
'iddata':(1, 2, 3, 4),
}
self.session.execute(text(sql_tmpl), params)
Solution 5 - Python
As far as I'm aware, none of the SQL engines allow passing in an array parameter. The way that SQLAlchemy handles this is to pass in a parameter for each item in the array.
>>> from sqlalchemy.sql import table, column
>>> print(table('Data').delete(column('id_data').in_([5, 6, 7,])))
DELETE FROM "Data" WHERE id_data IN (:id_data_1, :id_data_2, :id_data_3)
If you don't use the SQLAlchemy expression constructs, you would need to do this manually.
Solution 6 - Python
Adding to dwanderson's answer: using SQLAlchemy, you can use the func method to add the "any" function to a query. This works for me using SQLAlchemy 1.0.9 and a PostgreSQL database.
Generic example:
from sqlalchemy import func
# some list
id_list = [1, 2, 3]
# assuming you have created a session
query = session.query(Table.user_name, Table.user_id).\
filter(Table.user_id == func.any(id_list))
# one way of running the query
query.all()
You can verify that the list is passed as a single parameter (as opposed to a parameter per object in the list).
print(query.statement)
> SELECT user_id, user_name > FROM table > WHERE table.user_id = any(:any_1)
Solution 7 - Python
You can generate a where clause using a loop and use ** to break out the list in the query.execute parameters. Here's an example: https://gist.github.com/pawl/555e5eecce77d4de0ada
Solution 8 - Python
In Microsoft SQL Server, you can use Table Valued Parameters to accomplish the same thing.
SELECT * FROM table_name WHERE customer_id in (SELECT * FROM @MyTVP)
TVP's are currently only supported with PyTDS, not PyODBC. The expanding=True
flag detailed by Jason Damiani is probably the best way to do this if you have a newer version of SQLAlchemy. But TVP's will do in a pinch.
Solution 9 - Python
In case you are dealing raw SQL, you can go with this approach:
ids = [1000032, 1000048]
sql = 'SELECT CompNo, CompName, CompType FROM Component WHERE DeptID IN %(ids)s' % {"ids": tuple(ids)}
cursor.execute(sql)