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