Psycopg2 formatting error when using SQL module-CodePudding

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',))

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

CodePudding user response：

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)