To run the examples, change the CONNECTION_STRING in cheatsheet/__init__.py. Also change the connection string in cheatsheet/alembic/alembic.ini for running Alembic migrations. mysql-connector is included as dependency for running the examples against MySQL or MariaDb.
This project uses Poetry to manage dependencies, so install it and then run:
poetry shellThen apply Alembic migrations:
cd cheatsheet && alembic upgrade headNote: to run the commands in this project, switch to cheatsheet directory:
cd cheatsheetWhen beginning a new project, Alembic needs to be initialized. It will create alembic_version database table with alembic_num column to store information about applied migrations:
alembic init alembicAfter that, a migration based on changes in the SQLAlchemy model can be generated ("migration" would become the name for the migration):
alembic revision --autogenerate -m "migration"The new migration file will be stored in alembic/versions as a Python file and can be further modified before it is applied. It is a good idea to review the changes, since Alembic might not pick up all changes perfectly.
Generated migrations needs to be applied to the database. Alembic will only apply migrations that haven't been applied before:
# head refers to the latest migration,
# but we can provide a different "target" migration here
alembic upgrade headIn case Alembic doesn't work well for us, we can use a simple migration tool nomad that works with plain Python scripts and SQL files.
One possible workflow could be to use Alembic to only generate initial migration SQL based on the diff between our models and a database, but store and run migrations using nomad.
Session is basic object that is tied to our SQL connection and will allow us to run various ORM commands as a Unit of Work.
Session is created once in cheatsheet/__init__.py like this:
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
Session = sessionmaker()
engine = create_engine(CONNECTION_STRING)
Session.configure(bind=engine)There are two basic ways to define our models in SQLAlchemy:
- subclassing from
Baseclass - using classical mapping through
MetaDataobject
Relationships are defined using relationship function.
When we define relationships, we need to choose a loading strategy that SQLAlchemy will use to load relevant objects into memory. There are two basic loading strategies:
- lazy loading (default)
- eager loading
Column properties can be used for automatically computed columns.
For instance, if we have a Poll with multiple Voters, we can automatically expose the number
of voters in the Poll object:
import sqlalchemy as sa
from sqlalchemy import select, func
from sqlalchemy.orm import column_property
from sqlalchemy.sql.functions import coalesce
class Voter(Base):
id = Column(sa.BigInteger, autoincrement=True, primary_key=True, index=True)
poll_id = Column(sa.BigInteger, ForeignKey("polls.id"), nullable=False)
class Poll(Base):
id = Column(sa.BigInteger, autoincrement=True, primary_key=True, index=True)
voters = sa.relationship("Voter", backref="poll")
# coalesce will handle the situation where there aren't any
# voters yet associated with the poll
n_voters = column_property(
select([coalesce(func.count(Voter.id), 0)])
.correlate_except(Voter)
)