Syntax error when trying to insert record into posgtresql using pg8000 driver

Question

When I am trying to insert a record into Postgresql I am getting below error.

pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near ","', 'P': '345', 'F': 'scan.l', 'L': '1150', 'R': 'scanner_yyerror'}

Below is my python code snippet where I am getting an error:

def insert_data(metadata):
    if type(metadata) == dict: metadata = [metadata]
    print("Metadata = ", metadata)
    for detail in metadata:
        detail["dismissed"] = check_if_dismissed(detail["uuid"])
        cols = ",".join(detail.keys())
        print("Cols: ", cols)
        qmarks = ','.join(['?' for s in detail.keys()])
        print("Qmarks: ", qmarks)
        # values = [v for v in detail.values()]
        values = []
        for v in detail.values():
            if isinstance(v, list):
                values.append(json.dumps(v))
            else:
                values.append(v)
        print("Values: ", values)
        insert_statement = "INSERT INTO %s (%s) VALUES (%s);" % (TABLE_NAME, cols, qmarks)
        print("Insert Statement: ", insert_statement)
        conn = get_conn()
        conn.insert_record(insert_statement, str(values))

Can anybody please help me to resolve this error?

Answer 1

pg8000 uses "name" style placeholders for values

SELECT * FROM tbl WHERE name = :name

which are passed to the connection's run method as keyword arguments. It's not clear what conn.insert_record is - I don't see it in pg8000's README - but to build a similar statement using pg8000 you could do this:

import pg8000.native

DDL = """CREATE TABLE IF NOT EXISTS table_name (
  name varchar,
  age integer
)
"""


conn = pg8000.native.Connection(...)
conn.run(DDL)

table = 'table_name'
data = {'name': 'Alice', 'age': 42}
cols = ', '.join(f'"{k}"' for k in data.keys())
vals = ', '.join(f':{k}' for k in data.keys())
stmt = f"""INSERT INTO "{table}" ({cols}) VALUES ({vals})"""
conn.run(stmt, **data)
conn.close()