Python PostgreSQL Tutorial Using Psycopg2

Posted in /  

Python PostgreSQL Tutorial Using Psycopg2

Vinay Khatri
Last updated on June 29, 2022

    PostgreSQL is one of the popular Relational Database Management Systems. It is a simple RDBMS with advanced and enterprise-class features. One of the reasons it is so popular is, it supports both SQL and JSON querying, which means you can perform relational and non-relation queries on a PostgreSQL database. PostgreSQL is supported by many programming languages, and Python is one of those. In this Python PostgreSQL tutorial, you will learn how to use the Pscopg2 library to connect PostgreSQL with Python. After connecting your Python script to PostgreSQL you can also perform the database queries and it will reflect on your actual PostgreSQL database. By the end of this tutorial, you will have a solid understanding on

    1. What is the Pscopg2 library?
    2. How to connect Python with PostgreSQL database.
    3. How to create a PostgreSQL database using Python
    4. How to perform PostgreSQL CURD operation with Python.

    Prerequisites tools

    Before we get started with the main topic Python connection with PostgreSQL, there are some tools that need to be installed in your system before you start coding.

    1. PostgreSQL : PostgreSQL must be downloaded on your system as a localhost. To download and install PostgreSQL on your computer click here .
    2. Python 3: This article primarily focuses on Python3, so it’s a no-brainer that Python must be installed on your system.

    What is the Psycopg2 library?

    Psycopg2 is one of the most popular Python PostgreSQL database adapters. It is a third-party open-source library, which allows you to connect your Python script with the PostgreSQL server or database. It is a very powerful library cum API that supports heavy multi-threaded applications, which help multiple users to operate on a single database concurrently. Popular Python Web-Frameworks like Flask, Django, and Pyramid are also compatible with Psycopg2 for Postgresql connectivity.

    Install Python Psycopg2 library

    As Psycopg2 is not a Part of Standard Python modules, we need to install it for your Python environment using the pip install command. Run the following command on your Terminal, Bash, or command prompt and it will install the Psycopg2 for your Python environment.

    pip install psycopg2

    Alternatives of Python Psycopg2 library

    There are many alternatives of Psycopg2 which can also connect Python with PostgreSQL, and here is the list of those Psycopg2 alternatives.

    1. pg8000
    2. py-postgresql
    3. pygresql
    4. ocpgdb
    5. bpgsql
    6. Sqlalchemy

    Note: To use sqlalchemy one of the above libraries also needs to be installed. This tutorial will only be sticking to the Psycopg2 library because it is the most popular and stable Python library to connect with the PostgreSQL database. psycopg2 also has a binary stand-along package that needs no compiler or external libraries to execute. With the following pip install, you can also download its binary version using the pip command only.

    pip install psycopg2-binary

    Psycopg2 has multiple versions if you are interested in the older versions, you can install that specific version by specifying it while installing the package. For example, suppose you want to install the psycopg2 version 2.8.5 released back in April 2020, you can install it using the following pip install command.

    pip install psycopg2 = 2.8.5

    How to connect Python with PostgreSQL

    Here are the steps you need to follow to connect Python with PostgreSQL:

    Step 1: Create a PostgreSQL Database

    With Python and psycopg2 we can only set the connection between Python and a PostgreSQL database. Before setting up the connection we need to create a Database first then we will connect using it with a Python script using psycopg2. To create a database in PostgreSQL, you can either use the PgAdmin or SQL Shell(psql). It’s straightforward to create a PostgreSQL database using SQL Shell, so we will be using that. To create a new database you can use the CREATE DATABASE databaseName; commend. Let’s create a PostgreSQL database by name students . Open your SQL Shell(psql), log in with the local host, and execute the following command.

    CREATE DATABASE students;

    The above command will create a new database of students for you. To check if the database was created successfully you can list out all the databases using the \l command.

    Step 2: Connect Python with PostgreSQL database

    After successfully creating a PostgreSQL database now you can start writing your Python code and connect your Python script with created PostgreSQL database.

    How to connect Python with PostgreSQL Database?

    Now let’s discuss the steps that we need to follow just to connect a Python script with the PostgreSQL database.

    Step I: Import the psycopg2 library

    The first step toward setting up the connection is importing the psycopg2 library into the script. To import the library we can use Python’s import keyword.

    #import the psycopg2 library into the Python program
    
    import psycopg2

    Step II: Setup the connection using psycopg2.connect() metod and login credentials.

    While creating the Database on Shell(psql) you have noticed that it asked you Server, Database, Port, Username, and Password details. This is because of security reasons, even in the command shells, you have to log in first before accessing any PostgreSQL database. The same security goes when we access a PostgreSQL database using Python. We first have to connect to the database using the database credentials. To set the connection, the credentials you required are

    1. UserName
    2. Password
    3. Host
    4. Port
    5. DataBaseName

    Username: It is your username with which you are working on PostgreSQL. By default, its value is postgres Password: It is the password that you have created while installing Postgresql. Host: It is the host IP address or server name on which PostgreSQL is running. In our case, we are running the PostgreSQL locally, which means our host value is localhost. Port: It is the Port number on which the PostgreSQL is running. By default, PostgreSQL uses Port number 5432. DataBaseName: It is the database name that you want to set up a connection with. For this tutorial, we have created students database in the above section. So we will be using that database name. You can most of the above details while running the Shell (psql) Once you have all the credentials you can set a connection to the PostgreSQL database using the psycopg2.connect(dbname, user, password, port=5432, host) method

    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)

    The psycopg2.connect() method creates a new session of the database, connects our script to the valid database, and returns a new connection object. The try block would execute and set the connection successfully if all the credentials are correct, otherwise for the invalid credential the except block will execute.

    Step 3: Perform SQL operation on PostgreSQL database using Python

    After the successful connection setup, now it’s time to perform the SQL operation on the connected database using Python. To execute the SQL operation on the database we have to initialize a cursor object for the connection. To initialize the cursor object we can call the cursor() class on the conn object that we created in the above section. The cursor object allows you to perform the database operations on the selected database.

    #initialize cursor object
    cursor = conn.cursor()

    The cursor object cursor , supports a execute() method which accepts the SQL query or command as a string parameter and executes that command on the database. Let’s use the execute() method and execute the SELECT version(); postgreSQL command. Example

    # import the psycopg 2 library into Python program
    import psycopg2
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        #initialize cursor object
        cursor = conn.cursor()
    
        #execute the query on cursor
        cursor.execute("SELECT version();")
    
        result = cursor.fetchone()
    
        print("The version of the PostgreSQL is:", result)
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    #close the connection after all the operations
    cursor.close()

    Output

    The version of the PostgreSQL is: ('PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit',)

    The execute() method return None, the method executes the query on the cursor object only. To get the query result we have to use the fetchone(), fetchall(), or fetchmany() methods. The fetchone() returns the first row from the result query set The fetchall() returns a list of all the rows from the result query set. The fetchmany(n) returns specific n numbers of rows from the result query set. The cursor.close() method closes the created connection session between the script and PostgreSQL. It is generally used at the end of the script when we are done with all the operations.

    Step 4: Create a PostgreSQL table using Python

    Now we are connected with our students’ database. Right now the database has no tables, to create a new table with columns, we can execute the CREATE TABLE command using the execute() method. Let’s create a table of student_details with the following column attributes Integer ID, Varchar Name, Integer age, Varchar grade, Real marks, Date dob. Example

    # import the psycopg 2 library into Python program
    import psycopg2
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        #initialize cursor object
        cursor = conn.cursor()
    
        query = '''
                   CREATE TABLE IF NOT EXISTS student_details
                   (id integer PRIMARY KEY, name varchar, age integer, grade varchar, marks real, dob date );
                '''
    
        #execute the query on cursor
        cursor.execute(query)
    
        #make the changes to the database persistent
        conn.commit()
    
        print("The table has been created successfully")
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    #close the connection after all the operations
    cursor.close()

    Output The table has been created successfully The above program will generate a table student_details for the students database. You can verify whether the table was created or not by typing \dt command on the Shell(psql).

    students=# \c students
    You are now connected to database "students" as user "postgres".
    students=# \dt
                  List of relations
     Schema |      Name       | Type  |  Owner
    --------+-----------------+-------+----------
     public | student_details | table | postgres
    (1 row)

    The conn.commit() method will make sure that the changes also reflect on the actual database. If you forget to execute the commit() method on the connection object, there will be no changes on the database.

    Take away points from the above example

    1. Always use the CREATE TABLE IF NOT EXISTS query when creating a table for a database. The IF NOT EXISTS SQL command makes sure that it only creates a table if it does not exist. If you omit this command from the create table and re-execute the python script it will raise the relation "student_details" already exists error.
    2. Always bind the query string using triple quotes ''' or use a raw string.
    3. Make sure to commit the changes after executing all the operations before closing the connection.

    Step 5: Insert data into PostgreSQL table using Python

    Whether it is a structured programing language or a high-level programming language both have data types to represent different data objects. Both Python and PostgreSQL have a defined set of data types. When we insert data into the database table the psycopg2 map the Python datatype to PostgreSQL data type. Here is the list of the data type mapping performed by the psycopg2

    Python Data types PostgreSQL Data type
    None NULL
    bool bool
    float real double
    int smallint integer bigint
    str unicode varchar text
    buffer memoryview bytearray bytes Buffer protocol bytea
    datetime.date date
    time time timetz
    datetime timestamp timestamptz
    timedelta interval
    list ARRAY
    tuple namedtuple Composite types IN syntax
    dict hstore
    Psycopg’s Range range
    Anything™ json
    UUID uuid
    ipaddress objects inet cidr

    Now let’s insert some random data set to the student_details table that we created in the above section. To insert the data into the PostgreSQL database’s table we can use the SQL INSERT INTO table_name (id, name, age, grade, marks, dob ) VALUES (id_data, name_data, age_data, grade_data, marks_data, dob_data) Example

    # import the psycopg2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        names = ['Rahul', 'Neha', 'Joe', 'Rose', 'Jack']
        ages = [16, 17, 18, 17, 16]
        grades = ['11th', '11th', '12th', '12th', '10th']
        marks = [998.2, 997.2, 990, 987, 983.4]
        dobs = [(2005, 1, 1), (2004, 2, 14), (2003, 4, 15),
                (2004, 5, 17), (2005, 7, 25)]
    
        for i in range(5):
            query = f'''
                    INSERT INTO
                    student_details (id , name, age, grade, marks, dob)
                    VALUES  ({i}, '{names[i]}',{ages[i]}, '{grades[i]}', {marks[i]}, '{datetime.datetime(dobs[i][0], dobs[i][1], dobs[i][2])}')
                    ;
                    '''
    
            print(f"Inserted {i+1} record to row")
    
            # execute the query on cursor
            cursor.execute(query)
    
        # make the changes to the database persistent
        conn.commit()
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    Inserted 1 record to row
    Inserted 2 record to row
    Inserted 3 record to row
    Inserted 4 record to row
    Inserted 5 record to row

    Take Away points for the above example.

    1. For date data convert the date into a datetime object.
    2. For the string, and date values use the wrap the data with a single quote, as done in the above example.

    That’s it with connecting to a PostgreSQL database using Python, creating tables, and inserting data into the tables. Now Let’s perform some CRUD operations with the student database.

    How to Perform the CRUD operations on PostgreSQL database using Python?

    CRUD operation means Create, Read/Retrieve, Update and Delete. To perform the CRUD operation on a database table, we can use the following SQL command.

    1. INSERT to Create new row data.
    2. SELECT to Read or Retrieve row data.
    3. UPDATE and SET commands to Update row data, and,
    4. DELETE command to delete row data.

    Create a new data row in a database using Python

    To create a new row or a record of data, we can use the SQL INSERT command. Example Let’s add a new student detail to the student_details table, using Python.

    # import the psycopg 2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        new_student = {'id': 5, 'name': 'Rohan', 'age': 17, 'grade': '11th',
                       'marks': 979.3, 'dob': datetime.datetime(2004, 5, 20)}
    
        query = f'''
                INSERT INTO
                student_details (id , name, age, grade, marks, dob)
                VALUES  ({new_student['id']}, '{new_student['name']}',{new_student['age']}, '{new_student['grade']}', {new_student['marks']}, '{new_student['dob']}')
                ;
                '''
        print(f"New Record has been inserted into the table")
    
        # execute the query on cursor
        cursor.execute(query)
    
        # make the changes to the database persistent
        conn.commit()
    
    except psycopg2.Error as e:
    
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    New Record has been inserted into the table

    The INSERT command inserts a new row into the table. To add a new column to the table we can use the ALTER TABLE table_name ADD COLUMN command. Let’s add a new column email into the student_details table

    # import the psycopg 2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        query = f'''
                ALTER TABLE student_details
                ADD COLUMN email VARCHAR
                ;
                '''
    
        print(f"New Column has been added to the table")
    
        # execute the query on cursor
        cursor.execute(query)
    
        # make the changes to the database persistent
        conn.commit()
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    New Column has been added to the table

    To verify the newly added row and column you can run the SELECT * FROM table_name command on your Shell(psql)

    Read/Retrieve rows data from Postgresql database table using Python

    To read the data from the rows we use the SELECT row_names FROM table_name SQL command. And to access those rows in Python we can either use fetchone() , fetchall() , or fetchmany() methods. Example 1 Read the first student detail from the student_details table.

    # import the psycopg2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        # select all the rows from the studnet_details
        query = f'''
                SELECT * FROM student_details ;
                '''
    
        # execute the query on cursor
        cursor.execute(query)
    
        # get only first row data
        result = cursor.fetchone()
    
        print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
              "\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
    
        print(result[0], "\t", result[1], "\t", result[2], "\t", result[3],
              "\t", result[4], "\t", result[5].strftime("%d/%m/%Y"), "\t", result[6])
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    ID       NAME    AGE     GRADE   MARKS   DOB     EMAIL
    0        Rahul   16      11th    998.2   01/01/2005      None

    The fetchone() method returns a single row of data as a tuple. To fetch all the rows we can use the fetchall() method, or to fetch a specific number of rows we can use the fetchmany() function Example 2 Read the first 3 students by selecting all the rows.

    # import the psycopg 2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        # select all the rows from the studnet_details
        query = f'''
                SELECT * FROM student_details ;
                '''
    
        # execute the query on cursor
        cursor.execute(query)
    
        # get only first 3 rows data
        result = cursor.fetchmany(3)
    
        print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
              "\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
    
        for student in result:
            print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
                  "\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    ID       NAME    AGE     GRADE   MARKS   DOB     EMAIL
    0        Rahul   16      11th    998.2   01/01/2005      None
    1        Neha    17      11th    997.2   14/02/2004      None
    2        Joe     18      12th    990.0   15/04/2003      None

    Instead of fetchmany(3) we could have also limited the query result by using the LIMIT 3 SQL command. For example

    query = f'''
                SELECT * FROM student_details LIMIT 3 ;
                '''

    Update rows data of a Postgresql database table using Python

    In the update operation, you can update the old content or data of a single or multiple rows at once. In SQL to update a data of a row we have the UPDATE and SET commands. Example Update the student marks which id is 4 and also set his/her email address.

    # import the psycopg 2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
        cursor = conn.cursor()
    
        # update the row
        query1 = f'''
                UPDATE student_details
                SET marks = 986.56,
                email = 'jackgmail.com'
                WHERE id = 4
                ;
                '''
        # execute the query on cursor
        cursor.execute(query1)
    
        #commit the changes
        conn.commit()
    
        # SELECT all the rows
        query2 = f'''
                SELECT * FROM student_details
                ORDER BY id
                ;
                '''
        # execute the query on cursor
        cursor.execute(query2)
    
        # get all rows
        result = cursor.fetchall()
    
        print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
              "\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
    
        for student in result:
            print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
                  "\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
    
    except psycopg2.Error as e:
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    ID       NAME    AGE     GRADE   MARKS   DOB     EMAIL
    0        Rahul   16      11th    998.2   01/01/2005      None
    1        Neha    17      11th    997.2   14/02/2004      None
    2        Joe     18      12th    990.0   15/04/2003      None
    3        Rose    17      12th    987.0   17/05/2004      None
    4        Jack    16      10th    986.56          25/07/2005      jackgmail.com
    5        Rohan   17      11th    979.3   20/05/2004      None

    Delete rows data of a Postgresql database table using Python

    To delete a row or record from a PostgreSQL database table we can use the DELETE FROM command. Example DELETE the row which id is 5

    # import the psycopg 2 library into Python program
    import psycopg2
    # import datetime module for date
    import datetime
    
    
    # connection credentials
    userName = "postgres"
    passWord = "secretpass123"
    port = 5432
    dataBaseName = 'students'
    
    try:
        # set the connection
        conn = psycopg2.connect(dbname=dataBaseName, user=userName,
                                password=passWord, port=port, host='localhost')
    
        # initialize cursor object
    
        cursor = conn.cursor()
    
        # delete the row
        query1 = f'''
                DELETE FROM student_details
                WHERE id = 5
                ;
                '''
    
        # execute the query on cursor
        cursor.execute(query1)
    
        #commit the changes
        conn.commit()
    
        # SELECT all the rows
        query2 = f'''
                SELECT * FROM student_details
                ORDER BY id
                ;
                '''
    
        # execute the query on cursor
        cursor.execute(query2)
    
        # get all rows
        result = cursor.fetchall()
    
        print('ID', "\t", 'NAME', "\t", 'AGE', "\t", 'GRADE',
              "\t", 'MARKS', "\t", 'DOB', "\t", 'EMAIL')
    
        for student in result:
            print(student[0], "\t", student[1], "\t", student[2], "\t", student[3],
                  "\t", student[4], "\t", student[5].strftime("%d/%m/%Y"), "\t", student[6])
    
    except psycopg2.Error as e:
    
        print("Fail to execute due to the error:", e)
    
    # close the connection after all the operations
    cursor.close()

    Output

    ID       NAME    AGE     GRADE   MARKS   DOB     EMAIL
    0        Rahul   16      11th    998.2   01/01/2005      None
    1        Neha    17      11th    997.2   14/02/2004      None
    2        Joe     18      12th    990.0   15/04/2003      None
    3        Rose    17      12th    987.0   17/05/2004      None
    4        Jack    16      10th    986.56          25/07/2005      jackgmail.com

    Conclusion

    Now let’s sum up our Python tutorial on how to connect Python with the Postgresql database. To connect a Python program to a PostgreSQL database we need a database adapter like psycopg2. Psycopg2 is an API that acts as a bridge between Python and PostgreSQL databases so we can execute SQL queries using Python only. To set up the connection between Python and Postgresql we use the psycopg2.connect() method, and conn.cursor() method to perform the SQL operation. To execute the SQL query on the database we take the help of the cursor.execute() method. And to save the changes on the real database use the conn.commit() method.

    People are also reading:

    Leave a Comment on this Post

    0 Comments