How can I bind a list to a parameter in a custom query in sqlalchemy?

Answer #1:

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 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

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

Answer #2:

New 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)

Answer #3:

Try without the parens around :iddata That has been working for me.

sql_tmpl = """delete from Data where id_data in :iddata """

Answer #4:

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.

Answer #5:

Adding to dwanderson‘s answer: using SQLAalchemy 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 Postgres DB.

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)

Answer #6:

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)     

Answer #7:

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

Answer #8:

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.