DEV Community

Todd Birchard for Hackers And Slackers

Posted on • Originally published at hackersandslackers.com on

Implement ORM Data Models with SQLAlchemy

Implement ORM Data Models 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:

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

Base = declarative_base()
Enter fullscreen mode Exit fullscreen mode
models.py

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
    )
Enter fullscreen mode Exit fullscreen mode
models.py

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
Enter fullscreen mode Exit fullscreen mode
models.py

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)
Enter fullscreen mode Exit fullscreen mode
Create SQL tables from classes which extend Base

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")
);
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode
database.py

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
Enter fullscreen mode Exit fullscreen mode
Adding a new user via a session

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()
Enter fullscreen mode Exit fullscreen mode
Delete a record

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 logo hackersandslackers / sqlalchemy-tutorial

๐Ÿงช๐Ÿ”ฌ Use SQLAlchemy to connect, query, and interact with relational databases.

SQLAlchemy Tutorial

Python SQLAlchemy PyMySQL GitHub Last Commit GitHub Issues GitHub Stars GitHub Forks

SQLAlchemy Tutorial

This repository contains the source code for a four-part tutorial series on SQLAlchemy:

  1. Databases in Python Made Easy with SQLAlchemy
  2. Implement an ORM with SQLAlchemy
  3. Relationships in SQLAlchemy Data Models
  4. 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
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
spaceofmiah profile image
Osazuwa J. Agbonze

Hello, thanks for taking the time to put this together. There's a mistake here though

  • nullable: When set to ~True~, adds a constraint that the column is mandatory, and no row will be created unless a value is provided.

Cheers ๐Ÿป