CODEWITHSUNDEEP
postgresqlpsycopg2
sds
sds

Reputation: 60014

How to make psycopg2 emit no quotes?

I want to create a table from Pytnon:

import psycopg2  as pg
from psycopg2 import sql

conn = pg.connect("dbname=test user=test")
table_name = "testDB"
column_name = "mykey"
column_type = "bigint"
cu = conn.cursor()
cu.execute(sql.SQL("CREATE TABLE {t} ({c} {y})").format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name),
    y=sql.Literal(column_type)))

Alas, this emits CREATE TABLE "testDB" ("mykey" 'bigint') which fails with a

psycopg2.ProgrammingError: syntax error at or near "'bigint'"

Of course, I can do something like

cu.execute(sql.SQL("CREATE TABLE {t} ({c} %s)" % (column_name)).format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name)))

but I suspect there is a more elegant (and secure!) solution.

PS. See also How to make psycopg2 emit nested quotes?

Upvotes: 1

Views: 124

Answers (1)

klin
klin

Reputation: 121604

There is an example in the documentation how to build a query text with a placeholder. Use psycopg2.extensions.AsIs(object) for column_type:

query = sql.SQL("CREATE TABLE {t} ({c} %s)").format(
    t=sql.Identifier(table_name),
    c=sql.Identifier(column_name)).as_string(cu)
    
cu.execute(query, [AsIs(column_type)])

Upvotes: 1

Related Questions