Getting Started with SQLAlchemy

SQLAlchemy is an Object Relational Mapper (ORM) for Python. Example use cases are standalone SQL access in Python, Flask-SQLAlchemy for better SQL integration in Flask and Alembic for database migrations.

If you haven’t used an ORM, or have been advised to avoid them, I highly recommend you give it a try. A little bit of initial setup opens up a brand new world of SQL accessibility. Because of how easy it is to use, it’s also easy to write bad or under performant code/SQL. It’s incredibly important to understand the SQL getting created under the hood and what results are getting returned from the server. But well organized, well written SQLAlchemy is a dream to use and will tremendously benefit your code base.

Initial Connectivity

SQLAlchemy has two main components: the core and the ORM. This article will focus on the ORM.

To begin, we’ll be accomplishing 3 things:

1. Establish a database session. This example will use sqlite.
2. Create the declarative base and table models.
3. Instruct the database engine to build the tables defined in the declarative base.

We’ll need some models to work with to show off this functionality. For familiarity and simplicity we’ll use a group of people signing up for some activities:

from sqlalchemy import create_engine, Column, Date, ForeignKey, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
# Setup the back-end sql engine. In this case, sqlite
# Create and instantiate a session class using the defined engine
engine = create_engine('sqlite:///relationships.db', echo=True)
DBSession = sessionmaker(bind=engine)
session = DBSession()
# Initialize a new declarative base which will manage the `Table`
# objects and all the behind the scenes work for the models
Base = declarative_base()
# Inheriting from our `Base`, define each table/model
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
class Activity(Base):
    __tablename__ = 'activity'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
class Enrollment(Base):
    __tablename__ = 'enrollment'
    id_student = Column(ForeignKey(Student.id), primary_key=True)
    id_class = Column(ForeignKey(Activity.id), primary_key=True)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date)
    # Because the foreign key is on this table, SQLAlchemy knows
    # these are one to many relationships with the backref being the many
    student = relationship(Student, backref='enrollments')
    activity = relationship(Activity, backref='enrollments')
# Create the tables defined in the base.
Base.metadata.create_all(engine)

This will let us work with 3 tables, including a secondary table for a little more fun. A few words regarding some naming decisions in this example:

• I’m a fan of singular table names, because when creating new records, Student(name=‘Bob’) sounds a lot better than Students(name=‘Bob’)
• I like to prefix foreign keys with id_ followed by the tablename. If there are multiple foreign keys, then some description. e.g. id_user_created and id_user_deleted for two separate foreign keys to a theoretical user table. The relationships would have the same name, sans id_.
• Note that the session hasn’t actually been used yet. The Base does not depend upon a session nor the engine. The engine was only used when issuing the command to create the tables defined in the Base. A session will only come in to play once records are added with the intent to be committed and when those records are later accessed via queries.

Creating Data

Now that the models have been defined, creating data is as simple as working with any Python class. To create a new student or activity is simply a matter of initializing a new Student and Activity:

alfred = User(name='Alfred')
directing = Activity(name='Directing')

Those records can now be added and committed to the database session:

session.add_all([alfred, directing])
session.commit()

Which will produce the following:

BEGIN (implicit)
INSERT INTO activity (name) VALUES (?)
('Directing',)
INSERT INTO student (name) VALUES (?)
('Alfred',)
COMMIT

Congratulations!

Next we will want to associate alfred with his favorite activity, writing. Traditionally you would either need to use INSERT ... RETURNING id or SELECT currval(sequence) to get the ids of the records just inserted to populate the foreign keys. That way madness lies. Just use the relationships defined and let SQLAlchemy handle the rest:

from datetime import date
Enrollment(
    student=alfred,
    activity=directing,
    start_date=date(1922, 1, 1)
)
session.commit()

Will automatically add the enrollment to the session because its relationships are in the session, and it will handle the foreign key ids for you:

INSERT INTO enrollment (id_student, id_class, start_date, end_date) VALUES (?, ?, ?, ?)
(1, 1, '1922-01-01', None)
COMMIT

Because the relationships were defined with backrefs the whole way through, the same enrollment could have been created from alfred or from activity as well:

alfred.enrollments.append(
    Enrollment(activity=directing, start_date=date(1922, 1, 1)
)
directing.enrollments.append(
    Enrollment(student=alfred, start_date=date(1922, 1, 1))
)

Note that alfred and directing both had lists for their enrollments. Enrollment has one-to-many relationships extending from it, so the backrefs from Student and Activity would both have many-to-one relationships to Enrollment hence they will be lists. Or more accurately, InstrumentedLists.

Querying and Accessing Data

What good is data in a database if you can’t read it? Querying in SQLAlchemy is incredibly easy:

students = session.query(Student).all()
for student in students:
    print(student.name)
> Alfred

If you want to add some filters, you can also do that:

alfred = session.query(Student).filter_by(name='Alfred').all()

And if we want to know every activity that Alfred is enrolled in:

for enrollment in alfred.enrollments:
    print(f"{alfred.name} enrolled in {enrollment.activity.name} on {enrollment.start_date.strftime('%m/%d/%Y')}")
> Alfred enrolled in Directing on 01/01/1922

Anything you can do in queries in SQL, you can also do in SQLAlchemy. Including window functions, unions, group by, subqueries, etc…

You may be tempted to be smarter than SQLAlchemy and modify your query to only query for the data you need:

session.query(Student.name)

But please take caution when doing so. This seriously hampers a lot of SQLAlchemy’s strengths on tracking objects for you and unless you know exactly what you’re doing, will at best have the same performance, and at worst will slow down your application and make it more complicated at the same time.

Changing and Deleting Data

Let’s add an end to Alfred’s directing:

alfred.end_date = date(1976, 4, 9)

And that’s it. The data has only been modified locally. A session.flush() will push it up to the current database session and a session.commit() will commit it. Any data can be modified in the same way.

If we want to delete alfred and everything he stands for, that can be done via the session as well:

for enrollment in alfred.enrollments:
    session.delete(enrollment)
session.delete(alfred)
session.commit()

Note that with how the relationships have been configured, the enrollments need to be explicitly deleted as well. Otherwise SQLAlchemy would have updated each Enrollment to have a null id_student which is part of the primary key, which is a huge no-no. If you see an error that looks like:

Dependency rule tried to blank-out primary key column '<table>.<column>' on instance <Model>

Then that’s the problem you’re encountering. To fix this, you can let SQLAlchemy know that Enrollment is not to be orphaned by the removal of a Student. The Student.enrollments (which is the backref of Enrollment.student) relationship can be configured to delete orphans:

student = relationship(Student, backref=backref('enrollments', cascade='all, delete-orphan'))

Which will then delete any related Enrollment rather than nulling out id_student which will allow a simple deletion of alfred:

session.delete(alfred)
session.commit()
DELETE FROM enrollment WHERE enrollment.id_student = ? AND enrollment.id_class = ?
(1, 1)
DELETE FROM student WHERE student.id = ?
(1,)

With this information, you should now know how to:

• Setup a new database connection
• Define your database models
• Complete CRUD on your data

Thank you for your time, and I hope this article helps get you started on your new journey with SQLAlchemy. Good luck!

Ready for more? Proceed to Relationships in SQLAlchemy.