Python Script to Execute SQL Statements on PostgreSQL

In this blog I have added the Python Script to connect and execute SQL statements on PostgreSQL databases.

Here is what you will learn in this python script.

1. Connect to PostgreSQL database
2. Create cursor object for database connection session.
3. Execute SQL query
4. Save query data in a variable as a list of tuple.
5. Loop through the tuple and print all data.

Python Script To Access PostgreSQL Databases

To access PostgreSQL from a Python scripts you need to have psycopg2 module. It is a database adopter for PostgreSQL.

First you need to install psycopg2-binary module.

pip3 install psycopg2-binary

Here is the full Python script that connects to catalog database and query the table named products.

import psycopg2

try:
    connection = psycopg2.connect(database="catalog",
                        host="192.168.5.5",
                        user="postgres",
                        password="myPassword",
                        port="5432")
    cursor = connection.cursor()
    sql_query = "select * from products"

    cursor.execute(sql_query)
    print("Selecting rows from mobile table using cursor.fetchall")
    product_list = cursor.fetchall()

    print("Print each row and columns data")
    for row in product_list:
        print("Id = ", row[0], )
        print("Name = ", row[1])
        print("Price  = ", row[2], "\n")

except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)

finally:
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Here is the script explanation.

1. The script runs on a try, except and finally exception handler.

2. Under try, we create the PostgreSQL connection string using the database details.

connection = psycopg2.connect(database="catalog",
                        host="192.168.5.5",
                        user="postgres",
                        password="myPassword",
                        port="5432")

3. The we create a cursor object to open the connection. The cursor class is part of psycopg2 module that lets you execute PostgreSQL commands in database session.

cursor = connection.cursor()

4. Then we save the select query in sql_query variable.

sql_query = "select * from products"

5.We use the cursor.execute () method to execute the SQL query.

cursor.execute(sql_query)

6. Next we use cursor.fetchall() method to fetch all the rows of a SQL query result. It returns all the rows as a list of tuples so that we can loop through each row.

    prodcut_list = cursor.fetchall()

7. Next we loop through the product_list tuple list to print all the rows and column data.

    for row in product_list:
        print("Id = ", row[0], )
        print("Name = ", row[1])
        print("Price  = ", row[2], "\n")

Conclusion

In this blog, we learned how to connect and execute SQL queries on PostgreSQL using python script.

Python knowledge is essential for developers, DevOps engineers, and data engineers.

Check out the python for devops blog, where I have explained how python is used in DevOps and why it is essential for DevOps engineers.