How to remove the quotes from a string for SQL query in Python?
postgresql, psycopg2, python, sql
Solution
The structural components of an SQL query such as table and field names cannot be parameterized as you attempt in second argument of `cursor.execute(query, params)`. Only numeric/literal data values can be parameterized.
Consider interpolating the database_name variable into the SQL query string but do so safely with psycopg2's `sqlIdentifier()` with `str.format`:
from psycopg2 import sql
...
cur.execute(sql.SQL('INSERT INTO {} VALUES(...)').format(sql.Identifier(database_name)))
Valid parameterizaiton in your case would be to bind the data values passed in the `VALUES(...)` in append query such as `VALUES(%s, %s, %s)`. Alternatively in other queries:
"SELECT %s AS NewColumn..."
"...WHERE fieldname = %s OR otherfield IN (%s, %s, %s)"
"...HAVING Max(NumColumn) >= %s"
Problem
I have a dictionary of database names. I take a name from the dictionary ``` database_name = database_dict[i] ``` lets say the value for database_name is 'foo' Using Psycopg2 I am executing a statement: ``` cur.execute("INSERT INTO %s VALUES(...);", database_name) ``` I get A syntax error at foo, because it should be "INSERT INTO foo VALUES" not "INSERT INTO 'foo' VALUES" Any advice how to pass in a string value for the name of the table and removing the single quotes? Should I place an escape character inside my database dictionary values? EDIT: Something closer is here: How do I remove single quotes from a table in postgresql? but I could not get it to work using REMOVE. It gave a syntax error at the single quote inside the remove statement.