Implement an ORM with SQLAlchemy
Utilizing ORMs as a data layer is a concept as old as object-oriented programming itself; by abstracting SQL concepts, developers avoid dreaded "context switches" by modifying objects instead of queries. ORMs aren't merely an artifact of the slowly-dwindling era of overzealous OOP; they guarantee a level of durability by ensuring data integrity on the application side, thus minimizing the possibility of catastrophic SQL queries.
Instead of executing SQL queries against a database, ORMs allow developers to handle data by modifying objects in code (data classes). This workflow pays off for user-facing applications that regularly verify and modify data (think authenticating users, modifying profiles, posting content, etc.). ORMs shine by providing an interface for handling operations that are both frequent and predictable; this is essential for application development, but surely a burden for anything involving data analysis.
Creating a Model
Data models are Python classes representing a SQL table in our database, where attributes of a model translate to columns in a table.
When working with ORMs, creating instances of our models translate to creating rows in a SQL table. Naturally, this means we need to define our models before we can write any meaningful business logic.
We create models by defining Python classes that extend something from SQLAlchemy called a
declarative_base(). We define a model "base" object as such:
Now we can extend
Base to create our first model. As is tradition, our first model will be a model representing user accounts aptly named User. We'll start simple:
While it may be barebones, the above Python class is a fully valid data model that would result in a table with three columns (one per attribute): id, username, and password. Each of these attributes has the type
Column(), a data structure unique to SQLAlchemy (hence our inclusion of
from sqlalchemy import Column).
We also imported three SQLAlchemy "types," which we see getting passed into each
Column. Each type corresponds to a SQL data type. Thus, our SQL table columns' data types would be integer, varchar(255), and text, respectively.
Columns can also accept optional parameters to things like keys or column constraints:
- primary_key: Designates a column as the table's "primary key," a highly recommended practice that serves as a unique identifier as well as an index for SQL to search on.
- autoincrement: Only relevant to columns which are both the
primary_keyas well as have the type
Integer. Each user we create will automatically be assigned an id, where our first user will have an id of 1, and subsequent users would increment accordingly.
- unique: Places a constraint where no two records/rows share the same value for the given column (we don't want two users to have the same username).
- nullable: When set to
True, adds a constraint that the column is mandatory, and no row will be created unless a value is provided.
- key: Places a secondary key on the given column, typically used in tandem with another constraint such as "index."
- index: Designates that a column's values are sortable in a non-arbitrary way in the interest of improving query performance
- onupdate: Automatically assigns a value to the column via a Python function whenever a row is created or updated.
- server_default: A default value to assign if a value is not explicitly passed.
In our example, we set the optional attribute
__tablename__ to explictly specify what model's corresponding SQL table should be named. When not present, SQL will use the name of the class to create the table.
With all that knowledge, we can continue building out our model:
Now that's a model! We added a few attributes to our model, each of which is self-explanatory. The
updated_at columns demonstrate the usage of SQLAlchemy functions to assign these values automatically.
It's best practice to set the value of
__repr__ on data models (and Python classes in general) for the purpose of logging or debugging our class instances. The value returned by
__repr__ is what we'll see when we
print() an instance of
User. If you've ever had to deal with
Our model is looking good, so let's create a SQL table out of it. We do this by invoking a method called
create_tables() after our models are created:
Once that runs, SQLAlchemy handles everything on the database side to create a table matching our model. In case you're curious, this is what our
User model outputs:
CREATE TABLE "user" ( "id" int NOT NULL AUTO_INCREMENT, "username" varchar(255) NOT NULL, "password" text NOT NULL, "email" varchar(255) NOT NULL, "first_name" varchar(255) DEFAULT NULL, "last_name" varchar(255) DEFAULT NULL, "bio" text, "avatar_url" text, "last_seen" datetime DEFAULT NULL, "created_at" datetime DEFAULT CURRENT_TIMESTAMP, "updated_at" datetime DEFAULT NULL, PRIMARY KEY ("id"), UNIQUE KEY "username" ("username"), UNIQUE KEY "email" ("email") );
Creating a Session
A session is a persistent database connection that lets us add, remove, change, and even undo changes with ease. We're going to use the
User model we just created to create new users via a database session.
Sessions are created by binding them to an SQLAlchemy engine, which we covered in Part 1 of this series. With an engine created, all we need is to use SQLAlchemy's
sessionmaker to define a session and bind it to our engine:
That's all it takes! We'll use
session in tandem with our
User model to create some users.
Creating Records with Models & Sessions
With a model defined and session created, we have the luxury of adding and modifying data purely in Python. SQLAlchemy refers to this as function-based query construction. Let's see what it would take to create a new user from our
With an instance of
User created and saved as variable
new_user, all it takes to create this user in our database are are two calls to our session:
add() queues the item for creation, and
commit() saves the change. We should now see a row in our database's user table!
session is as easy as four simple methods:
session.add(): We can pass an instance of a data model into
add()to quickly create a new record to be added to our database.
session.delete(): Like the above,
delete()accepts an instance of a data model. If that record exists in our database, it will be staged for deletion.
session.commit(): Changes made within a session are not saved until explicitly committed.
session.close(): Unlike SQLAlchemy engines, sessions are connections that remain open until explicitly closed.
The syntax for deleting a record closely matches that of creation. With the
user variable in hand, deleting the user we created is as simple as the below:
Like magic, we deleted a record that matched the record we created earlier by simply passing
new_user into a the delete method. Don't just take my word for it: the most satisfying part of this process is watching it happen with your own eyes! Try connecting to your database with your GUI of choice and watch as records are created and deleted with each line of code you run. Neat!
Wax On, Wax Off
Creating and modifying records is only the beginning of your ORM journey with SQLAlchemy. We still need to be able to fetch the records we've created, after all! We'll be covering the joy of fetching rows via data models in part 3, so don't touch that dial!
Anyway, you can grab the working source code for this tutorial from Github below. Source code for each chapter in this series can be found there: