Select Git revision
database.py
Forked from
Brady James Garvin / advanced_orm_relations
Source project has a limited visibility.
-
Christopher Bohn authoredChristopher Bohn authored
database.py 6.40 KiB
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Persisted = declarative_base() # pylint: disable=invalid-name
class Person(Persisted):
__tablename__ = 'people'
person_id = Column(Integer, primary_key=True)
name = Column(String(256), nullable=False)
supervisor_id = Column(Integer, ForeignKey('people.person_id', ondelete='CASCADE'))
mentor_id = Column(Integer, ForeignKey('people.person_id', ondelete='CASCADE'))
# There are multiple foreign keys this many-to-one relationship could use, so we have to specify extra information.
# When we have only one object to relate to (the uselist=False case), there are two things to specify:
# (1) which of our foreign keys this relationship should use to identify the other record and
# (2) the key in the other record to match it to (usually a primary key).
# Note that all of this extra information is written in terms of classes and OO fields, not tables or DB fields.
supervisor = relationship('Person', foreign_keys='[Person.supervisor_id]', remote_side='[Person.person_id]',
back_populates='supervisees')
mentor = relationship('Person', foreign_keys='[Person.mentor_id]', remote_side='[Person.person_id]',
back_populates='mentees')
# There are multiple foreign keys this one-to-many relationship could use, so we have to specify extra information.
# When we have potentially many objects to relate to (the uselist=True case), SQLAlchemy defaults to using our own
# primary key, so we do not have to specify the local side, just:
# (1) which of the other record's foreign keys this relationship should match against our primary key.
# Note that all of this extra information is written in terms of classes and OO fields, not tables or DB fields.
supervisees = relationship('Person', uselist=True, foreign_keys='[Person.supervisor_id]',
back_populates='supervisor')
mentees = relationship('Person', uselist=True, foreign_keys='[Person.mentor_id]',
back_populates='mentor')
# There are multiple foreign keys this many-to-many relationship could use, so we have to specify extra information.
# Here, however, there are two joins, so we must specify that information separately for each.
# For the first join, which finds the matching pairs in the join table, we must specify:
# (1) which of the join table's foreign keys this relationship should match against our key,
# (2) that we want to match on key equality (==), and
# (3) the key in our record to match the foreign key to (usually our primary key).
# For the second join, which finds the related records indicated by the matching pairs, we must specify:
# (1) which of the join table's foreign keys this relationship should use to identify the other record,
# (2) that we want to match on key equality (==), and
# (3) the key in the other record to match the foreign key to (usually a primary key).
# Note that all of these conditions are written in terms of classes, OO fields, and Python operators, not tables,
# DB fields, or SQL operators.
reviewers = relationship('Person', uselist=True, secondary='reviewerships',
primaryjoin='Reviewership.reviewee_id == Person.person_id',
secondaryjoin='Reviewership.reviewer_id == Person.person_id',
back_populates='reviewees')
reviewees = relationship('Person', uselist=True, secondary='reviewerships',
primaryjoin='Reviewership.reviewer_id == Person.person_id',
secondaryjoin='Reviewership.reviewee_id == Person.person_id',
back_populates='reviewers')
# If we want to be able to access the join table's pairs themselves, we write a standard one-to-many relationship
# with extra information.
# (replace the reviewers & reviewees relationships on lines 45-52 with:
# reviewers = relationship('Person', uselist=True, secondary='reviewerships',
# primaryjoin='Reviewership.reviewee_id == Person.person_id',
# secondaryjoin='Reviewership.reviewer_id == Person.person_id',
# viewonly=True)
# reviewees = relationship('Person', uselist=True, secondary='reviewerships',
# primaryjoin='Reviewership.reviewer_id == Person.person_id',
# secondaryjoin='Reviewership.reviewee_id == Person.person_id',
# viewonly=True)
# reviewerships = relationship('Reviewership', uselist=True, foreign_keys='[Reviewership.reviewer_id]',
# back_populates='reviewer')
# revieweeships = relationship('Reviewership', uselist=True, foreign_keys='[Reviewership.reviewee_id]',
# back_populates='reviewee')
class Reviewership(Persisted):
__tablename__ = 'reviewerships'
reviewer_id = Column(Integer, ForeignKey('people.person_id', ondelete='CASCADE'), primary_key=True)
reviewee_id = Column(Integer, ForeignKey('people.person_id', ondelete='CASCADE'), primary_key=True)
# If we want to be able to access the related records from a join-table pairs, we write a standard many-to-one
# relationship with extra information.
# reviewer = relationship('Person', foreign_keys='[Reviewership.reviewer_id]', remote_side='[Person.person_id]',
# back_populates='reviewerships')
# reviewee = relationship('Person', foreign_keys='[Reviewership.reviewee_id]', remote_side='[Person.person_id]',
# back_populates='revieweeships')
class Database(object):
@staticmethod
def construct_mysql_url(authority, port, database, username, password):
return f'mysql+mysqlconnector://{username}:{password}@{authority}:{port}/{database}'
@staticmethod
def construct_in_memory_url():
return 'sqlite:///'
def __init__(self, url):
self.engine = create_engine(url)
self.Session = sessionmaker()
self.Session.configure(bind=self.engine)
def ensure_tables_exist(self):
Persisted.metadata.create_all(self.engine)
def create_session(self):
return self.Session()