Databases — SQLAlchemy & SQLite/PostgreSQL
Define models with SQLAlchemy 2.0 ORM, write async queries, manage migrations with Alembic, and integrate with FastAPI.
Part 1: What You Will Learn
- Define SQLAlchemy 2.0 ORM models.
- Create a SQLite database and tables.
- Insert, query, update, and delete rows with a
Session. - Know how the same ORM model can move to PostgreSQL and how Alembic fits into schema migrations.
Part 2: Key Concepts
An ORM maps Python classes to database tables. SQLAlchemy 2.0 uses typed mapped attributes and sessions to track database work. SQLite is ideal for a self-contained learning project; PostgreSQL can be introduced later by changing the database URL and installing its driver.
Part 3: Topic-Specific Code Examples
# Install once: pip install sqlalchemy
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class Student(Base):
__tablename__ = "students"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(100))
course: Mapped[str] = mapped_column(String(80))
mark: Mapped[int]
def __repr__(self) -> str:
return f"Student(id={self.id}, name={self.name!r}, mark={self.mark})"
engine = create_engine("sqlite:///students.db", echo=False)
Base.metadata.create_all(engine)
with Session(engine) as session:
# CREATE
student = Student(name="Aisha", course="Python", mark=88)
session.add(student)
session.commit()
# READ
result = session.scalars(
select(Student).where(Student.mark >= 80)
).all()
print("High marks:", result)
# UPDATE
student.mark = 92
session.commit()
# DELETE
# session.delete(student)
# session.commit()SQLite: sqlite:///students.db PostgreSQL example: postgresql+psycopg://user:password@localhost/studentdb Alembic migration workflow: pip install alembic alembic init migrations alembic revision --autogenerate -m "create students" alembic upgrade head
Part 4: How the Example Works
DeclarativeBase is the base for mapped classes. Mapped[...] annotations describe Python and database field types. A Session groups changes into a transaction; commit() persists them. Keep the database URL in configuration or environment variables instead of hard-coding real credentials.
Part 5: Hands-On Practice
Mini project — Product Database. Add a Product model with name, category, price, and stock. Write functions to add a product, list products by category, update stock, and delete a product. Then inspect the generated SQLite database file.
Part 6: Next Steps
Run and modify the examples in Visual Studio 2026, then continue to Lesson 27. Return to Python Tutorial Home to review the complete curriculum.