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.
Column
s 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_key
as well as have the typeInteger
. 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
- server_default : A default value to assign if a value is not explicitly passed.
In our example, we set the optional attribute __tablename__
to explicitly 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 created_at
and 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 [object Object]
in Javascript, you're already familiar with how obnoxious it is to debug an object's value and receive nothing useful in return.
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 CURRENT_TIMESTAMP,
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 User
class:
With an instance of User
created and saved as a 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!
Working with session
is as easy as four simple methods:
-
session.add()
: We can pass an instance of a data model intoadd()
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 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 here:
hackersandslackers / sqlalchemy-tutorial
๐งช๐ฌ Use SQLAlchemy to connect, query, and interact with relational databases.
SQLAlchemy Tutorial
This repository contains the source code for a four-part tutorial series on SQLAlchemy:
- Databases in Python Made Easy with SQLAlchemy
- Implement an ORM with SQLAlchemy
- Relationships in SQLAlchemy Data Models
- Constructing Database Queries with SQLAlchemy
Getting Started
Get set up locally in two steps:
Environment Variables
Replace the values in .env.example with your values and rename this file to .env:
-
SQLALCHEMY_DATABASE_URI
: Connection URI of a SQL database. -
SQLALCHEMY_DATABASE_PEM
(Optional): PEM key for databases requiring an SSL connection.
Remember never to commit secrets saved in .env files to Github.
Installation
Get up and running with make deploy
:
$ git clone https://github.com/hackersandslackers/sqlalchemy-tutorial.git
$ cd sqlalchemy-tutorial
$ make deploy
Hackers and Slackers tutorials are free of charge. If you found this tutorial helpful, a small donation would be greatly appreciated to keep us in business. All proceeds go towards coffee, and all coffee goes towards more content.
Top comments (1)
Hello, thanks for taking the time to put this together. There's a mistake here though
Cheers ๐ป