Object-Relational Mappers (ORMs)

To understand the role that SQLAlchemy plays, we first need to understand the role of an ORM.

Thus far, we have worked a fair bit with SQL queries. SQL, like other languages we're familiar with, has its own distinct rules and ways of composing statements.

For example, take the following:

INSERT INTO pets (name, species, hunger) 
       VALUES ('Whiskey', 'dog', 50);
SELECT hunger FROM pets WHERE name = 'Whiskey';
UPDATE pets SET hunger WHERE name = 'Whiskey';

Unless we're working with SQL on a daily basis, the above wouldn't be necessarily difficult for us to produce, but it would definitely require a bit of a context switch if we're used to always working in an object-oriented manner.

This is where ORMs come in!

ORMs allow us to write code in an object-oriented manner, very similar to the other languages that we've used thus far. As a result, we don't have to context switch when communicating with our relational database.

Let's implement the same SQL statements from above to work with our ORM:

whiskey = Pet(name='Whiskey', species="dog", hunger=50)
whiskey.hunger # 50
whiskey.hunger = 20

Though it looks just like the Python we're working with on our server, our ORM translates the above and communicates its meaning to our relational database in a way that it will understand.

In other words, it maps our object-oriented code to the relational database we're dealing with.

And that is the purpose of an Object-Relational Mapper!

Installing SQLAlchemy

For our purposes, since we've been working in Flask, we're going to install two dependencies: flask-sqlalchemy and psycopg2.

psycopg2 allows us to use Python to speak PostgreSQL.

(venv) $ pip3 install psycopg2-binary
(venv) $ pip3 install flask-sqlalchemy

SQLAlchemy Setup

To set up our ORM to establish communication with our relational database, we must first setup and configure our application to do so.

At the top of our models.py file, we'll place the following code:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


def connect_db(app):
    """Connect to database."""

    db.app = app
    db.init_app(app)

In app.py, we import both db and the connect_db function we just created from models.py (line 2), executing the connection only after we've set up some configuration (line 9):

from flask import Flask, request, redirect, render_template
from models import db, connect_db, Pet

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///sqla_intro'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True

connect_db(app)

There are more configuration options available, but those included above serve the following purposes:

1. SQLALCHEMY_DATABASE_URI. The database URI that we are connecting to.
2. SQLALCHEMY_TRACK_MODIFICATIONS. Track changes that are committed to the database.
3. SQLALCHEMY_ECHO. Logs out all the statements issued (helpful for debugging).

In our case, we are using PostgreSQL, but SQLAlchemy can also be used to communicate with other databases, such as MySQL, SQLite and Oracle.

Now that we've setup SQLAlchemy to communicate with our database, let's start creating some models.

Models

In SQLAlchemy, when we create a model, we are indicating that we'd like to create a table in our database.

Writing a model is similar to writing classes as we've been doing so far in Python, with a few extra considerations.

To create a model, we must extend from a base class provided by SQLAlchemy called db.Model.

Within our new model, every Column object that we declare will create a corresponding column in that table. Since the name of the variable we set our db.Column to is what our column name will be, be sure to name them mindfully!

Let's take a look at an example below for our Pet model, whose resulting table will have the columns id, name, species and hunger:

class Pet(db.Model):
    """Pet."""

    __tablename__ = "pets"

    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    name = db.Column(db.String(50),
                     nullable=False,
                     unique=True)
    species = db.Column(db.String(30), nullable=True)
    hunger = db.Column(db.Integer, nullable=False, default=20)

When translated by our ORM to SQL, the above code produces the following table schema:

CREATE TABLE pets (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    species VARCHAR(30),
    hunger INTEGER NOT NULL
)

Columns

As we've covered, every Column defined in our Model will correspond to a column in our resulting table.

Column Type

For each column, we must first pass in the column type, prepended with our db. This type indicates to SQL what kind of data will be stored as values.

The following types are available for use: Integer, String(size), Text, DateTime, Float, Boolean, PickleType, and LargeBinary.

Column Configuration

Any additional arguments passed in after the Column type are used to configure the behavior of that column.

The available keys to pass in include: default, unique, nullable, primary_key, and autoincrement.

The behaviors of each are as follows:

