Syntax error when creating table using pycopg2

Question

def create_table(self, name: str, coulmn: str):
    """This method creates a table in the session.

    Args:
        name : Name of the table to be created.
        coulmn : Column in the table to be created. 
            Format is "(name data_type condition, name2 data_type2 condition2...)".
    """
    self.cur.execute(
        query=SQL("CREATE TABLE {name} %s;").format(name=Identifier(name)), 
        vars=[coulmn]
    )

This is method source code.

self.postgres.create_table(name="test", coulmn="(id serial PRIMARY KEY, name text)")

This is Test code.

psycopg2.errors.SyntaxError: 오류(error): 구문 오류(SyntaxError), "'(id serial PRIMARY KEY, name text)'" 부근(near)
LINE 1: CREATE TABLE "test" '(id serial PRIMARY KEY, name text)';

Why am I getting a syntax error?

Use Python string formatting for the columns too, the column string is quoted if you pass it as a query param — Iain Shelvington
– Iain Shelvington, Commented Jan 8, 2022 at 15:05
Because you did not follow the documentation here sql. Go back and read it again, hint one error is it is sql.SQL not just SQL. Also you cannot pass multiple columns into one place holder. — Adrian Klaver
– Adrian Klaver, Commented Jan 8, 2022 at 16:44

Adrian Klaver · Accepted Answer · 2022-01-08 23:26:31Z

A first run at this:

import psycopg2
from psycopg2 import sql 

name = 'test'
columns = [('id', ' serial PRIMARY KEY,'), ('name', ' text')]

composed_cols = []

for col in columns:
    composed_cols.append(sql.Composed([sql.Identifier(col[0]), sql.SQL(col[1])]))

[Composed([Identifier('id'), SQL(' serial PRIMARY KEY,')]),
 Composed([Identifier('name'), SQL(' text')])]

qry = sql.SQL("CREATE TABLE {name} ({} {})").format(sql.SQL('').join(composed_cols[0]), sql.SQL('').join(composed_cols[1]), name=sql.Identifier(name)) 

print(qry.as_string(con))                                                                                                                                                 
CREATE TABLE "test" ("id" serial PRIMARY KEY, "name" text)

cur.execute(qry) 
con.commit()

\d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 name   | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Basically break the column definition into two components, the name/identifier and the type/constraint portion. Then create a list that has these elements composed into the correct sql objects. Build the query string by joining the elements of the list into the {} placeholders for the column name and type/constraint portions respectively. Use named placeholder {name} for the table name. The portion that needs to be paid attention to is the sql.SQL as that is a literal string and if it is coming from an outside source it would need to be validated.

UPDATE

Realized this could be simplified as:

col_str = '(id serial PRIMARY KEY, name text)'
qry = sql.SQL("CREATE TABLE {name} {cols} ").format(cols=sql.SQL(col_str), name=sql.Identifier(name)) 

print(qry.as_string(con))                                                                                                                                                 
CREATE TABLE "test" (id serial PRIMARY KEY, name text)

Collectives™ on Stack Overflow

Syntax error when creating table using pycopg2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related