I have that error

File "/home/darek/PycharmProjects/Small_programs/Analiza_danych_nauka/db_manager.py", line 52, in _execute self._cur.execute(query) psycopg2.errors.SyntaxError: syntax error at end of input LINE 1: ... EXISTS "companys" ("id SERIAL PRIMARY KEY", "name VARCHAR")

I start this code like

db.create_table('companys', ['id SERIAL PRIMARY KEY', 'name VARCHAR'])

next

    def create_table(self, table, columns):
    create_query = sql.SQL("CREATE TABLE IF NOT EXISTS {} ({})").format(
        sql.Identifier(table),
        sql.SQL(', ').join(map(sql.Identifier, columns))
    )
    self._execute(create_query)

and line from error msg

    def connect(self):
    try:
        conn = psycopg2.connect(
            user=self.user,
            password=self.password,
            host=self.host,
            port=self.port,
            dbname=self.dbname)
        # cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur = conn.cursor()
        pp(
            '------------------------------------------------------------'
            '\n-# PostgreSQL connection & transaction is ACTIVE\n'
        )
    except (Exception, psycopg2.Error) as error:
        print(error, sep='\n')
        sys.exit()
    else:
        self._conn = conn
        self._cur = cur
        self._counter = 0

def _check_connection(self):
    try:
        self._conn
    except AttributeError:
        print('ERROR: NOT Connected to Database')
        sys.exit()

def _execute(self, query, Placeholder_value=None):
    self._check_connection()
    if Placeholder_value == None or None in Placeholder_value:
        self._cur.execute(query) # 52 line from error msg
        print('-# '   query.as_string(self._conn)   ';\n')
    else:
        self._cur.execute(query, Placeholder_value)
        print('-# '   query.as_string(self._conn) % Placeholder_value   ';\n')

CodePudding user response：

After checking SQL class of psycopg2 module as Adrian stated you can provide column names. Because of compasable subclass it parses columns names between doubles quotes(single quote if you use "Literal"). By the way, in order to print the query we should have used as_string method :). Here:

import psycopg2
from psycopg2 import sql
conn_string = "host='localhost' dbname='postgres' user='postgres' password='123123'"
cursor = conn.cursor()
query = sql.SQL("CREATE TABLE IF NOT EXISTS {} ({})").format(sql.Identifier('companys'), sql.SQL(', ').join(map(sql.Identifier, ['id SERIAL PRIMARY KEY', 'name VARCHAR'])))
print(query.as_string(conn))
CREATE TABLE IF NOT EXISTS "companys" ("id SERIAL PRIMARY KEY", "name VARCHAR")
cursor.execute(query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at end of input
LINE 1: ... EXISTS "companys" ("id SERIAL PRIMARY KEY", "name VARCHAR")
                                                                       ^


s1 = sql.Literal('id SERIAL PRIMARY KEY')
s2 = sql.Literal('name VARCHAR')
query = sql.SQL("CREATE TABLE IF NOT EXISTS {} ({})").format(sql.Identifier('companys'), sql.SQL(', ').join([s1, s2]))
print(query.as_string(conn))
CREATE TABLE IF NOT EXISTS "companys" ('id SERIAL PRIMARY KEY', 'name VARCHAR')
cursor.execute(query)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at or near "'id SERIAL PRIMARY KEY'"
LINE 1: CREATE TABLE IF NOT EXISTS "companys" ('id SERIAL PRIMARY KE...

I think, "psycopg2.sql – SQL string" generally are being used to prevent SQL injection in any case of data retrieve operation. You can try to convert you own environment to fit this solution but it is a tailored solution. Because this is a DDL operation you can safely use:

cursor.execute(query)

CodePudding user response：

A suggestion. NOTE: This uses sql.SQL() which does not escape strings and therefore is a SQL injection risk. Make sure you validate the input. It also involves a change in the column listing:

col_list = [
{"col_name": "id", "col_type": "serial", "col_constraint": "primary key"},
{"col_name": "name", "col_type": "varchar", "col_constraint": None}
]

composed_list = []
for col in col_list:
    col_cmp = []
    col_cmp.append(sql.Identifier(col["col_name"]))
    col_cmp.append(sql.SQL(" "))
    col_cmp.append(sql.SQL(col["col_type"]))
    if col.get("col_constraint"):
        col_cmp.append(sql.SQL(" ")) 
        col_cmp.append(sql.SQL(col["col_constraint"])) 
    composed_list.append(sql.Composed(col_cmp))

base_sql = sql.SQL(
"CREATE TABLE IF NOT EXISTS {table} ({fields})").\
format(table=sql.Identifier("companys"), 
fields=sql.SQL(",").join(composed_list)
)

print(base_sql.as_string(con))                                                                                                                                           
CREATE TABLE IF NOT EXISTS "companys" ("id" serial primary key,"name" varchar)

cur.execute(base_sql)                                                                                                                                                    
con.commit()

--psql
 \d companys
                                 Table "public.companys"
 Column |       Type        | Collation | Nullable |               Default                
-------- ------------------- ----------- ---------- --------------------------------------
 id     | integer           |           | not null | nextval('companys_id_seq'::regclass)
 name   | character varying |           |          | 
Indexes:
    "companys_pkey" PRIMARY KEY, btree (id)