CODEWITHSUNDEEP
pythonpostgresql
이창연
이창연

Reputation: 27

Syntax error when creating table using pycopg2

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?

Upvotes: 0

Views: 435

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19570

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)

Upvotes: 1

Related Questions