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.
- Connect to PostgreSQL database
- Create cursor object for database connection session.
- Execute SQL query
- Save query data in a variable as a list of tuple.
- 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
pip3 install psycopg2-binary
Here is the full Python script that connects to
catalog database and query the table named
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, ) print("Name = ", row) print("Price = ", row, "\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 = "select * from products"
5.We use the
cursor.execute () method to execute the 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, ) print("Name = ", row) print("Price = ", row, "\n")
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.