Insert Data Into PostgreSQL Using Python And Psycopg2


  • 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.

Inserting rows into a PostgreSQL table using Python:

  • 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



# 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




# Select statement

sqlSelect = "select * from test.Cities";


rows = dbCursor.fetchall();


# Print rows

for row in rows:


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


# Close the session and free up the resources   



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

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


Copyright 2022 ©