Query String Composition in Psycopg2-CodePudding

I am trying to run a SQL "SELECT" query in Postgres from Python using Psycopg2. I am trying to compose the query string as below, but getting error message, using psycopg2 version 2.9.

from psycopg2 import sql

tablename = "mytab"
schema = "public"
query = sql.SQL("SELECT table_name from information_schema.tables where table_name = {tablename} and table_schema = {schema};")
query = query.format(tablename=sql.Identifier(tablename), schema=sql.Identifier(schema))
cursor.execute(query)
result = cursor.fetchone()[0]

Error:

psycopg2.error.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Can someone please help. Thanks.

CodePudding user response：

In the (a bit strange) query

select table_name 
from information_schema.tables 
where table_name = 'mytab'
and table_schema = 'public';

'mytab' and 'public' are literals, not identifiers. For comparison, mytab is an identifier here:

select *
from mytab;

Thus your format statement should look like this:

query = query.format(tablename=sql.Literal(tablename), schema=sql.Literal(schema))

Note that the quoted error message is somewhat misleading as it is about executing a query other than what is shown in the question.