1. default. Provides a default value in the case that no value is provided when a row is added or updated.
2. unique. Ensures that there are no duplicate column values across all rows.
3. nullable. Allows (or disallows) NULL values. If omitted, NULL values will be allowed by default.
4. primary_key. Marks a column as a Primary Key. Primary Key columns are NOT NULL, but it's still a good idea to explicitly do so using nullable=False.

Creating Our Database (from Models)

In our terminal, using python3, we'll now run the app.py file we just created:

$ ipython3
>>> In [1] %run app.py
>>> In [2] db.create_all()

Remember that with our %run app.py command, we now have access to the members of that file. This allows us to execute db.create_all() manually from within our terminal.

In the case that our tables already exist and we run this again, nothing will happen.

If we make any alterations to our model and/or the schemas defined within them, we must drop our table and rerun the db.create_all() method to see our changes take effect.

Using Models

Creating / INSERT

To create a new record within our table, we have to create an instance of whatever model we're working with.

In the case below, we're creating a new Pet:

>>> fluffy = Pet(name='Fluffy', species="Pet")
>>> fluffy.hunger
20

>>> db.session.add(fluffy)    # required to add to database!
>>> db.session.commit()       # commit the transaction

After we create our instance, we must then add and commit the addition to our database.

Querying / SELECT

To return all records from a table:

Pet.query.all()

To return a specific record, using the Primary Key (in this case, id):

Pet.query.get(1)

From all records, return only those records where the column species has a value of 'dog':

Pet.query.filter_by(species='dog').all()

Alternatively, we could also write:

Pet.query.filter(Pet.species == 'dog').all()

How do we choose between using .filter_by() and .filter()?

We'll most often reach for .filter() when we're writing more complex types of conditions:

Pet.query.filter(Pet.hunger < 15,
                 Pet.species == 'dog').all()

The code above's SQL corresponds to the following:

SELECT *
FROM pets
WHERE hunger < 15
  AND species = 'dog'

Fetching Records

Thus far, we've ended all of our queries with .all(), but we have several other methods at our disposal:

1. .get(pk) Get a single record with that Primary Key value
2. .all() Get all records as a list
3. .first() Get the first record or None
4. .one() Get the first record, error if 0 or if > 1
5. .one_or_none() Get the first record, error if >1, None if 0

Methods

Just like the classes we've worked with before SQLAlchemy, we can also add methods to our models that, in turn, make changes to that record in our database.

Let's bring back our Pet class:

class Pet(db.Model):
    """Pet."""

    __tablename__ = "pets"

    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    name = db.Column(db.String(50),
                     nullable=False,
                     unique=True)
    species = db.Column(db.String(30), nullable=True)
    hunger = db.Column(db.Integer, nullable=False, default=20)

    def greet(self):
        """Greet using name."""

        return f"I'm {self.name} the {self.species or 'thing'}"

    def feed(self, units=10):
        """Nom nom nom."""

        self.hunger -= units
        self.hunger = max(self.hunger, 0)

To use our new methods, we treat them the same way we would any other class method:

>>> fluffy.greet() # 'I am Fluffy the cat'
>>> fluffy.feed() # add 10 units to hunger
>>> fluffy.hunger
30
>>> db.session.commit()

We must remember, however, that we have to commit that modifications that we make to our class model instances.

If it's the case that we'd like to add a static method to the class itself:

class Pet(db.Model):
    # .. truncated for display
    @classmethod
    def get_by_species(cls, species):
        """Get all pets matching that color."""

        return cls.query.filter(Pet.species == species).all()

Improving Representation of Returned Instances

Currently, with the Pet model we've written, if we query all records, we'll be returned the following:

>>> Pet.query.filter(Pet.species == 'dog').all()
[<__main__.Pet object at ...>, <__main__.Pet object at ...>]

This isn't great: there is no meaningful information that we can get from just glancing at this.

Luckily, we know that we can alter the __repr__ in Python to provide us something better!

Back in our Pet model, we add in a custom __repr__:

class Pet(db.Model):   # ...
    def __repr__(self):
        """Show info about pet."""

        p = self
        return f"<Pet {p.id} {p.name} {p.species} {p.hunger}>"

And the next time we run a query:

>>> Pet.query.get(1)
<Pet 1 Whiskey dog 10>

Much better!