Search⌘ K
AI Features
Home
Courses
Quick Start Full Stack Web Development

Integrity Tests

Explore how to maintain database integrity with SQLAlchemy by writing data validation methods and tests. Understand how to detect and clean nonstandard skill entries and unnecessary project assignments using Python ORM classes in a full stack development context.

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 ...