insert record into table postgresql

1
2            
3                
4            
5         INSERT INTO table_name(column1, column2, …)
6VALUES (value1, value2, …)
7RETURNING *;Code language: SQL (Structured Query Language) (sql)

1    try:
2        connection = psycopg2.connect(**postgres_credentials())
3        cursor = connection.cursor()
4
5        records = [(1,'FOO'),(2,'SPAM')]
6
7        placeholders = ','.join(['%s']*len(records)) # => '%s,%s'
8        sql = f"""
9            INSERT INTO schema.table(id, field)
10            VALUES {placeholders}
11        """
12                       
13        # Mogrify helpful to debug command sent to DB bc transforms command into human readable form. 
14        # It's not necessary. Could just use executemany, but slower & harder to debug command as SO suggests.
15        insert_statement = cursor.mogrify(sql, records)
16        # print(insert_statement.decode('utf-8'))
17
18        cursor.execute(insert_statement)
19        # cursor.executemany(sql, records) # SLOW bc executes and commits each record one at a time.
20        # print(cursor.mogrify(sql, records).decode('utf-8'))
21
22        connection.commit()
23
24    except psycopg2.DatabaseError:
25        raise
26    finally:
27        if not connection.closed:
28            cursor.close()
29            connection.close()
30