1from __future__ import absolute_import, print_function 2 3from pony.orm.core import * 4from decimal import Decimal 5from datetime import date 6 7db = Database() 8 9class Faculty(db.Entity): 10 _table_ = 'Faculties' 11 number = PrimaryKey(int) 12 name = Required(str, unique=True) 13 departments = Set('Department') 14 15class Department(db.Entity): 16 _table_ = 'Departments' 17 number = PrimaryKey(int) 18 name = Required(str, unique=True) 19 faculty = Required(Faculty) 20 teachers = Set('Teacher') 21 majors = Set('Major') 22 groups = Set('Group') 23 24class Group(db.Entity): 25 _table_ = 'Groups' 26 number = PrimaryKey(int) 27 grad_year = Required(int) 28 department = Required(Department, column='dep') 29 lessons = Set('Lesson', columns=['day_of_week', 'meeting_time', 'classroom_number', 'building']) 30 students = Set('Student') 31 32class Student(db.Entity): 33 _table_ = 'Students' 34 name = Required(str) 35 scholarship = Required(Decimal, 10, 2, default=Decimal('0.0')) 36 group = Required(Group) 37 grades = Set('Grade') 38 39class Major(db.Entity): 40 _table_ = 'Majors' 41 name = PrimaryKey(str) 42 department = Required(Department) 43 courses = Set('Course') 44 45class Subject(db.Entity): 46 _table_ = 'Subjects' 47 name = PrimaryKey(str) 48 courses = Set('Course') 49 teachers = Set('Teacher') 50 51class Course(db.Entity): 52 _table_ = 'Courses' 53 major = Required(Major) 54 subject = Required(Subject) 55 semester = Required(int) 56 composite_key(major, subject, semester) 57 lect_hours = Required(int) 58 pract_hours = Required(int) 59 credit = Required(int) 60 lessons = Set('Lesson') 61 grades = Set('Grade') 62 63class Lesson(db.Entity): 64 _table_ = 'Lessons' 65 day_of_week = Required(int) 66 meeting_time = Required(int) 67 classroom = Required('Classroom') 68 PrimaryKey(day_of_week, meeting_time, classroom) 69 course = Required(Course) 70 teacher = Required('Teacher') 71 groups = Set(Group) 72 73class Grade(db.Entity): 74 _table_ = 'Grades' 75 student = Required(Student) 76 course = Required(Course) 77 PrimaryKey(student, course) 78 teacher = Required('Teacher') 79 date = Required(date) 80 value = Required(str) 81 82class Teacher(db.Entity): 83 _table_ = 'Teachers' 84 name = Required(str) 85 degree = Optional(str) 86 department = Required(Department) 87 subjects = Set(Subject) 88 lessons = Set(Lesson) 89 grades = Set(Grade) 90 91class Building(db.Entity): 92 _table_ = 'Buildings' 93 number = PrimaryKey(str) 94 description = Optional(str) 95 classrooms = Set('Classroom') 96 97class Classroom(db.Entity): 98 _table_ = 'Classrooms' 99 building = Required(Building) 100 number = Required(str) 101 PrimaryKey(building, number) 102 description = Optional(str) 103 lessons = Set(Lesson) 104 105db.bind('sqlite', 'university2.sqlite', create_db=True) 106#db.bind('mysql', host='localhost', user='pony', passwd='pony', db='university2') 107#db.bind('postgres', user='pony', password='pony', host='localhost', database='university2') 108#db.bind('oracle', 'university2/pony@localhost') 109 110db.generate_mapping(create_tables=True) 111 112sql_debug(True) 113 114def test_queries(): 115 # very simple query 116 select(s for s in Student)[:] 117 118 # one condition 119 select(s for s in Student if s.scholarship > 0)[:] 120 121 # multiple conditions 122 select(s for s in Student if s.scholarship > 0 and s.group.number == 4142)[:] 123 124 # no join here - attribute can be found in table Students 125 select(s for s in Student if s.group.number == 4142)[:] 126 127 # automatic join of two tables because grad_year is stored in table Groups 128 select(s for s in Student if s.group.grad_year == 2011)[:] 129 130 # still two tables are joined 131 select(s for s in Student if s.group.department.number == 44)[:] 132 133 # automatic join of tree tables 134 select(s for s in Student if s.group.department.name == 'Ancient Philosophy')[:] 135 136 # manual join of tables will produce equivalent query 137 select(s for s in Student for g in Group if s.group == g and g.department.name == 'Ancient Philosophy')[:] 138 139 # join two tables by composite foreign key 140 select(c for c in Classroom for l in Lesson if l.classroom == c and l.course.subject.name == 'Physics')[:] 141 142 # Lessons will be joined with Buildings directly without Classrooms 143 select(s for s in Subject for l in Lesson if s == l.course.subject and l.classroom.building.description == 'some description')[:] 144 145 # just another example of join of many tables 146 select(c for c in Course if c.major.department.faculty.number == 4)[:] 147