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:

"""SQLAlchemy Data Models."""
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

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:

"""SQLAlchemy Data Models."""
from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Integer, Text, String


Base = declarative_base()

class User(Base):
    """User account."""

    __tablename__ = "user"

    id = Column(
        Integer,
        primary_key=True,
        autoincrement="auto"
    )
    username = Column(
        String(255),
        unique=True,
        nullable=False
    )
    password = Column(
        Text,
        nullable=False
    )

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_key as 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
• 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:

...
from sqlalchemy.types import Integer, Text, String, DateTime
from sqlalchemy.sql import func

class User(Base):
    """User account."""

    __tablename__ = "user"

    id = Column(
        Integer,
        primary_key=True,
        autoincrement="auto"
    )
    username = Column(
        String(255),
        unique=True,
        nullable=False
    )
    password = Column(
        Text,
        nullable=False
    )
    email = Column(
        String(255),
        unique=True,
        nullable=False
    )
    first_name = Column(String(255))
    last_name = Column(String(255))
    bio = Column(Text)
    avatar_url = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now())

    def __repr__ (self):
        return "<User %r>" % self.username

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:

Base.metadata.create_all(engine)

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:

"""Database engine & session creation."""
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    'mysql+pymysql://user:password@host:3600/database',
    echo=True
)
Session = sessionmaker(bind=engine)
session = Session()

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:

from models import User
from database import session

user = User(
    username="admin",
    password="Please don't set passwords like this",
    email="admin@example.com",
    first_name="Todd",
    last_name="Birchard",
    bio="I write tutorials on the internet.",
    avatar_url="https://example.com/avatar.jpg"
)

session.add(user) # Add the user
session.commit() # Commit the change

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 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:

...

session.delete(new_user)
session.commit()

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:

$ git clone https://github.com/hackersandslackers/sqlalchemy-tutorial.git
$ cd sqlalchemy-tutorial
$ make deploy