SQLAlchemy & Alembic

✅ Objectives

• Understand what SQLAlchemy is and how it’s beneficial as an ORM
• Know how to create a database with SQLAlchemy
• Create schema
• Understand what Alembic is and how it works with SQLAlchemy
• Configure an app to use Alembic
• Create and apply migrations
• Execute full CRUD using SQLAlchemy

What is SQLAlchemy?

“SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.”

What is a schema?

A schema is the blueprint of a database. It describes how data relates to other data in tables, columns, and the relationships between

We define our schema with Python classes we will call “models”.

First we import the elements we’ll need from SQLAlchemy…

from sqlalchemy import Column, Integer, PrimaryKeyConstraint, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Then we create a model class which inherits the ORM functionality from the Base class.

class Pet(Base):
    __tablename__ = "pets"

    id = Column(Integer(), primary_key=True)
    name = Column(String())
    species = Column(String())
    breed = Column(String())
    temperament = Column(String())

For debugging and readability, let’s add a __repr__() to our model!

class Pet(Base):

  # (...model logic...)

  def __repr__(self):
    return (
            f"Id: {self.id}, "
            + f"Name:{self.name}, "
            + f"Species {self.species}, "
            + f"Breed {self.breed}, "
            + f"Species {self.temperament}"
        )

Let’s jump into the code!

What is an engine?

In SQLAlchemy, an engine is a Python object that translates SQL to Python and vice-versa. It is similar to our connection object in sqlite3. We’ll need an engine to apply our schema to our database.

What is a session?

A Python object that uses an engine to allow us to programmatically interact with a database.

What is Alembic?

A database migration tool written by the author of SQLAlchemy