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

Question

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?

Answer 1

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)

Answer 2

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 3

Try it without the parentheses around, :iddata. That has been working for me.

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

Answer 4

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 5

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 6

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)

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.

Answer 9

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)