Skip to content
Snippets Groups Projects
Select Git revision
  • 8c3150da33aa05808eeba8b91cb92b9b76251c67
  • main default protected
2 results

database.py

Blame
  • Forked from Brady James Garvin / advanced_orm_relations
    Source project has a limited visibility.
    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()