Implement ORM Data Models with SQLAlchemy
Tags: sqlalchemy, python, sql, software
Published: 2022-05-15 17:38:00

---
title: Implement ORM Data Models with SQLAlchemy
published: true
date: 2019-01-24 22:00:00 UTC
tags: SQLAlchemy,Python,SQL,Software
series: Mastering SQLAlchemy
canonical_url: https://hackersandslackers.com/implement-sqlalchemy-orm/
---

![Implement ORM Data Models with SQLAlchemy](https://hackersandslackers-cdn.storage.googleapis.com/2021/01/sqlalchemy-models.jpg)

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:

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

Base = declarative_base()
```
<figcaption>models.py</figcaption>

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:

```python
"""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
    )
```
<figcaption>models.py</figcaption>

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

<!--kg-card-begin: html-->

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.

<!--kg-card-end: html-->

With all that knowledge, we can continue building out our model:

```python
...
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
```
<figcaption>models.py</figcaption>

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.

<!--kg-card-begin: html-->

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.

<!--kg-card-end: html-->

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:

```python
Base.metadata.create_all(engine)
```
<figcaption>Create SQL tables from classes which extend <code>Base</code></figcaption>

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:

```sql
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](https://hackersandslackers.com/python-database-management-sqlalchemy) 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:

```python
"""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()
```
<figcaption>database.py</figcaption>

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:

```python
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
```
<figcaption>Adding a new user via a session</figcaption>

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:

```python
...

session.delete(new_user)
session.commit()
```
<figcaption>Delete a record</figcaption>

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:

{% github https://github.com/hackersandslackers/sqlalchemy-tutorial %}