Search⌘ K
Home
Courses
Quick Start Full Stack Web Development

Integrity Tests

Learn about integrity tests in this lesson.

We'll cover the following...

Let’s write some data check methods and exercise them in a test. To do this, we’ll start with three new ORM classes that are complicated enough to require some out-of-database constraint checks.

Writing in some complex data

The idea in this example is that we have employees that work for us and projects we need to be done. There is a many-to-many relationship between employees and projects which describes who has been assigned to what project. These assignments are captured in a separate Assignment table.

Python 3.8
import sqlalchemy
from sqlalchemy import create_engine, Column, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import CheckConstraint
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
connection_string = "sqlite:///checksdemo.db"
db   = create_engine(connection_string)
base = declarative_base()
class Employee(base):
    __tablename__ = 'employees'
    id = Column(types.Integer, primary_key=True)
    name  = Column(types.String(length=50), nullable=False)
    skill = Column(types.String(length=20))
    assignments = relationship("Assignment", back_populates="employee")
class Project(base):
    __tablename__ = 'projects'
    id = Column(types.Integer, primary_key=True)
    name  = Column(types.String(length=50), nullable=False)
    needs = Column(types.String(length=120))
    members = relationship("Assignment", back_populates="project")
class Assignment(base):
     __tablename__ = 'assignments'
     id = Column(types.Integer, primary_key=True)
     employee_id = Column(types.Integer,
                          ForeignKey('employees.id'),
                          nullable=False)
     project_id   = Column(types.Integer,
                           ForeignKey('projects.id'),
                           nullable=False)
     employee    = relationship("Employee", back_populates="assignments")
     project     = relationship("Project",  back_populates="members")
Session = sessionmaker(db)
session = Session()
base.metadata.create_all(db)

We create a function to add records and call it from the __main__ clause. In Python, if evaluates true only when the script is run directly and not when it is imported. It’s convenient to have a file do double-duty as both a module to import and as a script that we can run.

Python 3.8
def add_sample_data():
    # Add employees and projects; assign employees to projects
    session.add_all([
        Employee(id=1, name="Hercules the Bear", skill="client"),
        Employee(id=2, name="Herman Hollerith", skill="db_admin"),
        Employee(id=3, name="Martina Hingis", skill="server"),
        Employee(id=4, name="Margo Hoff", skill="designer"),
        Employee(id=5, name="Hulk Hogan"),
        Project(id=1, name="Design Transmogrifier",
                needs='designer server'),
        Project(id=2, name="Build Duplicator", needs='client db_admin'),
        Project(id=3, name="Repair Enhance-o-Tron",
                needs='client juggler'),
        Assignment(employee_id=1, project_id=2),
        Assignment(employee_id=2, project_id=2),
        Assignment(employee_id=3, project_id=3),
        Assignment(employee_id=4, project_id=1),
    ])
    session.commit()
if __name__ == '__main__':
    add_sample_data()

Checking the data

We’ve chosen to leave the skill needs for each project as space-separated words, mainly for brevity. We could have created a separate field need_server, ...