Psycopg2 formatting error when using SQL module

Question

I imagine this will be an easy one for somebody but I can't figure it out...

I am developing a SQL query dynamically following the instructions here. After creating postgresql connection and cursor object, I use the psycopg2 sql module to create a query with parameters as such:

query = sql.SQL("SELECT * FROM {t_name} WHERE {col} in %s;".format(
    t_name=sql.Identifier(table_name),
    col=sql.Identifier(fips_col)))

This works fine (as far as I know) with this result:

SQL("SELECT * FROM Identifier('cb_tracts') WHERE Identifier('st_cnty_fips') in %s;")

I then try to execute with an argument (or list of arguments):

cursor.execute(query, ('06037',))

or

cursor.execute(query, (['06037'],))

But I am getting this error:

psycopg2.errors.SyntaxError: syntax error at or near "'06073'"
LINE 1: ...er('cb_tracts') WHERE Identifier('st_cnty_fips') in '06073';

and this error, respectively:

psycopg2.errors.SyntaxError: syntax error at or near "ARRAY"
LINE 1: ...('cb_tracts') WHERE Identifier('st_cnty_fips') in ARRAY['060...

Richard Huxton · Accepted Answer · 2021-11-30 23:28:52Z

3

The SQL IN (...) is actually a syntactic construction rather than a function or anything like that. Which means that it takes a comma-separated list of literal values rather than a single value that is a list (if you can see the difference).

This means the value-binding that almost every database driver uses can't work with IN (...) even though everybody thinks it should.

The way you would express a list as a single value in PostgreSQL is an array. The way to check for membership of an array is with

... my_column = ANY(%s)

answered Nov 30, 2021 at 23:28

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

5 Comments

jtam Over a year ago

Thanks! Hmmm...I thought the purpose of psycopg2 was to format queries correctly? So, what do I need to do to format this query correctly?

Richard Huxton Over a year ago

What I said. Express it as an array and use ANY

Adrian Klaver Over a year ago

See here list/array for the docs confirmation of what @RichardHuxton is telling you. Also from Postgres docs Functions for the different uses of IN; Subquery and Row/Array.

Adrian Klaver Over a year ago

If you want to work with just one value then ...Identifier('st_cnty_fips') = '06073'.

jtam Over a year ago

Ah, sorry. I misunderstood. Thanks for your help!

Collectives™ on Stack Overflow

Psycopg2 formatting error when using SQL module

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related