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.

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:

# ----- 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:

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

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

 


Copyright 2024 © pythontic.com