Created
January 15, 2016 11:23
-
-
Save zopyx/5ae4fd53804ca7dd29ed to your computer and use it in GitHub Desktop.
sqlalchemy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from sqlalchemy import * | |
from sqlalchemy.orm import sessionmaker | |
from sqlalchemy.ext.declarative import declarative_base | |
from sqlalchemy.dialects.postgresql import JSON | |
from sqlalchemy.orm import relationship | |
from sqlalchemy.ext.declarative import declared_attr | |
################################################################ | |
# DPIS database model | |
################################################################ | |
Base = declarative_base() | |
class Country(Base): | |
""" Managed through Kotti """ | |
__tablename__ = 'countries' | |
dpis_id = Column(Integer, primary_key=True) | |
title = Column(Unicode(80)) | |
regions = relationship("Region", | |
primaryjoin="Country.dpis_id==Region.country_id") | |
class Region(Base): | |
""" Managed through Kotti """ | |
__tablename__ = 'regions' | |
dpis_id = Column(Integer, primary_key=True) | |
country_id = Column(Integer, ForeignKey('countries.dpis_id')) | |
title = Column(Unicode(80)) | |
parishes = relationship("Parish", | |
primaryjoin="Region.dpis_id==Parish.region_id") | |
class Parish(Base): | |
""" Managed through Kotti """ | |
__tablename__ = 'parishes' | |
dpis_id = Column(Integer, primary_key=True) | |
region_id = Column(Integer, ForeignKey('regions.dpis_id')) | |
title = Column(Unicode(80)) | |
schools = relationship("School", | |
primaryjoin="Parish.dpis_id==School.parish_id") | |
################################################################ | |
# School <-1:N-> Student <-1:N-> Score | |
# are managed within Sqlalchemy as a bidirectional relation | |
################################################################ | |
class School(Base): | |
""" Managed through Kotti """ | |
__tablename__ = 'schools' | |
dpis_id = Column(Integer, primary_key=True) | |
parish_id = Column(Integer, ForeignKey('parishes.dpis_id')) | |
title = Column(Unicode(80)) | |
# all students of this school | |
students = relationship("Student", | |
primaryjoin="School.dpis_id==Student.school_id") | |
class Student(Base): | |
""" Managed by system, no integration with Kotti """ | |
__tablename__ = 'students' | |
dpis_id = Column(Integer, primary_key=True) | |
school_id = Column(Integer, ForeignKey('schools.dpis_id')) | |
firstname = Column(Unicode(80)) | |
lastname = Column(Unicode(80)) | |
address = Column(Unicode(80)) | |
city = Column(Unicode(80)) | |
birth = Column(Date) | |
gender = Column(Unicode(4)) | |
# back-reference to school | |
school = relationship(School, primaryjoin=school_id==School.dpis_id) | |
# all scores of this student | |
scores = relationship("Score", | |
primaryjoin="Student.dpis_id==Score.student_id") | |
class Score(Base): | |
__tablename__ = 'scores' | |
dpis_id = Column(Integer, primary_key=True) | |
student_id = Column(Integer, ForeignKey('students.dpis_id')) | |
year = Column(Integer, index=True) | |
subject = Column(Unicode(80), index=True) | |
score = Column(Integer, index=True) | |
# back reference to student | |
student = relationship(Student, primaryjoin="Score.student_id==Student.dpis_id") | |
class Person(Base): | |
""" A Person represents someone how can login into the system | |
for doing *something*. | |
""" | |
__tablename__ = 'dpis_users' | |
dpis_id = Column(Integer, primary_key=True) | |
title = Column(Unicode(80)) | |
firstname = Column(Unicode(80)) | |
lastname = Column(Unicode(80)) | |
username = Column(Unicode(32)) # Kotti login name | |
password = Column(Unicode(32)) # Hashed Kotti password? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment