Insert data into PostgreSQL using Python and Psycopg2

Overview:

The INSERT statement is one of the essential DML statements available as per SQL standards in any Relational Database Management System.
Insert is one of the CRUD operations - Create/Read/Update and Delete.
PostgreSQL is a popular open-source Relational Database Management System.
Once a table is created on an existing PostgreSQL database, any PostgreSQL user with required privileges can add rows to the table using insert statement.
After one or more rows are added using INSERT statement, the rows of the PostgreSQL table can be queried using the SELECT statement.

Psycopg2 is the Python client for the PostgreSQL Relational Database Management System.
The Python example program given below connects to a PostgreSQL database and inserts two rows of data into a table.
Through the connect() function of Psycopg2 module, a connection to a PostgreSQL database can be obtained.
A cursor object is obtained from the connection and is used to execute the INSERT statement.
After inserting one or more rows into the PostgreSQL table, a SELECT statement is executed using the database cursor, followed by calling one of the fetch() methods.

# ----- Example Python program to insert data into a PostgreSQL database table

import psycopg2

# Open a DB session

dbSession = psycopg2.connect("dbname='test' user='test' password='test'");

# Open a database cursor

dbCursor = dbSession.cursor();

# SQL statement to create a table

sqlCreateTable = "CREATE TABLE Cities(id bigint, cityname varchar(128), latitude numeric, longitude numeric);";

# Execute CREATE TABLE command

dbCursor.execute(sqlCreateTable);

# Insert statements

sqlInsertRow1 = "INSERT INTO Cities values(1, 'New York City', 40.73, -73.93)";

sqlInsertRow2 = "INSERT INTO Cities values(2, 'San Francisco', 37.733, -122.446)";

# Insert statement

dbCursor.execute(sqlInsertRow1);

dbCursor.execute(sqlInsertRow2);

# Select statement

sqlSelect = "select * from test.Cities";

dbCursor.execute(sqlSelect);

rows = dbCursor.fetchall();

# Print rows

for row in rows:

print(row);

# It is essential to commit the transaction else it will be rollbacked

dbSession.commit();

# Close the session and free up the resources
dbSession.close();

(1, 'New York City', Decimal('40.73'), Decimal('-73.93'))

(2, 'San Francisco', Decimal('37.733'), Decimal('-122.446'))