Search code examples
pythonpostgresqlpsycopg2

sql.Identifier without quoting

I did a web search for "sql.Identifier without quoting" and this post was relevant only. It advices to use .format(sql.Identifier.

However this method adds double quotes to identifiers and cannot as far as I can tell be used for identifiers made w/out quotes in PostgreSQL. As I've read in here expert advice not to quote identifiers in Postgres.

I do not see an option in sql.Identifier to skip quoting in the document and alternative methods in sql module of psycopg2. How can I use PostgreSQL from Python in injection-safe way for unquoted identifiers?

ADDED: my confusion was due to me using "public.abc" for sql.Identifier when as noted in the answer by @klin I should have used two identifiers. After that sorted out I see quoting is used only for case sensitive (and/or where "other" symbols like dot are used).

Solution

  • You should distinguish between two different situations. When you want to use uppercase letters in case-sensitive identifiers you have to use double-quotes. If you use only lowercase letters for identifiers, you can but not have to use double-quotes. Experts usually recommend avoiding the first case. In the second case, the quotes automatically added by psycopg2 are not a problem.

    Note, that public.abc is not an identifier, it is an expression containing two identifiers. Hence you should use it in the way like this:

    sql.SQL("select * from {}.{}").format(sql.Identifier(schema_name), sql.Identifier(table_name))

    or this:

    sql.SQL("select * from {}").format(sql.Identifier(schema_name, table_name))

    as (per the documentation):

    Multiple strings can be passed to the object to represent a qualified name, i.e. a dot-separated sequence of identifiers.