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

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 6

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

Using this example from above:

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

I had to make change func.any, to func.all and then it worked! Good luck

Answer 10

Jason Damiani's answer works when you are performing an execution. But if you want to get the stringified form of the text clause, you need to use bindparam() and apply expanding=True.

Example:

mylist = [1,2,3]
query = text("mycolumn IN :arr")
  .bindparams(bindparam(key="arr", value=mylist, expanding=True))
  .compile(compile_kwargs={"literal_binds": True})
print(query)

Answer 11

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)