Published on

How to connect and CRUD on PostgreSQL with Python psycopg2

Authors
Table of Contents

Python and PostgreSQL ?

PostgreSQL is an open-source easy-to-use relational database management system. It’s one of the few RDBMS with built-in JSON field support. Postgresql’s JSON support makes it different from other databases. Many Developers prefer PostgreSQL’S JSON feature rather than using NoSql databases.

In this tutorial, we will learn how to use python to connect with postgresql.

PostgreSQL Connectors For Python

Various Python libraries are available that help to establish connection and perform operations on postgreSQL databases. Some of the popular libraries are as follows:

  • Psycopg2: Psycopg2 is the most popular PostgreSQL database adapter for python. Many popular python web development frameworks like flask and django have extensive support for Psycopg2. Learn more about psycopg2 here

  • PyGreSQL: PyGreSQL embeds PostgreSQL query library to allow easy use of the PostgreSQL feature from python script. Source code and more about PyGreSQL

  • pg8000: pg8000 is entirely written in python with no external dependencies. The project is actively maintained,find more about it here

  • py-PostgreSQL: py-PostgreSQL is a Python 3 library for accessing and querying PostgreSQL databases. This library is not actively maintained, but is still used by a lot of projects worldwide. For more visit PyPi page for py-PostgreSQL

How to Download and Install Psycopg2

In this tutorial, We will be using the Psycopg2 python module for connecting to the PostgreSQL database and we will perform CRUD operations on the connected database.

Note : Before we proceed further, Make sure you have python installed on your system. For this blog post, we will be using Python 3.8.

  • Open your terminal, and execute command :

    pip install psycopg2

  • After the installation is done, let’s verify the installation by importing it into our python shell. Execute command :

    python

  • Import psycopg2 in your python shell. A successful import will look like this :

  • If you did not get any errors after the import, then you've installed the psycopg2 library in your system successfully.

Connect PostgreSQL with Python

Now after your installation is done , let’s move to VScode text editor and write some code to establish a connection to our PostgreSQL database. You can use any text editor of your choice.

To connect to the PostgreSQL database we would need some credentials. Make sure you have the following credentials with you.

  1. Username : name of the user that has access to the database. Default PostgreSQL user is postgres.
  2. Password : password for your user.
  3. Database : name of the database to which we will work.
  4. Host : address of the database. Host should look something like this 23.23.34.45 or my.awesome-db.com. By default the host will be localhost.
  5. Port : the port number. By default the port number for PostgreSQL is 5432.

Now once you are finished with acquiring these credentials, let’s work on connecting to our database with these credentials.

Create two python files i.e config.py and crud-script.py.

  1. config.py : config.py will contain all your credentials stored in python variables. We will use these variables inside crud-script.py to connect to our PostgreSQL database. We have kept our secrets in separate files just to keep sensitive information away from the away from the main code. Our config.py should look something like this:

    user = “bond-james-bond”
password = “836547VESPER”
database = “casino_db”
host = “bond.007server.com”
port = 3306

  2. crud-script.py : This file will connect to our database, print a version of the database and perform CRUD operations on the database. The file should look something like this :

    import psycopg2
import Config
Import traceback

def connect():
    try:
            connection = psycopg2.connect(
            user=Config.user,
            password=Config.password,
            database=Config.database,
            host=Config.host,
            port=Config.port
            )
            return connection
    except Exception as err:
        print(“Error occurred in making connection …”)
        traceback.print_exc()

def print_version(connection):
    cursor = connect().cursor()
    cursor.execute('SELECT version()')
    db_version = cursor.fetchone()
    print(db_version)
    cursor.close()
    connection.close()

if __name__ == ‘__main__’:
print_version(connect())

    In this script, we've imported the config, psycopg2 postgresql connector and traceback module. Traceback module will help us to print the complete exception log in event of an Exception.

Save these two files, and run the crud-script.py with the command :

python crud-script.py

If “Error occurred in making connection …” get printed on your terminal, then most probably the credentials that you have entered are not correct. Recheck your credentials and try again running the Crud-script.py.

On a successful run, it will show version of the database like this :

CRUD Operations with PostgreSQL and Python

CRUD stands for Create, Read, Update and Delete. These are the four basic operations that are being done on all the relational database management systems. We will be doing the same with the PostgreSQL database using Python psycopg2.

Creating a table with psycopg2

Let’s create a table named person inside our database which has id, first_name, last_name and city. Query which will create table inside the database is as follows :

CREATE TABLE person (
	id INT PRIMARY KEY,
	first_name VARCHAR(100),
	last_name VARCHAR(100),
	city VARCHAR(100)
);

Now, let us execute this query with the help of psycopg2. To create the table inside our database add the following function to crud-script.py :

