SQL Alchemy with PG8000 exception in IN query: invalid input syntax for type integer: "{2,3,5,6,7}"

Question

cursor.execute(text("SELECT * FROM pnl WHERE type IN (:types)"))

Works.

The Following tests:

cursor.execute(text("SELECT * FROM pnl WHERE type IN (:types)"), types=[2, 3, 5, 6, 7])
cursor.execute(text("SELECT * FROM pnl WHERE type IN (:types)"), types=tuple([2, 3, 5, 6, 7]))
cursor.execute(text("SELECT * FROM pnl WHERE type IN (:types)"), types=list([2, 3, 5, 6, 7]))

Throws exception:

(Background on this error at: https://sqlalche.me/e/14/f405) - Traceback (most recent call last):
  File "/Git/argus-periodic/venv/lib/python3.9/site-packages/pg8000/legacy.py", line 252, in execute
    self._context = self._c.execute_unnamed(
  File "/Git/argus-periodic/venv/lib/python3.9/site-packages/pg8000/core.py", line 649, in execute_unnamed
    self.handle_messages(context)
  File "/Git/argus-periodic/venv/lib/python3.9/site-packages/pg8000/core.py", line 767, in handle_messages
    raise self.error
pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '22P02', 'M': 'invalid input syntax for type integer: "{2,3,5,6,7}"', 'F': 'numutils.c', 'L': '323', 'R': 'pg_strtoint32'}

So how can I pass a list to use in the IN query for sql alchemy.

[SQL: SELECT * FROM pnl WHERE type IN %s] [parameters: ((2, 3, 5, 6, 7),)] ... pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "$1"', 'P': '33', 'F': 'scan.l', 'L': '1180', 'R': 'scanner_yyerror' Looks like it comes a trailing empty parameter? — Andreas, Nov 29 '21 at 13:59

snakecharmerb · Answer 1 · 2023-06-04T11:34:27.723

There are idiosyncrasies in both pg8000's and SQLAlchemy's handling of IN clauses that need to be considered here.

pg8000 requires parameterised IN queries to take this form

SELECT col FROM tbl WHERE col IN (SELECT(unnest(CAST(:params) AS integer[])))

So the code would look like this:

values = {'types': [1, 2, 5]}
q = sa.text('select id from users where id in (select(unnest(cast(:types as integer[]))))')

with engine.connect() as conn:
    result = conn.execute(q, values)
    # Do stuff with result

SQLAlchemy on the other hand, requires that we mark parameters as expanding when used as the target of an IN clause.

So the final SQLAlchemy code, would look like this (which I think is more idiomatic):

values = {'types': [2, 3, 5, 6, 7]}
q = sa.text('select * from pnl where type in :types')
q = q.bindparams(sa.bindparam('types', expanding=True))

with engine.connect() as conn:
    result = conn.execute(q, values)
    # Do stuff with result.

The difference between the two statements is caused by the way the parameters are passed. The "native" pg8000 statement is passed a row object ((1, 2, 5),) which must be unnested etc. The SQLAlchemy version receives individual values: (1, 2, 5).

score 1 · Answer 2 · answered Jun 04 '23 at 14:35

Further to @snakecharmerb 's answer, you can also avoid having to remember the idiosyncrasies of each driver (e.g., pg8000) by letting SQLAlchemy build the SELECT statement for you:

import sqlalchemy as sa

# …

pnl = sa.Table("pnl", sa.MetaData(), autoload_with=engine)

engine.echo = True
with engine.begin() as conn:
    types = [2, 3, 5, 6, 7]
    stmt = (
        sa.select(sa.text("*")).select_from(pnl).where(pnl.c.type.in_(types))
    )
    results = conn.execute(stmt).all()

"""SQL emitted:
SELECT * 
FROM pnl 
WHERE pnl.type IN (%s::INTEGER, %s::INTEGER, %s::INTEGER, %s::INTEGER, %s::INTEGER)
[generated in 0.00042s] (2, 3, 5, 6, 7)
"""

SQL Alchemy with PG8000 exception in IN query: invalid input syntax for type integer: "{2,3,5,6,7}"

2 Answers2