CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Azrul
Azrul

Reputation: 1

How use psycopg2.sql.Identifier as a single quotes?

I get error when creating function to create table using postgresql database

def create_table(tb_names, cursor):

cursor.execute(sql.SQL("DROP TABLE IF EXISTS {}").format(sql.Identifier(tb_names)))
cursor.execute(sql.SQL("""CREATE TABLE {}(
                        id SERIAL,
                        PRIMARY KEY (id))
                """).format(sql.Identifier(tb_names)))
cursor.execute(sql.SQL("SELECT AddGeometryColumn({}, 'geom', 3375, 'POLYGON', 2)").format(sql.Identifier(tb_names)))
cursor.execute(sql.SQL("CREATE INDEX geomIndex ON {} USING GIST 

(geom)").format(sql.Identifier(tb_names)))

create_table(tb_names="test_data1", cursor=cur)

error:
psycopg2.errors.UndefinedColumn: column "test_data1" does not exist
LINE 1: SELECT AddGeometryColumn("test_data1", 'geom', 3375, 'POLYGO...

Any idea how to solve it?

How to pass variable as a single quote?

Sorry for my bad english

Upvotes: 0

Views: 1826

Answers (2)

Azrul
Azrul

Reputation: 1

Answer:

cursor.execute("SELECT AddGeometryColumn(%s, 'geom', 3375, 'POLYGON', 2)", [tb_names])

Just learn how to passing a regular parameter using my variable. :)

Upvotes: 0

Masklinn
Masklinn

Reputation: 42292

... pass it as a regular parameter to execute?

The point of sql.Identifier is to apply identifier quoting rules when the string is an identitfier to inject in the query. That is how it's used in the first, second and last query but in the third it looks to be a regular parameter, why are you quoting and injecting it as an identifier?

Upvotes: 2

Related Questions