Mastering Relational Databases in Python: From Psycopg2 to SQLAlchemy with a Real Project

📜 Prelude: A Tale of Two Libraries

Once upon a repo in the kingdom of Python, a developer (you!) stood at the crossroads of working with PostgreSQL. Two powerful paths appeared:

• Psycopg2 — the sharp sword of raw SQL, mighty but manual.
• SQLAlchemy — a magical staff, harnessing the arcane arts of ORM.

In this tale, we will walk both paths, one after another, using the Student Enrollment System — a classic yet evergreen RDBMS example.

🏗️ Project: Student Enrollment System

We have three mighty tables:

• students: Stores each learner's details.
• courses: Holds course information.
• enrollments: A bridge table showing who enrolled in what.

Let’s first create this project using psycopg2, then recreate it with SQLAlchemy ORM.

⚙️ Setup

🧱 Install PostgreSQL 16 (already done!)

Create a database named school.

🧪 Install Requirements

pip install psycopg2 sqlalchemy

📂 Folder Structure

Student-Enrollment-System/
├── psycopg2_version/
│   ├── create_tables.py
│   ├── insert_data.py
│   └── delete_data.py
├── sqlalchemy_version/
│   ├── models.py
│   ├── database.py
│   ├── insert_data.py
│   ├── delete_data.py
│   └── create_tables.py

🐘 Psycopg2: Low-level SQL, High-level Control

🔧 create_tables.py

import psycopg2

conn = psycopg2.connect("dbname=school user=postgres password=yourpass")
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT NOT NULL,
        grade VARCHAR(10)
    );
    CREATE TABLE IF NOT EXISTS courses (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT
    );
    CREATE TABLE IF NOT EXISTS enrollments (
        id SERIAL PRIMARY KEY,
        student_id INT REFERENCES students(id),
        course_id INT REFERENCES courses(id)
    );
''')
conn.commit()
cursor.close()
conn.close()

✍️ insert_data.py

conn = psycopg2.connect("dbname=school user=postgres password=yourpass")
cursor = conn.cursor()

cursor.execute("INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)", ('Ravi', 16, '10th'))
cursor.execute("INSERT INTO courses (name, description) VALUES (%s, %s)", ('Math', 'Algebra & Geometry'))
cursor.execute("INSERT INTO enrollments (student_id, course_id) VALUES (%s, %s)", (1, 1))
conn.commit()
cursor.close()
conn.close()

❌ delete_data.py – Delete enrollment where student name is Ravi

conn = psycopg2.connect("dbname=school user=postgres password=yourpass")
cursor = conn.cursor()
cursor.execute('''
    DELETE FROM enrollments
    WHERE student_id = (SELECT id FROM students WHERE name = %s)
''', ('Ravi',))
conn.commit()
cursor.close()
conn.close()

So far, so SQL. But there’s a lot of manual wiring. Let’s step into the realm of SQLAlchemy!

🧙 SQLAlchemy ORM: Pythonic Magic

🔮 database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql://postgres:yourpass@localhost/school"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

🧱 models.py

from sqlalchemy import Column, Integer, String, ForeignKey, Text
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()
class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    age = Column(Integer, nullable=False)
    grade = Column(String(10))
    enrollments = relationship("Enrollment", back_populates="student")
class Course(Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    description = Column(Text)
    enrollments = relationship("Enrollment", back_populates="course")
class Enrollment(Base):
    __tablename__ = 'enrollments'
    id = Column(Integer, primary_key=True)
    student_id = Column(Integer, ForeignKey('students.id'))
    course_id = Column(Integer, ForeignKey('courses.id'))
    student = relationship("Student", back_populates="enrollments")
    course = relationship("Course", back_populates="enrollments")

🏗️ create_tables.py

from models import Base
from database import engine

if __name__ == "__main__":
    Base.metadata.create_all(engine)
    print("All tables created successfully.")

✍️ insert_data.py

from database import SessionLocal
from models import Student, Course, Enrollment

session = SessionLocal()
student1 = Student(name="Ravi", age=16, grade="10th")
course1 = Course(name="Math", description="Algebra & Geometry")
session.add_all([student1, course1])
session.commit()
enrollment1 = Enrollment(student=student1, course=course1)
session.add(enrollment1)
session.commit()
session.close()
print("Sample data inserted.")

❌ delete_data.py

from database import SessionLocal
from models import Enrollment, Student

session = SessionLocal()
student = session.query(Student).filter(Student.name == "Ravi").first()
if student:
    session.query(Enrollment).filter(Enrollment.student_id == student.id).delete()
    session.commit()
    print("Enrollment deleted for Ravi.")
session.close()

⚔️ Psycopg2 vs SQLAlchemy — Code Duel

📚 Final Thoughts: Which Should You Choose?

• Use Psycopg2 if you want fine-grained SQL control or are doing performance tuning.
• Use SQLAlchemy ORM for 90% of applications — it’s readable, maintainable, and Pythonic.

But best of all, know both, like we did! Start with psycopg2 to understand SQL, then level up to ORM for speed and style.

🏁 The End (Or the Beginning?)

May your databases always be normalized, your queries optimized, and your models beautifully mapped.

If this blog helped you, share it, star it, fork it — and remember:

“The best code is not just written; it’s woven like poetry.”