Python PostgreSQL Tutorial

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

PostgreSQL is a powerful, open-source relational database management system. Python, with its extensive libraries, can interact with PostgreSQL databases to perform various database operations like creating, reading, updating, and deleting records. This tutorial covers the basics of connecting Python to a PostgreSQL database and performing common database operations.

Table of Contents

1. Prerequisites
2. Installing Psycopg2
3. Connecting to PostgreSQL Database
4. Creating a Database
5. Creating a Table
6. Inserting Data
7. Querying Data
8. Updating Data
9. Deleting Data
10. Using Transactions
11. Handling Exceptions
12. Conclusion

1. Prerequisites

Before you start, make sure you have:

• Python installed on your system.
• PostgreSQL installed and running on your system.
• Basic knowledge of SQL and Python.

2. Installing Psycopg2

To interact with PostgreSQL, you need to install the Psycopg2 library. You can install it using pip.

pip install psycopg2-binary 

3. Connecting to PostgreSQL Database

To connect to a PostgreSQL database, you need to import the psycopg2 module and use the connect() function.

Example

import psycopg2 # Establishing the connection conn = psycopg2.connect( host="localhost", database="yourdatabase", user="yourusername", password="yourpassword" ) # Creating a cursor object cursor = conn.cursor() # Checking if the connection was successful if conn: print("Connected to PostgreSQL database") 

4. Creating a Database

To create a new database, you need to connect to the PostgreSQL server and use the CREATE DATABASE SQL statement. Note that typically, databases are created outside of Python scripts, as this often requires higher-level administrative privileges.

Example

# Connecting to the default database conn = psycopg2.connect( host="localhost", database="postgres", user="yourusername", password="yourpassword" ) # Creating a cursor object cursor = conn.cursor() # Creating a new database cursor.execute("CREATE DATABASE mydatabase") print("Database created successfully") # Closing the connection to the default database cursor.close() conn.close() 

5. Creating a Table

To create a table, you need to connect to your newly created database and then use the CREATE TABLE SQL statement.

Example

# Connecting to the new database conn = psycopg2.connect( host="localhost", database="mydatabase", user="yourusername", password="yourpassword" ) # Creating a cursor object cursor = conn.cursor() # Creating a table create_table_query = """ CREATE TABLE employees ( id SERIAL PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL ) """ cursor.execute(create_table_query) conn.commit() print("Table created successfully") 

6. Inserting Data

You can insert data into a table using the INSERT INTO SQL statement.

Example

# Inserting data insert_query = "INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)" values = ("Ravi", "Kumar", "ravi.kumar@example.com") cursor.execute(insert_query, values) conn.commit() print("Data inserted successfully") 

7. Querying Data

You can query data from a table using the SELECT SQL statement.

Example

# Querying data select_query = "SELECT * FROM employees" cursor.execute(select_query) # Fetching all rows rows = cursor.fetchall() # Printing the rows for row in rows: print(row) 

8. Updating Data

You can update data in a table using the UPDATE SQL statement.

Example

# Updating data update_query = "UPDATE employees SET email = %s WHERE first_name = %s AND last_name = %s" values = ("ravi.kumar@newemail.com", "Ravi", "Kumar") cursor.execute(update_query, values) conn.commit() print("Data updated successfully") 

9. Deleting Data

You can delete data from a table using the DELETE FROM SQL statement.

Example

# Deleting data delete_query = "DELETE FROM employees WHERE first_name = %s AND last_name = %s" values = ("Ravi", "Kumar") cursor.execute(delete_query, values) conn.commit() print("Data deleted successfully") 

10. Using Transactions

Transactions ensure that a sequence of operations is executed as a single unit of work. You can use BEGIN, COMMIT, and ROLLBACK statements to manage transactions.

Example

try: # Starting a transaction conn.autocommit = False # Performing some operations cursor.execute("INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)", ("Anjali", "Sharma", "anjali.sharma@example.com")) cursor.execute("UPDATE employees SET email = %s WHERE first_name = %s AND last_name = %s", ("anjali.sharma@newemail.com", "Anjali", "Sharma")) # Committing the transaction conn.commit() print("Transaction committed successfully") except Exception as e: # Rolling back the transaction in case of an error conn.rollback() print("Transaction failed and rolled back", e) finally: # Resetting autocommit mode conn.autocommit = True 

11. Handling Exceptions

Use try-except blocks to handle exceptions that may occur during database operations.

Example

try: # Trying to connect to PostgreSQL conn = psycopg2.connect( host="localhost", database="mydatabase", user="yourusername", password="yourpassword" ) cursor = conn.cursor() print("Connected to PostgreSQL and accessed the database") except psycopg2.DatabaseError as e: print("Failed to connect to PostgreSQL", e) finally: if conn: cursor.close() conn.close() print("PostgreSQL connection closed") 

12. Conclusion

Connecting Python to a PostgreSQL database allows you to perform various database operations from your Python applications. This tutorial covered the basics of connecting to a PostgreSQL database, creating databases and tables, inserting, querying, updating, and deleting data, using transactions, and handling exceptions. By mastering these concepts, you can efficiently manage and interact with PostgreSQL databases in your Python projects.