Overview

PostgreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language and Python is a high level, interpreted general purpose language. This article covers basic integration of Python with PostgreSQL apparently how we can establish connection with PostgreSQL database using Python program and perform CRUD operations on it.

The examples have been tested to run with the following software setup:

• PostgreSQL 10 or above
• PgAdmin4(GUI tool of PostgreSQL)
• Python 3.7
• PyCharm Community Edition 2019.2.3 (Python IDE)
• PostgreSQL Python Adapter (In this example we are going to use Psycopg)

Software Setup

Note - This section can be skipped if the required software are already there in the system.

PostgreSQL with PgAdmin4 - The following tutorial covers the installation of  PostgreSQL with PgAdmin4 on windows:  https://www.sqlservercentral.com/articles/installing-postgresql-11-on-windows-step-by-step-instruction

The following tutorial covers the installation of  PostgreSQL with PgAdmin4 on Linux: https://www.sqlservercentral.com/articles/postgresql-jdbc-tutorial-on-linux

Python - The Python engine can be downloaded from here: https://www.python.org/downloads/

PyCharm Community Edition 2019.2.3 - PyCharm developed by JetBrains is an IDE for Python programming language. The installer can be downloaded from here: https://www.jetbrains.com/pycharm/download/other.html

Psycopg plugin installation in Pycharm - Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Given below is its step by step installation process:

Navigate to File->Settings

Project->ProjectInterpreter

Note: Create a new project if one doesn't exist

Search and select 'Psycopg2' to install

Wait for 'Package Psycopg2 installed successfully' message to show up

Database Integration Programs

Now that we have all the required software in place, let us get started with the programming.  We will create a new Python file to perform each of the following operations described below. To create a new File within the existing project navigate to File->Project->PythonFile:

Connect To An Existing Database

Here the import statements make the module psycopg2 available to the python interpreter. The next line of code establishes a connection with the PostgreSQL database using its name, username, password, host and port. Next we print an output statement to mark the end of execution.

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
print("Database Connected....")

Create A New Table

In the above section we have already seen the use of the import statement and how to establish connection with the database. In addition to this, here we use an instance of the cursor class to perform the database operation. Cursors are created by the connection. cursor() method and they remain active for the entire database session until closed.

The cur.execute()  statement takes the SQL as parameter and creates the table on execution. Finally conn.commit() and conn.close() statements are executed to commit the DML and close the connection.

import psycopg2
conn = psycopg2.connect(database="student", user="postgres", password="postgres", host="127.0.0.1", port="5432")
print("Database Connected....")
cur = conn.cursor()
cur.execute("CREATE TABLE student(id serial PRIMARY KEY, name CHAR(50), roll integer);")
print("Table Created....")
conn.commit()
conn.close()

Verify our table in pgAdmin.

Insert Record(s) into a Table

As seen in the previous examples, we import the module, establish the connection to the database, and open up a cursor.

Next using the INSERT INTO statement as a parameter within the cur.execute() method, we insert some records into the table. Next, the conn.commit() and conn.close() statements are executed to commit the inserted records and close the connection.

import psycopg2
conn = psycopg2.connect(database="student", user="postgres", password="postgres", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("INSERT INTO student (id, name, roll) \ 
      VALUES (01, 'Maddy', 3)");
cur.execute("INSERT INTO student (id, name, roll) \ 
      VALUES (02, 'John', 4)");
conn.commit()
print("Records created successfully");
conn.close()

Verify our data in pgAdmin

Select Records

As seen in previous examples we import the module, establish the connection to the database, and then open up a cursor.

Next using the SELECT statement as parameter within cur.execute() method we fetch records from the table. The cur.fetchall() method fetches all records from the table as list of tuples and stores in the variable 'rows' which we iterate using 'for' loop to display the result. Finally the conn.close() statement is executed to close the connection.

import psycopg2
conn = psycopg2.connect(database="student", user="postgres", password="postgres", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("SELECT id, name, roll from student")
print("ID   Roll Number Student Name")
print("--------------------------")
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())
conn.close()

Update Record(s)

As seen in previous examples we import the module, establish the connection to the database, and then open up a cursor.

Next using the UPDATE statement as parameter within cur.execute() method we update the roll number for the student with ID=2 and use conn.commit() to commit the change. Next we run the SELECT statement to fetch the updated records and iterate over the list of tuples to display the result.

Finally the conn.close() statement is executed to close the connection.

import psycopg2
conn = psycopg2.connect(database="student", user="postgres", password="postgres", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("UPDATE student set roll = 5 where ID=2")
conn.commit
cur.execute("SELECT id, name, roll from student")
print("ID   Roll Number Student Name")
print("--------------------------")
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())
conn.close()

Delete Record(s)

As seen in previous examples we import the module, establish the connection to the database, and then open up a cursor.

Next using the DELETE statement as parameter within cur.execute() method we delete the entry for the student with ID=2 and use conn.commit() to commit the change. Next we run the SELECT statement to fetch the remaining records and iterate over the list of tuples to display the result.

Finally the conn.close() statement is executed to close the connection.

import psycopg2
conn = psycopg2.connect(database="student", user="postgres", password="postgres", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("DELETE from student where ID=2;")
conn.commit
cur.execute("SELECT id, name, roll from student")
print("ID   Roll Number Student Name")
print("--------------------------")
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())
conn.close()

Conclusion

This article gives an overview on how Python can be integrated with PostgreSQL database and how the basic DML and DDL statements can be executed via Python programming. We hope this article will help you get started on your Python with PostgreSQL journey.