CODEWITHSUNDEEP
pythonsqlalchemypg8000
Andreas
Andreas

Reputation: 569

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

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.

Upvotes: 2

Views: 1790

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123839

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)
"""

Upvotes: 1

snakecharmerb
snakecharmerb

Reputation: 55963

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).

Upvotes: 1

Related Questions