SQL_and_SQLAlchemy_Cheat_Sheet.md

SQL & SQLAlchemy ORM - Complete Reference Guide

Quick Reference Card

SQL Commands

Command	Purpose	Example
CREATE TABLE	Define new table	CREATE TABLE users (...)
INSERT	Add rows	INSERT INTO users VALUES (...)
SELECT	Query data	SELECT * FROM users
UPDATE	Modify rows	UPDATE users SET ...
DELETE	Remove rows	DELETE FROM users WHERE ...
DROP TABLE	Delete table	DROP TABLE users

SQLAlchemy CRUD

# Create
user = User(username='Alice')
session.add(user)
session.commit()

# Read
users = session.query(User).all()
user = session.query(User).filter(User.username == 'Alice').first()

# Update
user.email = 'new@email.com'
session.commit()

# Delete
session.delete(user)
session.commit()

Table of Contents

• SQL Basics
• DDL - Data Definition
• DML - Data Manipulation
• SQL Queries
• SQLAlchemy Setup
• SQLAlchemy Models
• SQLAlchemy CRUD
• Relationships
• Advanced Queries

---

SQL Basics

What is SQL?

-- SQL - Structured Query Language
-- Used to manage and query relational databases

-- CRUD Operations:
-- Create (INSERT)
-- Read (SELECT)
-- Update (UPDATE)
-- Delete (DELETE)

Database Concepts

-- Database: Collection of tables
-- Table: Collection of rows (records)
-- Row: Single record with multiple columns
-- Column: Single field/attribute

-- Example:
-- Database: school
--   Table: students
--     Columns: id, name, age, grade
--     Row: (1, 'Wilson', 20, 'A')

---

DDL - Data Definition

Create Table

-- CREATE TABLE - Define new table structure

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER,
    grade TEXT,
    enrolled_date DATE DEFAULT CURRENT_DATE
);

-- With constraints
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1,
    CHECK (age >= 18)
);

Alter Table

-- ALTER TABLE - Modify existing table

-- Add column
ALTER TABLE students
ADD COLUMN phone TEXT;

-- Drop column
ALTER TABLE students
DROP COLUMN phone;

-- Rename column
ALTER TABLE students
RENAME COLUMN grade TO gpa;

-- Rename table
ALTER TABLE students
RENAME TO enrollments;

Drop Table

-- DROP TABLE - Delete entire table

DROP TABLE students;

-- Drop if exists (safer)
DROP TABLE IF EXISTS students;

Data Types

-- Common SQLite data types:

INTEGER  -- Whole numbers: 1, 42, -100
REAL     -- Decimal numbers: 3.14, 99.99
TEXT     -- Strings: 'Hello', 'Wilson@email.com'
BLOB     -- Binary data: images, files
BOOLEAN  -- True/False (stored as 0/1)
DATE     -- Dates: '2024-01-15'
DATETIME -- Date and time: '2024-01-15 14:30:00'

Constraints

-- Constraints - Rules for data integrity

CREATE TABLE products (
    id INTEGER PRIMARY KEY,           -- Unique identifier
    name TEXT NOT NULL,               -- Cannot be NULL
    price REAL NOT NULL CHECK (price > 0),  -- Must be positive
    sku TEXT UNIQUE,                  -- Must be unique
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id)  -- Must exist in categories
);

---

DML - Data Manipulation

INSERT - Add Data

-- Insert single row
INSERT INTO students (first_name, last_name, email, age)
VALUES ('Wilson', 'Johnson', 'Wilson@email.com', 20);

-- Insert multiple rows
INSERT INTO students (first_name, last_name, email, age)
VALUES
    ('Bob', 'Smith', 'bob@email.com', 22),
    ('Charlie', 'Brown', 'charlie@email.com', 21),
    ('Diana', 'Prince', 'diana@email.com', 23);

-- Insert with all columns
INSERT INTO students
VALUES (1, 'Eve', 'Wilson', 'eve@email.com', 19, 'A', '2024-01-15');

SELECT - Retrieve Data

-- Select all columns
SELECT * FROM students;

-- Select specific columns
SELECT first_name, last_name, email FROM students;

-- Select with alias
SELECT first_name AS "First Name",
       last_name AS "Last Name"
FROM students;

-- Select distinct values
SELECT DISTINCT grade FROM students;

-- Select with calculation
SELECT first_name, age, age + 1 AS "Next Year Age"
FROM students;

