Insert data into PostgreSQL using Python and Psycopg2

Rows can be inserted into a PostgreSQL database table using the execute() method on a cursor object. A database cursor can be opened on a connection instance. The example Python program creates a table and inserts two rows of data and displays them back by querying.

PostgreSQL Updated 3 Jul 2019 2 min read

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.

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

Output

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

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