upsert into Postgres dealing with quotations

Question

I'm trying to write a function that upserts a list of dicts into postgres, but I'm having difficulty dealing with quotation marks.

I have the two functions below, one to generate the insert statement and one to execute.

import psycopg2 as pg
import string

def create_insert_sql(record, table, on_key):
    """creates the sql insert statement"""

    columns = list(record.keys())
    insert_columns = ','.join([str(x) for x in columns])
    columns.remove(on_key)
    update_set = ','.join([str(x) for x in columns])
    exclude_set = ','.join(['EXCLUDED.' + str(x) for x in columns])

    values = ','.join(['\'' + str(x) + '\'' for x in record.values()])

    insert_sql_statement = """
   INSERT INTO {} ({})
        VALUES ({})
        ON CONFLICT ({})
        DO UPDATE SET
            ({})
            = ({}) ;
    """.format(table, insert_columns, values, on_key, update_set, exclude_set)

    return insert_sql_statement

def upsert_into_pg(dataset, table, on_key):
    """Given a list of dicts, upserts them into a table with on_key as conflict"""

    conn = pg.connect(user=XXXX,
                      password=XXXX,
                      host='127.0.0.1',
                      port=3306,
                      database='XXXX')

    cur = conn.cursor()

    try:
        for record in dataset:
            cur.execute(create_insert_sql(record, table, on_key))
    except Exception as e:
        conn.rollback()
        print(e)
    else:
        conn.commit()
        print('upsert success')
    finally:
        cur.close()
        conn.close()

An example of the error

test = [
    {'a': 'structure', 'b': 'l\'iv,id', 'c': 'doll', 'd': '42'},
    {'a': '1', 'b': 'shoe', 'c': 'broke', 'd': '42'},
    {'a': 'abc', 'b': 'proc', 'c': 'moe', 'd': '42'}
]

upsert_into_pg(test, 'testing', 'a')

Returning

syntax error at or near "iv"
LINE 3:         VALUES ('structure','l'iv,id','doll','42')

Any help is greatly appreciated.

Possible duplicate of [Escape input data for postgres](https://stackoverflow.com/questions/18292019/escape-input-data-for-postgres) — metatoaster, Jun 19 '18 at 14:15
Also [Parameterized queries with psycopg2 / Python DB-API and PostgreSQL](https://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql) — metatoaster, Jun 19 '18 at 14:17
If using parameters doesn't work for you (don't know about arrays in Python), you can double all single quotes in the string literals. — Laurenz Albe, Jun 19 '18 at 14:20
And this question [Insert text with single quotes in PostgreSQL](https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql) has a lot of answers on topic — gseva, Jun 19 '18 at 14:21

score 0 · Answer 1 · answered Jun 19 '18 at 15:22

I was able to solve it with the below changes.

def upsert(cur, record, table, on_key):
    """creates the sql insert statement"""

    cols = list(record.keys())
    cols_set = ','.join([str(x) for x in cols])
    vals = [record[x] for x in cols]

    vals_str_list = ["%s"] * len(vals)
    vals_str = ", ".join(vals_str_list)

    cols.remove(on_key)
    update_set = ','.join([str(x) for x in cols])
    exclude_set = ','.join(['EXCLUDED.' + str(x) for x in cols])

    cur.execute("""INSERT INTO {table} ({cols}) 
                            VALUES ({vals_str}) 
                            ON CONFLICT ({on_key}) 
                            DO UPDATE SET ({update_set}) = ({exclude_set})""".format(table=table, cols=cols_set, vals_str=vals_str, on_key=on_key, update_set=update_set, exclude_set=exclude_set),
                   vals)

upsert into Postgres dealing with quotations

1 Answers1