Defining Schema in SQLAlchemy ORM

SQLAlchemy ORM

SQLAlchemy ORM (Object Relational Mapper) is a way to define tables and relationship between them using Python classes. It also provides a system to query and manipulate the database using object-oriented code instead of writing SQL. Unlike SQLAlchemy Core, which is focused around tables, rows and columns; the ORM is focused around objects and models.

ORM is built upon the SQLAlchemy Core, so everything you have learned so far still applies.

As you will see, an ORM allows you to be more productive but it also adds additional overhead to your queries. However, for most applications, its benefits far outweighs the performance penalty.

Note: Before moving on to the next section delete all the tables from the sqlalchemy-tuts database using the following command:

Defining Models

A Model is a Python class which corresponds to the database table and its attributes represent the columns.

For the class to be a valid model, it must do the following:

  1. Inherit from a declarative base class created by calling declarative_base() function.
  2. define table name via __tablename__ attribute.
  3. define at-least one column which must be a part of the primary key.

The last two points are self-explanatory but the first one deserves a bit of explanation.

The base class maintains a catalog of classes and tables. In other words, the declarative base class wraps the mapper and the MetaData. The mapper maps the subclass to the table and MetaData holds all the information about the database and the tables it contains. Just as in Core, in ORM we use create_all() and drop_all() methods of the MetaData object to create and drop tables.

The following listing defines a Post model which can be used to store blog posts.

Let’s step through the code line by line.

  1. In line 1-4, we import necessary classes and functions.
  2. In line 6, we create a base declarative class by calling the declarative_base() function.
  3. In line 10-16, we define columns as class attributes.

Notice that we are using the same Column class to define the columns as we did while using SQLAlchemy Core. The only difference is that now the first argument is type instead of the column name. Also, notice that the keyword arguments passed to the Column() works exactly the same in ORM and Core.

Since ORM is built upon Core, SQLAlchemy will use the model definition to create a Table object and associate it with the model by calling the mapper() function. This completes the process of mapping the Post model with its corresponding Table instance. We can now use Post model to query and manipulate the database.

We can peek at the Table instance associated with the model using the __table__ attribute.

Classical Mappings

After reading the above section you might get an impression that to use SQLAlchemy’s ORM, you will have to rewrite your existing Table instances as Models. However, this is simply not true.

It turns out that you can map any plain Python class to a Table instance using the mapper() function. Here is an example:

The mapper() class takes two arguments: class to be mapped and the Table object.

Just by doing this the Post class will have attributes that corresponds to the columns of the table. Thus, the Post class now has following attributes:

  • post.title
  • post.slug
  • post.content
  • post.published
  • post.created_on
  • post.updated_on

The code in the above listing is functionally equivalent to the Post model we defined earlier.

Now you should have a good idea of what declarative_base() is doing for us behind the scenes.

Adding Keys and Constraints

When using ORM, we add keys and constraints to model using the __table_args__ attribute.

Defining Relationships

One to Many

We create a one-to-many relationship by placing a foreign key on the child class. For example:

The line author_id = Column(Integer, ForeignKey('')) establishes a one-to-many relationship between the Author and Book model.

The relationship() function adds attributes on the models to access the related data. At its minimum, it accepts the name of the class representing the target of the relationship.

The line books = relationship("Book") adds a books attribute to the Author class.

Given an Author object a, we can now access books written by him using a.books.

What if we want to access book’s author from a Book object?

One way to achieve this is to define separate relationship() on Author model like this:

Given a Book object b, we can now access its author as

Alternatively, we can use the backref parameters to specify the attribute name to be added on the other side of the relationship.

Further, you can define relationship() on any side of the relationship. Thus, the preceding code can also be written as:

One to One

Establishing a one-to-one relationship in SQLAlchemy is almost the same as one-to-many relationship, the only difference is that we pass an additional argument uselist=False to the relationship() function. Here is an example:

Given a Person object p, p.dl would return a DriverLicense object. If we hadn’t passed uselist=False to the relationship() function then the relationship between Person and DriverLicense would be one-to-many and p.dl would return a list of DriverLicense objects rather than a single object. The uselist=False argument doesn’t have any effect on the persons attribute of the DriverLicense object. As usual, it will return a Person object.

Many to Many

Creating a many-to-many relationship requires an extra table called an association table or an intermediary table. We define this table as an instance of the Table class and then connect it to the model using the secondary argument of the relationship() function.

An author can write one or more books. Similarly, a book can be written by multiple authors. Thus, there is a many-to-many relationship between author and book.

To represent many-to-many relationship we have created an association table called author_book.

Given an Author object a, we can access all books written by him as a.books. Similarly, given a Book object b, b.authors will return a list of Author objects.

In this case, we have defined relationship() on the Book model, but we could have just as easily defined it in the Author model.

There will be times when you want to store additional data in the association table. To achieve this we have to define association table as a model class.

Creating Tables

As in SQLAlchemy Core, use use create_all() method of the MetaData instance to create the table.

To drop the tables call drop_all method.

We will now re-define the tables using models and persists them to the database by calling the create_all() method. Here is the complete code to do so:

In the next chapter, we will learn how to work with the database using SQLAlchemy ORM.

5 thoughts on “Defining Schema in SQLAlchemy ORM

  1. I think you made a mistake on these lines

    class Book(Base):
    tablename = ‘authors’

    I think it should be

    class Book(Base):
    tablename = ‘books’

  2. Really enjoyed this tutorial! Hands down among the best ones on the subject.

    Possible (minor) typos:

    In the first example on backref, you have:
    books = relationship(“Book”, backref=”book”)

    Shouldn’t it be:
    books = relationship(“Book”, backref=”author”)

    And later when discussing many-to-many relationships, you mention “Given an Author object a, we can access all books written by him as a.books. Similarly, given a Book object b, “b.authors” will return a list of Author objects.”

    author = relationship(“Author”, secondary=author_book, backref=”books”)

    In this case, the variable name above should be “authors” instead of “author” for b.authors to work, no?

Leave a Comment

%d bloggers like this: