Variables in SQL queries with PyQGIS [closed]

Question

I want to create spatial indexes in all the tables that are included in a schema in PostGIS. The layers remain the same but the schema is everytime different. So I wanted to pass a variable var = schemaname + "l_abluft(geom)" to the SQL query (CREATE INDEX ON test.l_abluft(geom);)

#--Create a Connection to PostGIS
#---------------------------------------------------------------------------
connection = psycopg2.connect (dbname = "xxxxx",
                           user = "xxxxxx",
                           password = "xxxxx",
                           host = "xxxxxx",
                           )
cursor = connection.cursor()

#--Create spatial Indexes
#---------------------------------------------------------------------------
cursor.execute("CREATE INDEX ON test.l_abluft(geom);")
cursor.execute("CREATE INDEX ON test.l_zuluft(geom);")
cursor.execute("CREATE INDEX ON test.rl_isolierung(geom);")
cursor.execute("CREATE INDEX ON test.treppenhaus(geom);")
cursor.execute("CREATE INDEX ON test.waende_saeule_usw(geom);")
connection.commit()
print("Query successful")

I tried the following but I got the error: psycopg2.errors.SyntaxError: syntax error at end of input LINE 1: CREATE INDEX ON "test.l_abluft(geom)"

schemaname = "test"
var = schemaname + ".l_abluft(geom)"

#--Create spatial Indexes
#---------------------------------------------------------------------------
cursor.execute(
    sql.SQL("CREATE INDEX ON {}").format(sql.Identifier(var)))
connection.commit()
print("Query successful")

Answer 1

You can use f-strings (requires Python version >= 3.6) :

#--Create a Connection to PostGIS
#---------------------------------------------------------------------------
connection = psycopg2.connect(dbname = "xxxxx",
                              user = "xxxxxx",
                              password = "xxxxx",
                              host = "xxxxxx",
                             )
cursor = connection.cursor()

#--Create spatial Indexes
#---------------------------------------------------------------------------
my_schema = "test"
cursor.execute(f"CREATE INDEX ON {my_schema}.l_abluft(geom);")
cursor.execute(f"CREATE INDEX ON {my_schema}.l_zuluft(geom);")
cursor.execute(f"CREATE INDEX ON {my_schema}.rl_isolierung(geom);")
cursor.execute(f"CREATE INDEX ON {my_schema}.treppenhaus(geom);")
cursor.execute(f"CREATE INDEX ON {my_schema}.waende_saeule_usw(geom);")
connection.commit()
print("Query successful")

For Python versions < 3.6 use :

settings = {"my_schema": "test"}
cursor.execute("CREATE INDEX ON {my_schema}.l_abluft(geom);".format(**settings)

---

With Python 3.6, it will be cleaner (IMHO) to write code like this :

#--Create a Connection to PostGIS
#---------------------------------------------------------------------------
db_params = {
    "dbname": "xxxxx",
    "user" = "xxxxxx",
    "password" = "xxxxx",
    "host" = "xxxxxx",
}

my_schema = "test"
fields = [
    "l_abluft(geom)",
    "l_zuluft(geom)",
    "rl_isolierung(geom)",
    "treppenhaus(geom)",
]

with psycopg2.connect(**db_params) as conn:
    with conn.cursor() as curs:
        list(map(
            curs.execute,
            [f"CREATE INDEX ON {my_schema}.{fld};" for fld in fields]
        ))

print("Query successful")