Skip to content

#89: Constraints, Indexes and Default Values in SQLAlchemy ORM

The right index can boost the performance of your SQL query enormously while constrains help you to enforce the integrity of your data in the database. Let us look how SQLAlchemy can help us with both. I made my examples for SQLAlchemy ORM, but they work in Core as well.

Attention: SQLite needs batch mode

If you try to add constraints to existing tables in SQLite you may get this error in Alembic:

1
2
3
4
5
6
7
8
alembic upgrade head

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 347126fc065d -> 6af8c1fdf679, make ISBN unique
..
NotImplementedError: No support for ALTER of constraints in SQLite dialect Please 
refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy.

You can fix this by activating the batch mode as I explained in the last post.

Primary key constraint

In our models we can use primary_key=True in the table mapping to make a field a primary key:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
import sqlalchemy as sa
from data.modelbase import ModelBase

class Author(ModelBase):
    __tablename__ = 'Author'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    first_name = sa.Column('FirstName', sa.String, nullable=False)
    last_name = sa.Column('LastName', sa.String, nullable=False)
    books = sa.orm.relation("Book", secondary='BookAuthor', back_populates="authors")

    def __repr__(self):
        return f'<Author {self.id} ({self.first_name} {self.last_name})>'

If you need to create a composite primary key with multiple columns you can add primary_key=True to each of them:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
import sqlalchemy as sa
from data.modelbase import ModelBase

class BookAuthor(ModelBase):
    __tablename__ = 'BookAuthor'

    book_id = sa.Column('BookId', sa.Integer, sa.ForeignKey('Book.Id'), primary_key=True)
    author_id = sa.Column('AuthorId', sa.Integer, sa.ForeignKey('Author.Id'), primary_key=True)

    def __repr__(self):
        return f'<BookAuthor {self.book_id} {self.author_id}>'

Foreign key constraint

Foreign keys help us to enforce the relation between two tables. You can do that with ForeignKey('Table.Id') as we did with books that need a publishers:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
import sqlalchemy as sa
from sqlalchemy.sql.schema import UniqueConstraint
from data.modelbase import ModelBase

class Book(ModelBase):
    __tablename__ = 'Book'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    title = sa.Column('Title', sa.String, nullable=False)
    isbn = sa.Column('ISBN', sa.String(13), nullable=False, index=True, unique=True)
    pages = sa.Column('Pages', sa.Integer)
    published_by = sa.Column('PublishedBy', sa.Integer, sa.ForeignKey('Publisher.Id'), nullable=False) 
    publisher = sa.orm.relation("Publisher", back_populates="books")
    details = sa.orm.relation("BookDetails", uselist=False, back_populates="book")
    authors = sa.orm.relation("Author", secondary='BookAuthor', back_populates="books")

    def __repr__(self):
        return f'<Book {self.id} ({self.title} {self.isbn}) {self.pages}>'

Indexes

We can create an index for a single column with the index=True option:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import sqlalchemy as sa
from data.modelbase import ModelBase

class Publisher(ModelBase):
    __tablename__ = 'Publisher'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column('Name', sa.String, nullable=False, index=True)
    books = sa.orm.relationship("Book", back_populates="publisher")

    def __repr__(self):
        return f'<Publisher {self.id} ({self.name})>'

To create a multi-column index, we can add an index to the __table_args__ property and list all columns it should contain:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import sqlalchemy as sa
from data.modelbase import ModelBase
import datetime

class Employee(ModelBase):
    __tablename__ = 'Employee'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    last_name = sa.Column('LastName', sa.String, nullable=False)
    first_name = sa.Column('FirstName', sa.String, nullable=False)
    birth_date = sa.Column('BirthDate', sa.String)
    created_date = sa.Column(sa.DateTime, default=datetime.datetime.now)

    __table_args__ = (
        sa.Index('my_index', "LastName", "FirstName"), 
    )

    def __repr__(self):
        return f'<Employee {self.id} ({self.first_name} {self.last_name}) {self.birth_date}>'

Unique constraint

Unique constraints are indexes with the option unique=True:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
import sqlalchemy as sa
from sqlalchemy.sql.schema import UniqueConstraint
from data.modelbase import ModelBase

class Book(ModelBase):
    __tablename__ = 'Book'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    title = sa.Column('Title', sa.String, nullable=False)
    isbn = sa.Column('ISBN', sa.String(13), nullable=False, index=True, unique=True)
    pages = sa.Column('Pages', sa.Integer)
    published_by = sa.Column('PublishedBy', sa.Integer, sa.ForeignKey('Publisher.Id'), nullable=False) 
    publisher = sa.orm.relation("Publisher", back_populates="books")
    details = sa.orm.relation("BookDetails", uselist=False, back_populates="book")
    authors = sa.orm.relation("Author", secondary='BookAuthor', back_populates="books")

    def __repr__(self):
        return f'<Book {self.id} ({self.title} {self.isbn}) {self.pages}>'

At least for SQLite you must set the index=True field as well.

Default values

We can specify a default value for a column with the default= argument. It accepts a fixed value or a function. If we use a function like the current date, we must use the function name without the () as we did with Decorators:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import sqlalchemy as sa
from data.modelbase import ModelBase
import datetime

class Employee(ModelBase):
    __tablename__ = 'Employee'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    last_name = sa.Column('LastName', sa.String, nullable=False)
    first_name = sa.Column('FirstName', sa.String, nullable=False)
    birth_date = sa.Column('BirthDate', sa.String)
    created_date = sa.Column(sa.DateTime, default=datetime.datetime.now)

    __table_args__ = (
        sa.Index('my_index', "LastName", "FirstName"), 
    )

    def __repr__(self):
        return f'<Employee {self.id} ({self.first_name} {self.last_name}) {self.birth_date}>'

Next

With these constraints we can enforce the integrity of our data at the database level. Not everything that SQLAlchemy offers works in SQLite. Next week we look how we can connect to Microsoft SQL Server and what we need to do to get it working.