How to remove the quotes from a string for SQL query in Python?

Question

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.

Answer 1

from psycopg2.extensions import AsIs
cur.execute("INSERT INTO %s VALUES(...);", AsIs(database_name))

http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.AsIs

BTW that is not a database name, it is a table name.

Answer 2

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"

Answer 3

Note: I haven't use psycopg2, this is based on what I know from similar database libraries.

A table name is an identifier and they get quoted and escaped differently than values. I believe you should use psycopg2.extensions.quote_ident(str, scope) to quote and escape it. I believe it uses the PostgreSQL function PQescapeIdentifier().

PQescapeIdentifier escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved.

Then it will be quoted and escaped and can be safely added to the SQL string using normal string operations without risking a SQL injection attack, or using AsIs(quote_ident(database_name)) as a value to .execute.