Python Script to Execute SQL Statements on PostgreSQL
- Last Updated On: August 25, 2022
- By: Scriptcrunch Editorial
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 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.
Scriptcrunch Editorial
Other Interesting Blogs
[80% OFF] Linux Foundation Coupon for December 2024
Looking for CKA Coupon and other Linux Foundation certifications coupons? We got you covered. With the latest Linux Foundation Coupon, you can
[65% OFF] Linux Foundation LFCA, LFCS & LFCT Exam Voucher Codes
Linux Foundation has announced up to a $284 discount on its Linux certification programs Linux Foundation Certified IT Associate (LFCA) and Linux
Best Black Friday Deals For Techies in 2024
In this blog, we have listed down all the best Black Friday deals and Cyber Monday deals for techies. This is the