[Tutor] how to do this using psycopg2

[Sibylle Koczian]

Am 18.10.2019 um 20:38 schrieb Eggo why:
> Hi all,
>       I have following python code  but when perform execute got error due to signal quote.  How can I prepare the query without the signal quote?
>
>
> sql = ("alter table %s rename to %s")
> print(cur.mogrify(sql, (str_tab, tab_b)))
> cur.execute(sql, (str_tab, tab_b))
>
>
You are passing the table names as if they were query values, but they
aren't. From the psycopg2 documentation
(http://initd.org/psycopg/docs/usage.html), "Passing parameters to SQL
queries", last paragraph:

"Only query values should be bound via this method: it shouldn’t be used
to merge table or field names to the query (Psycopg will try quoting the
table name as a string value, generating invalid SQL). If you need to
generate dynamically SQL queries (for instance choosing dynamically a
table name) you can use the facilities provided by the psycopg2.sql module:

 >>> cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # WRONG
 >>> cur.execute(                                                # correct
...     SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
...     (10,))
"

So your query should work if you change it like this:

import psycopg2
from psycopg2 import sql
ddlquery = "alter table {0} rename to {1}")
# create connection and cursor
cur.execute(sql.SQL(ddlquery).format(sql.Identifier(str_tab),
                                      sql.Identifier(tab_b)))

HTH
Sibylle