def create(connection):
    cursor = connection.cursor()
    query = """
    create table person(
        id int primary key,
        first_name varchar(100),
        last_name varchar(100),
        city varchar(100)
    );
    """
    try:
        cursor.execute(query)
        connection.commit()
        print("table created successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This function will be responsible for creating a table named person inside our database.

Inserting data in PostgreSQL with psycopg2

Let's populate one row inside the table “person”. For that we would use a query that will insert the data to a table. Query for inserting the data would be:

INSERT INTO person (id, first_name, last_name, city) VALUES (1, “James”, “Bond”, “NY”);

Successful execution of this query will insert the data. Add the following function to crud-script.py to insert the data.

def insert(connection):
    cursor = connection.cursor()
    query = """
    INSERT INTO person (id, first_name, last_name, city) VALUES (%s, %s, %s, %s);
    """
    try:
        data = (1, “James”, “Bond”, “NY”)
        cursor.execute(query, data)
        connection.commit()
        print("Record inserted successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This function will populate one row inside the table “person”. On successful run of this function, “Record inserted successfully!” will get printed on your terminal.

Reading data from PostgreSQL with Python

In this step, we will be reading all the data present inside our table. To get all the records from the database , we need to use a select query. Query for fetching the data from a table “person” would be:

SELECT * FROM <table_name> LIMIT 500;

Now, to read the rows from the table “person” using psycopg2, add the following function to crud-script.py :

def read(connection):
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT * FROM person LIMIT 500;")
        record = cursor.fetchone()
        print(f"Read successful, 1st row is : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This would read the 500 rows from the table “person” as we have limited the number of rows to 500 here. On a successful run of this function, Read successful, 1st row is : id=1, name=James Bond, city=NY should get printed on your terminal.

Updating data in PostgreSQL with psycopg2

In this step, we will be updating the existing row. To update specific column of an existing row, we will use the update query as follows:

UPDATE <table_name> SET <column_to_update>=’%s’ WHERE <condition>;

Add the following function to crud-script.py to update the column of a specific row in a table.

def update(connection):
    cursor = connection.cursor()
    query = """
    UPDATE person SET city=’%s’ WHERE id=%s;
    """
    try:
        cursor.execute(query, ("Sydney", 1))
        cursor.execute("SELECT * FROM person WHERE id=1;")
        record = cursor.fetchone()
        print(f"Update successful : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This function will be update the column “city” from “NY” to “Sydney” where “id” is 1 . Initially the city was set to “NY”, after running the update function the city got changed to “Sydney”. On a successful run of this function, you would be getting Update successful : id=1, name=James Bond, city=Sydney on your terminal output.

Deleting a record with psycopg2

In this step, we will be deleting an existing record from the table. To delete the record we will use the delete query as follows :

DELETE FROM <table_name> WHERE <condition>;

Similar to the last operation, add the following function in Crud-script.py to delete a row from the table “person”.

def delete(connection):
    cursor = connection.cursor()
    query = """
    DELETE FROM person WHERE id=1;
    """
    try:
        cursor.execute(query)
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(record)
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This function will delete a row from our table “person” where the id is 1.

Ending Notes

That is how you create a connection and perform CRUD(create, delete, insert, update, read) operations on a PostgreSQL database using Python psycopg2. On completion of all the steps, your final crud-script.py should look something like this :

import psycopg2
import Config
Import traceback

def connect():
    try:
        connection = psycopg2.connect(
            user=Config.user,
            password=Config.password,
            database=Config.database,
            host=Config.host,
            port=Config.port
        )
        return connection
    except Exception as err:
        print(“Error occurred in making connection …”)
        traceback.print_exc()

def print_version(connection):
    cursor = connect().cursor()
    cursor.execute('SELECT version()')
    db_version = cursor.fetchone()
    print(db_version)
    cursor.close()
    connection.close()

def create(connection):
    cursor = connection.cursor()
    query = """
    create table person(
        id int primary key,
        first_name varchar(100),
        last_name varchar(100),
        city varchar(100)
    );
    """
    try:
        cursor.execute(query)
        connection.commit()
        print("table created successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def insert(connection):
    cursor = connection.cursor()
    query = """
    INSERT INTO person (id, first_name, last_name, city) VALUES (%s, %s, %s, %s);
    """
    try:
        data = (1, “James”, “Bond”, “NY”)
        cursor.execute(query, data)
        connection.commit()
        print("Record inserted successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def read(connection):
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT * FROM person LIMIT 500;")
        record = cursor.fetchone()
        print(f"Read successful, 1st row is : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def update(connection):
    cursor = connection.cursor()
    query = """
    UPDATE person SET city=’%s’ WHERE id=%s;
    """
    try:
        cursor.execute(query, ("Sydney", 1))
        cursor.execute("SELECT * FROM person WHERE id=1;")
        record = cursor.fetchone()
        print(f"Update successful : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def delete(connection):
    cursor = connection.cursor()
    query = """
    DELETE FROM person WHERE id=1;
    """
    try:
        cursor.execute(query)
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(record)
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

if __name__ == ‘__main__’:
  delete(connect())
Schedule·a·Technical·Requirements·Consultation·call·with·me.
Share on :  Facebook • Twitter • LinkedIn • Whatsapp