PostgreSQL with Python - Rishan Solutions

PostgreSQL is a powerful open-source relational database known for its robustness, scalability, and advanced features. Python provides easy integration with PostgreSQL using libraries like psycopg2 and SQLAlchemy.

1. Why Use PostgreSQL with Python?

Open Source – Free and widely supported.
ACID Compliance – Ensures data integrity.
Extensibility – Supports JSON, full-text search, and custom functions.
Concurrency – Handles multiple users efficiently.
Indexing & Optimization – Supports B-Trees, Hash, and GIN indexes.

2. Installing PostgreSQL and Python Connector

Install PostgreSQL

Download and install PostgreSQL from PostgreSQL Official Website.
Set up a username and password during installation.
Use pgAdmin or the command-line tool to manage databases.

Install Python PostgreSQL Connector (`psycopg2`)

pip install psycopg2-binary

pip install psycopg2

psycopg2 is the most popular PostgreSQL adapter for Python.

3. Connecting to a PostgreSQL Database

Example: Establishing a Connection

import psycopg2

# Connect to PostgreSQL Server
conn = psycopg2.connect(
    host="localhost",
    database="testdb",
    user="postgres",
    password="yourpassword"
)

cursor = conn.cursor()  # Create a cursor object
print("Connected to PostgreSQL database successfully!")

Change host, database, user, and password as per your setup.
cursor is used to execute SQL queries.

4. Creating a Database

Before creating tables, ensure the database exists.

cursor.execute("CREATE DATABASE testdb")
print("Database created successfully!")

You cannot create a database inside a connection to another database.
Connect to the default postgres database first to create a new one.

To use the database:

cursor.execute("USE testdb")  # PostgreSQL does not support USE; reconnect to the DB instead.

5. Creating a Table in PostgreSQL

Tables store structured data.

cursor.execute("""
CREATE TABLE IF NOT EXISTS Users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(255) UNIQUE
)
""")
print("Users table created successfully!")

SERIAL auto-generates unique id values.
PRIMARY KEY ensures unique row identification.
UNIQUE prevents duplicate emails.

6. Inserting Data into PostgreSQL

Single Record Insertion

cursor.execute("""
INSERT INTO Users (name, age, email) VALUES (%s, %s, %s)
""", ("John Doe", 25, "john@example.com"))

conn.commit()  # Save changes
print("User added successfully!")

Use %s placeholders to prevent SQL injection.
Always commit (conn.commit()) to save changes.

Insert Multiple Records

users = [
    ("Alice", 30, "alice@example.com"),
    ("Bob", 22, "bob@example.com"),
    ("Charlie", 28, "charlie@example.com")
]
cursor.executemany("INSERT INTO Users (name, age, email) VALUES (%s, %s, %s)", users)
conn.commit()
print("Multiple users added successfully!")

executemany() inserts multiple rows efficiently.

7. Retrieving Data from PostgreSQL

Fetching data is essential for application logic.

Retrieve All Users

cursor.execute("SELECT * FROM Users")
users = cursor.fetchall()  # Get all results

for user in users:
    print(user)  # Output each row

fetchall() returns a list of tuples.

Retrieve a Single Record

cursor.execute("SELECT * FROM Users WHERE name = %s", ("Alice",))
user = cursor.fetchone()  # Get the first match
print(user)

fetchone() returns the first result.

Retrieve Specific Columns

cursor.execute("SELECT name, age FROM Users")
names_and_ages = cursor.fetchall()
print(names_and_ages)

Fetching only required columns improves performance.

8. Updating Data in PostgreSQL

Modify records using UPDATE.

cursor.execute("UPDATE Users SET age = %s WHERE name = %s", (35, "Alice"))
conn.commit()
print("User updated successfully!")

Always use commit() after updates.

9. Deleting Data in PostgreSQL

Remove records using DELETE.

cursor.execute("DELETE FROM Users WHERE name = %s", ("Bob",))
conn.commit()
print("User deleted successfully!")

Deleting is permanent, so use it carefully.

10. Using Transactions in PostgreSQL

Ensure data consistency using transactions.

try:
    conn.autocommit = False  # Start transaction
    cursor.execute("UPDATE Users SET age = 40 WHERE name = 'Charlie'")
    cursor.execute("DELETE FROM Users WHERE name = 'Alice'")
    conn.commit()  # Commit changes
    print("Transaction successful!")
except:
    conn.rollback()  # Undo changes if an error occurs
    print("Transaction failed! Rolling back changes.")

Set autocommit = False to enable transactions.
Use rollback() to undo changes on failure.

11. Exporting and Importing PostgreSQL Data

Export Data to CSV

import csv

cursor.execute("SELECT * FROM Users")
with open("users.csv", "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerow([desc[0] for desc in cursor.description])  # Column names
    writer.writerows(cursor.fetchall())

print("Data exported to users.csv!")

Import Data from CSV to PostgreSQL

with open("users.csv", "r") as file:
    reader = csv.reader(file)
    next(reader)  # Skip header
    cursor.executemany("INSERT INTO Users (id, name, age, email) VALUES (%s, %s, %s, %s)", reader)

conn.commit()
print("Data imported successfully!")

Easily migrate PostgreSQL data between databases using CSV files.

12. Closing PostgreSQL Connection

Always close connections after database operations.

conn.close()
print("Database connection closed.")

Helps prevent memory leaks and connection issues.

13. Advanced PostgreSQL Features in Python

Feature	Description
Indexes	Speed up queries (`CREATE INDEX`)
Joins	Combine data from multiple tables
Stored Procedures	Execute reusable SQL scripts
Foreign Keys	Maintain relationships between tables
Full-Text Search	Improve search functionality
JSON Support	Store and query JSON data