CODEWITHSUNDEEP
pythonamazon-redshiftpsycopg2
scott martin
scott martin

Reputation: 1293

Psycopg2 - Passing variable in the where clause

I am trying to run a SQL script in Python where I am passing a variable in the where clause as below:

cursor.execute(f"""select * from table where type = variable_value""")

In the above query, variable_value has the value that I am trying to use in the where clause. I am however getting an error psycopg2.errors.UndefinedColumn: column "variable_value" does not exist in table

Upvotes: 1

Views: 2975

Answers (1)

botchniaque
botchniaque

Reputation: 5104

As per psycopg2 documentation the execute function takes variables as an extra parameter. 

cursor.execute("""select * from table where type = %(value)s """, {"value": variable_value})

More examples in psycopg2 user manual..

Also please read carefully the section about SQL injection - the gist is, you should not quote parameters in your query, the execute function will take care of that to prevent the injection of harmful SQL.

Also to explain the error you are getting - the query you're sending is comparing two identifiers (type and variable_value). The table does not contain variable_value column, hence the error.

I believe, you intended to use string interpolation to construct the query, but you forgot the {}. It would work like this:

cursor.execute(f"""select * from table where type = '{variable_value}'""")

⚠️ but because of previously mentioned SQL injection, it is not a recommended way!.

Upvotes: 4

Related Questions