UPDATE - Modify Data

-- Update single row
UPDATE students
SET grade = 'A+'
WHERE id = 1;

-- Update multiple columns
UPDATE students
SET grade = 'B', age = 21
WHERE first_name = 'Wilson';

-- Update all rows (careful!)
UPDATE students
SET is_active = 1;

-- Update with calculation
UPDATE products
SET price = price * 1.1  -- 10% increase
WHERE category = 'Electronics';

DELETE - Remove Data

-- Delete specific rows
DELETE FROM students
WHERE id = 1;

-- Delete with condition
DELETE FROM students
WHERE age < 18;

-- Delete all rows (careful!)
DELETE FROM students;

-- Better: use TRUNCATE (if supported)
TRUNCATE TABLE students;

---

SQL Queries

WHERE Clause

-- Filter results with conditions

-- Equal
SELECT * FROM students WHERE grade = 'A';

-- Not equal
SELECT * FROM students WHERE grade != 'F';

-- Comparison
SELECT * FROM students WHERE age >= 21;

-- Multiple conditions (AND)
SELECT * FROM students
WHERE grade = 'A' AND age >= 20;

-- Multiple conditions (OR)
SELECT * FROM students
WHERE grade = 'A' OR grade = 'B';

-- IN operator
SELECT * FROM students
WHERE grade IN ('A', 'B', 'C');

-- BETWEEN
SELECT * FROM students
WHERE age BETWEEN 18 AND 22;

-- LIKE (pattern matching)
SELECT * FROM students
WHERE email LIKE '%@gmail.com';

-- IS NULL
SELECT * FROM students
WHERE phone IS NULL;

ORDER BY

-- Sort results

-- Ascending (default)
SELECT * FROM students
ORDER BY last_name;

-- Descending
SELECT * FROM students
ORDER BY age DESC;

-- Multiple columns
SELECT * FROM students
ORDER BY grade ASC, age DESC;

LIMIT and OFFSET

-- Limit number of results
SELECT * FROM students
LIMIT 10;

-- Pagination
SELECT * FROM students
LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

-- Top 5 oldest students
SELECT * FROM students
ORDER BY age DESC
LIMIT 5;

Aggregate Functions

-- COUNT - Count rows
SELECT COUNT(*) FROM students;
SELECT COUNT(*) FROM students WHERE grade = 'A';

-- SUM - Sum values
SELECT SUM(price) FROM products;

-- AVG - Average
SELECT AVG(age) FROM students;

-- MIN/MAX
SELECT MIN(age), MAX(age) FROM students;

-- Multiple aggregates
SELECT
    COUNT(*) as total_students,
    AVG(age) as average_age,
    MIN(age) as youngest,
    MAX(age) as oldest
FROM students;

GROUP BY

-- Group and aggregate

-- Count students per grade
SELECT grade, COUNT(*) as student_count
FROM students
GROUP BY grade;

-- Average age per grade
SELECT grade, AVG(age) as avg_age
FROM students
GROUP BY grade;

-- HAVING - Filter groups (like WHERE for groups)
SELECT grade, COUNT(*) as count
FROM students
GROUP BY grade
HAVING COUNT(*) > 5;

JOINs

-- INNER JOIN - Rows with matches in both tables
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

-- LEFT JOIN - All from left, matches from right
SELECT students.name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

-- Self JOIN - Join table to itself
SELECT e1.name, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

---

SQLAlchemy Setup

Installation

pip install sqlalchemy

Basic Setup

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

# Create engine (connect to database)
engine = create_engine('sqlite:///my_database.db', echo=True)
# echo=True shows SQL queries in console

# Create base class for models
Base = declarative_base()

# Create session factory
Session = sessionmaker(bind=engine)
session = Session()

Database Engines

# SQLite (file-based)
engine = create_engine('sqlite:///database.db')

# SQLite (in-memory)
engine = create_engine('sqlite:///:memory:')

# PostgreSQL
engine = create_engine('postgresql://user:password@localhost/dbname')

# MySQL
engine = create_engine('mysql://user:password@localhost/dbname')

---

SQLAlchemy Models

Basic Model

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime
from sqlalchemy.orm import declarative_base, Mapped, mapped_column
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'  # Table name in database

    # Define columns
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    email: Mapped[str] = mapped_column(String(300), unique=True, nullable=False)
    password: Mapped[str] = mapped_column(String(120), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)

    def __repr__(self):
        return f"<User(username='{self.username}', email='{self.email}')>"

