How to Bind a List to a Parameter in a Custom Query in SQLalchemy

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

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

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

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.

SQLAlchemy and RAW SQL: List as an input

On python 3.7:

import sqlalchemy

args = [1, 2, 3]
raw_sql = "SELECT * FROM table WHERE data IN :values" 
query = sqlalchemy.text(raw_sql).bindparams(values=tuple(args))
conn.engine.execute(query)

SQLAlchemy core - get a list of bindparams in a query, or check if one exists by name?

You can get the parameters from the compiled query, like this:

>>> q = select([test1]).limit(bindparam('lim')) 
>>> c = q.compile()
>>> c.params
{'lim': None}

safe parameter bindings in sqlalchemy filter

There is .params() on query. Try this:

query = s.query(account).filter(
    "coordinate <@> point(:lat, :long_) < :dist").params(
        lat=lat, long_=long_, dist=distance)

And there is the documentation on it.

Note: I renamed your long param, because there is alread a __builtin__ named long (long int) in python, it's good practice to not overwrite already used words for obvious reasons.

SQLAlchemy raw SQL parameter substitution with an IN clause

This is an unusual format supported only by some DBAPIs, in that it renders a tuple of items as individual SQL expressions, including that it renders the comma and such in between parameters, so a statement like execute("select * from table where value in %s", (somelist, )) expands out at the database level into select * from table where value in (1, 2, 3).

SQLAlchemy is not expecting this format - it already does some inspection of the incoming parameters as it is concerned with routing the parameters into either the DBAPI execute() or executemany() methods, and also accepts a few different styles, and the outcome of this conversion is that the tuple here gets flattened out. You can sneak your tuple past this parsing by adding one more tuple:

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

with engine.connect() as conn:
    trans = conn.begin()

    conn.execute("create table test (data integer)")
    conn.execute(
            "insert into test (data) values (%s)",
            [(1, ), (2, ), (3, ), (4, ), (5, )]
        )

    result = conn.execute(
                "select * from test where data in %s",
                (
                    ((1, 2, 3),),
                )
            )

    print result.fetchall()

The above style only works for some DBAPIs. A quick test confirms it works for psycopg2 and MySQLdb, but not on sqlite3. It has more to do with the underlying system which the DBAPI uses to send bound parameters to the database; psycopg2 and MySQLdb both do Python string interpolation and their own escaping, but systems like cx_oracle will pass the parameters individually to OCI, so this kind of thing wouldn't work in that case.

SQLAlchemy of course offers the in_() operator when using SQL expression constructs but this doesn't apply to straight strings.

---

Related Topics