How can I bind a Python list as a parameter in a custom query in SQLAlchemy and Firebird?

Question

Environment

I am using Firebird database with SQLAlchemy as ORM wrapper.

Backgound

I know that by using in_ it is possible to pass the sales_id list in IN clause and get the result.

I have a use case where I must use textual sql.

Question

Here is my snippet,

conn.execute('select * from sellers where salesid in (:sales_id)', sales_id=[1, 2, 3] ).fetchall()

This always throws token unknown error

All I need is to pass the list of sales_id ([1, 2, 3]) to bind parameter (:sales_id) and get the result set.

Firebird itself doesn't support passing arrays or lists to a parameter in an IN-predicate, it only supports discrete values here. Which driver are you using, and does it provide special support for this? — Mark Rotteveel, Apr 23 '19 at 12:37
I am using DP-API driver, which I think is the default. As @Ilja Everilä `bindparam` is the method as of now. Thanks, Mark for your effort. — Niranj Rajasekaran, Apr 23 '19 at 14:56
DB-API is the Python specification for drivers, not a driver itself. Are you using FDB or pyfirebirdsql as your driver? — Mark Rotteveel, Apr 23 '19 at 18:56
Sometimes it may make sense to reverse the IN-condition and use SQL `CONTAINING` instead, if your list can grow truly long. See alternative approach at https://stackoverflow.com/a/43997801/976391 — Arioch 'The, Apr 24 '19 at 10:43

Ilja Everilä · Accepted Answer · 2019-04-23T12:52:33.580

9

If using a DB-API driver that does not provide special handling of tuples and lists for producing expressions for row constructors and IN predicates, you can use the somewhat new feature "expanding" provided by bindparam:

stmt = text('select * from sellers where salesid in :sales_id') 
stmt = stmt.bindparams(bindparam('sales_id', expanding=True))

conn.execute(stmt, sales_id=[1, 2, 3]).fetchall()

This will replace the placeholder sales_id on a per query basis by required placeholders to accommodate the sequence used as the parameter.

edited Apr 23 '19 at 12:52

answered Apr 23 '19 at 12:47

Ilja Everilä

50,538
7
126
127

3

`conn.execute(stmt, sales_id=[1, 2, 3]).fetchall()` returns `TypeError: get_bind() got an unexpected keyword argument 'sales_id'`. By changing to `conn.execute(stmt, {'sales_id': [1, 2, 3]} ).fetchall()`, I am able to get what I needed. Thanks Ilja Everilä – Niranj Rajasekaran Apr 23 '19 at 15:00
What `conn` is? At least `Engine.execute()` accepts keyword arguments as arguments to query. – Ilja Everilä Apr 23 '19 at 17:25
As does [`Connection.execute()`](https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.Connection.execute). – Ilja Everilä Apr 23 '19 at 17:40
`conn` is the connection object – Niranj Rajasekaran Apr 24 '19 at 03:34

How can I bind a Python list as a parameter in a custom query in SQLAlchemy and Firebird?

Environment

Backgound

Question

1 Answers1

Linked