Create Tables

from sqlalchemy import create_engine

engine = create_engine('sqlite:///my_db.db')

# Create all tables defined in Base
Base.metadata.create_all(engine)

Model with Validation

from sqlalchemy import Column, Integer, String, event
from sqlalchemy.orm import validates

class User(Base):
    __tablename__ = 'users'

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(300))
    age: Mapped[int] = mapped_column(Integer)

    @validates('email')
    def validate_email(self, key, email):
        if '@' not in email:
            raise ValueError("Invalid email address")
        return email.lower()

    @validates('age')
    def validate_age(self, key, age):
        if age < 0 or age > 150:
            raise ValueError("Invalid age")
        return age

---

SQLAlchemy CRUD

Create (Insert)

from models import User, session

# Create single user
new_user = User(
    username='Wilson',
    email='Wilson@example.com',
    password='hashed_password'
)

# Add to session
session.add(new_user)

# Commit to database
session.commit()

# Access ID after commit
print(new_user.id)  # Auto-generated ID

# Create multiple users
users = [
    User(username='bob', email='bob@example.com', password='pass123'),
    User(username='charlie', email='charlie@example.com', password='pass456')
]

session.add_all(users)
session.commit()

Read (Query)

from models import User, session

# Get all users
all_users = session.query(User).all()

# Get first user
first_user = session.query(User).first()

# Get by ID
user = session.get(User, 1)  # User with id=1

# Query with filter
Wilson = session.query(User).filter(User.username == 'Wilson').first()

# Multiple filters (AND)
user = session.query(User).filter(
    User.username == 'Wilson',
    User.is_active == True
).first()

# OR condition
from sqlalchemy import or_
users = session.query(User).filter(
    or_(User.username == 'Wilson', User.username == 'bob')
).all()

# LIKE query
gmail_users = session.query(User).filter(
    User.email.like('%@gmail.com')
).all()

# IN query
users = session.query(User).filter(
    User.username.in_(['Wilson', 'bob', 'charlie'])
).all()

# Order by
users = session.query(User).order_by(User.username).all()
users = session.query(User).order_by(User.created_at.desc()).all()

# Limit
users = session.query(User).limit(10).all()

# Count
count = session.query(User).count()

Update

from models import User, session

# Method 1: Query, modify, commit
user = session.query(User).filter(User.username == 'Wilson').first()
if user:
    user.email = 'newemail@example.com'
    user.is_active = False
    session.commit()

# Method 2: Update query (more efficient)
session.query(User).filter(User.username == 'Wilson').update({
    'email': 'newemail@example.com',
    'is_active': False
})
session.commit()

# Update multiple records
session.query(User).filter(User.is_active == False).update({
    'is_active': True
})
session.commit()

Delete

from models import User, session

# Method 1: Get object, delete
user = session.query(User).filter(User.username == 'Wilson').first()
if user:
    session.delete(user)
    session.commit()

# Method 2: Delete query
session.query(User).filter(User.username == 'bob').delete()
session.commit()

# Delete multiple
session.query(User).filter(User.is_active == False).delete()
session.commit()

---

Relationships

One-to-Many

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, Mapped, mapped_column

class Owner(Base):
    __tablename__ = 'owners'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    # Relationship: one owner has many pets
    pets: Mapped[list["Pet"]] = relationship("Pet", back_populates="owner")

class Pet(Base):
    __tablename__ = 'pets'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    species: Mapped[str] = mapped_column(String(50))

    # Foreign key to owner
    owner_id: Mapped[int] = mapped_column(ForeignKey('owners.id'))

    # Relationship: many pets belong to one owner
    owner: Mapped["Owner"] = relationship("Owner", back_populates="pets")

# Usage
owner = Owner(name="Wilson")
pet1 = Pet(name="Buddy", species="Dog")
pet2 = Pet(name="Whiskers", species="Cat")

owner.pets.append(pet1)
owner.pets.append(pet2)

session.add(owner)
session.commit()

# Access relationship
print(owner.pets)  # [Pet(Buddy), Pet(Whiskers)]
print(pet1.owner)  # Owner(Wilson)

Many-to-Many

from sqlalchemy import Table, Column, Integer, ForeignKey

# Association table
student_course = Table(
    'student_course',
    Base.metadata,
    Column('student_id', Integer, ForeignKey('students.id')),
    Column('course_id', Integer, ForeignKey('courses.id'))
)

