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()
