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.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
Attention: SQLite needs batch mode
If you try to add constraints to existing tables in SQLite you may get this error in Alembic:
|
1 |
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.