class Student(Base):
    __tablename__ = 'students'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    # Many-to-many relationship
    courses: Mapped[list["Course"]] = relationship(
        "Course",
        secondary=student_course,
        back_populates="students"
    )

class Course(Base):
    __tablename__ = 'courses'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    # Many-to-many relationship
    students: Mapped[list["Student"]] = relationship(
        "Student",
        secondary=student_course,
        back_populates="courses"
    )

# Usage
student = Student(name="Wilson")
course1 = Course(name="Python 101")
course2 = Course(name="Web Development")

student.courses.append(course1)
student.courses.append(course2)

session.add(student)
session.commit()

# Access relationship
print(student.courses)  # [Course(Python 101), Course(Web Development)]
print(course1.students)  # [Student(Wilson)]

Association Object (Advanced Many-to-Many)

from datetime import datetime

class Enrollment(Base):
    """Association object with extra data"""
    __tablename__ = 'enrollments'

    id: Mapped[int] = mapped_column(primary_key=True)
    student_id: Mapped[int] = mapped_column(ForeignKey('students.id'))
    course_id: Mapped[int] = mapped_column(ForeignKey('courses.id'))

    # Extra fields
    enrolled_date: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
    grade: Mapped[str] = mapped_column(String(2), nullable=True)

    # Relationships
    student: Mapped["Student"] = relationship("Student", back_populates="enrollments")
    course: Mapped["Course"] = relationship("Course", back_populates="enrollments")

class Student(Base):
    __tablename__ = 'students'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    enrollments: Mapped[list["Enrollment"]] = relationship(
        "Enrollment",
        back_populates="student"
    )

class Course(Base):
    __tablename__ = 'courses'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    enrollments: Mapped[list["Enrollment"]] = relationship(
        "Enrollment",
        back_populates="course"
    )

# Usage
student = Student(name="Wilson")
course = Course(name="Python 101")
enrollment = Enrollment(student=student, course=course, grade="A")

session.add(enrollment)
session.commit()

---

Advanced Queries

Filtering with Relationships

# Get all pets owned by Wilson
pets = session.query(Pet).join(Owner).filter(Owner.name == 'Wilson').all()

# Get all students enrolled in Python 101
students = session.query(Student).join(
    student_course
).join(Course).filter(Course.name == 'Python 101').all()

# Count pets per owner
from sqlalchemy import func

results = session.query(
    Owner.name,
    func.count(Pet.id).label('pet_count')
).join(Pet).group_by(Owner.name).all()

Aggregations

from sqlalchemy import func

# Count
user_count = session.query(func.count(User.id)).scalar()

# Average
avg_age = session.query(func.avg(User.age)).scalar()

# Min/Max
min_age = session.query(func.min(User.age)).scalar()
max_age = session.query(func.max(User.age)).scalar()

# Group by
results = session.query(
    User.grade,
    func.count(User.id)
).group_by(User.grade).all()

Subqueries

# Subquery for average age
avg_age_subquery = session.query(func.avg(User.age)).scalar_subquery()

# Find users older than average
older_users = session.query(User).filter(User.age > avg_age_subquery).all()

---

Best Practices

Session Management

# Use context manager for automatic cleanup
from contextlib import contextmanager

@contextmanager
def get_session():
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()

# Usage
with get_session() as session:
    user = User(username='Wilson', email='Wilson@example.com')
    session.add(user)
# Auto-commits and closes

Error Handling

from sqlalchemy.exc import IntegrityError

try:
    new_user = User(username='Wilson', email='Wilson@example.com')
    session.add(new_user)
    session.commit()
except IntegrityError as e:
    session.rollback()
    print("User already exists or constraint violated")
except Exception as e:
    session.rollback()
    print(f"Error: {e}")
finally:
    session.close()

Query Performance

# Eager loading (prevent N+1 queries)
from sqlalchemy.orm import joinedload

# Load owner with pets in single query
owners = session.query(Owner).options(joinedload(Owner.pets)).all()

# Select only needed columns
users = session.query(User.username, User.email).all()

# Use pagination
page = 1
per_page = 20
users = session.query(User).limit(per_page).offset((page - 1) * per_page).all()

---

See Also

• OOP Cheat Sheet - Classes for SQLAlchemy models
• Error Handling Cheat Sheet - Database error handling
• File Operations Cheat Sheet - Database configuration files
• Testing and Debugging Cheat Sheet - Testing database operations

---