insert record into table postgresql
try:
connection = psycopg2.connect(**postgres_credentials())
cursor = connection.cursor()
records = [(1,'FOO'),(2,'SPAM')]
placeholders = ','.join(['%s']*len(records)) # => '%s,%s'
sql = f"""
INSERT INTO schema.table(id, field)
VALUES {placeholders}
"""
# Mogrify helpful to debug command sent to DB bc transforms command into human readable form.
# It's not necessary. Could just use executemany, but slower & harder to debug command as SO suggests.
insert_statement = cursor.mogrify(sql, records)
# print(insert_statement.decode('utf-8'))
cursor.execute(insert_statement)
# cursor.executemany(sql, records) # SLOW bc executes and commits each record one at a time.
# print(cursor.mogrify(sql, records).decode('utf-8'))
connection.commit()
except psycopg2.DatabaseError:
raise
finally:
if not connection.closed:
cursor.close()
connection.close()