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
– Mark Rotteveel, Commented Apr 23, 2019 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
– Niranj Rajasekaran, Commented Apr 23, 2019 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
– Mark Rotteveel, Commented Apr 23, 2019 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 stackoverflow.com/a/43997801/976391 — Arioch 'The
– Arioch 'The, Commented Apr 24, 2019 at 10:43

Ilja Everilä · Accepted Answer · 2019-04-23 12:52:33Z

10

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, 2019 at 12:52

answered Apr 23, 2019 at 12:47

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Niranj Rajasekaran Over a year ago

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ä

Ilja Everilä Over a year ago

What conn is? At least Engine.execute() accepts keyword arguments as arguments to query.

Ilja Everilä Over a year ago

As does Connection.execute().

Niranj Rajasekaran Over a year ago

conn is the connection object

Collectives™ on Stack Overflow

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

Environment

Backgound

Question

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Environment

Backgound

Question

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related