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

Posted on

Question :

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

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)     

However I got an exception

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

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

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 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 ":".

Answered By: dwanderson

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)
Answered By: Jason Damiani

Answer #3:

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

sql_tmpl = """delete from Data where id_data in :iddata """
Answered By: Conrad

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.

Answered By: Gary van der Merwe

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)

Answered By: Ido S

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)     
Answered By: k26dr

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

Answered By: pawl

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.

Answered By: susodapop

Leave a Reply

Your email address will not be published. Required fields are marked *