Example 1: return insert results in POSTGRESQL
# to select specific columns
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id, firstname;
# to return every column
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING *;
Example 2: insert postgres
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
Example 3: posgres insert
INSERT INTO films
(code, title, did, date_prod, kind)
VALUES
('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
Example 4: postgresql, Rows, INSERT INTO
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING *;Code language: SQL (Structured Query Language) (sql)
Example 5: insert record into table postgresql
try:
connection = psycopg2.connect(**postgres_credentials())
cursor = connection.cursor()
order_types = [(1,'SELL'),(2,'BUY')]
placeholders = ','.join(['%s']*len(order_types)) # => '%s,%s'
sql = f"""
INSERT INTO collection.instruction(id, side)
VALUES {placeholders}
"""
# Mogrify transforms command into human readable form so we can print and debug command sent to DB server.
# It is not necessary. We could just use executemany, but it's slower and also harder to debug the command.
insert_statement = cursor.mogrify(sql, order_types)
# prints the exact command that would be sent to server.
print(insert_statement.decode('utf-8'))
cursor.execute(insert_statement)
# cursor.executemany(sql, order_types) # SLOW bc executes and commits each record one at a time!
# prints the exact command that would be sent to server.
# print(cursor.mogrify(sql, order_types).decode('utf-8'))
connection.commit()
print('DB entries committed.')
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if connection:
cursor.close()
connection.close()