Imports



In [1]:

    
from sqlalchemy import create_engine



In [2]:

    
from sqlalchemy.ext.declarative import declarative_base



In [3]:

    
from sqlalchemy import Column, Integer, String



In [4]:

    
from sqlalchemy.orm import Session



In [5]:

    
from sqlalchemy.orm import relationship, backref



In [6]:

    
from sqlalchemy import ForeignKey



In [7]:

    
from sqlalchemy import Table



In [8]:

    
from sqlalchemy import func

Setup



In [9]:

    
engine = create_engine("sqlite:///:memory:", echo=False)



In [10]:

    
Base = declarative_base(bind=engine)



In [11]:

    
session = Session(engine)

Models



In [12]:

    
student_subjects = Table("student_subjects", Base.metadata,
                         Column("student_id", Integer, ForeignKey("students.id")),
                         Column("subject_id", Integer, ForeignKey("subjects.id")))



In [13]:

    
class Student(Base):
    
    __tablename__ = "students"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    subjects = relationship("Subject", secondary=student_subjects, backref="students")
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Student %r>" % self.name



In [14]:

    
class Teacher(Base):
    
    __tablename__ = "teachers"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    subjects = relationship("Subject", backref="teacher")
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Teacher %r>" % self.name



In [15]:

    
class Subject(Base):
    
    __tablename__ = "subjects"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    teacher_id = Column(Integer, ForeignKey("teachers.id"))
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Subject %r>" % self.name

DB Initialize



In [16]:

    
Base.metadata.create_all(engine)

Data Initialize



In [17]:

    
Bob = Student("Bob")
Alice = Student("Alice")
Eve = Student("Eve")



In [18]:

    
Math = Subject("Math")
Science = Subject("Science")
History = Subject("History")



In [19]:

    
Tanuja = Teacher("Tanuja")
Padmaja = Teacher("Padmaja")
Veena = Teacher("Veena")



In [20]:

    
Math.teacher = Padmaja



In [21]:

    
Science.teacher = Tanuja



In [22]:

    
History.teacher = Veena



In [23]:

    
Bob.subjects.append(Math)
Bob.subjects.append(History)



In [24]:

    
Alice.subjects.append(History)



In [25]:

    
Eve.subjects.append(Math)
Eve.subjects.append(Science)
Eve.subjects.append(History)



In [26]:

    
session.add_all([
    Bob,
    Alice,
    Eve,
    Math,
    Science,
    History,
    Tanuja,
    Padmaja,
    Veena
])



In [27]:

    
session.commit()

Queries



In [28]:

    
# Do you want the echo?
#engine.echo = True



In [29]:

    
# Get the list of students
session.query(Student).all()









    Out[29]:





[<Student u'Bob'>, <Student u'Eve'>, <Student u'Alice'>]



In [30]:

    
# Get the list of students whos name begins with A
session.query(Student).filter(Student.name.like("a%")).all()









    Out[30]:





[<Student u'Alice'>]



In [31]:

    
# Get the list of subjects of Alice
alice = session.query(Student).filter_by(name="Alice").first()
alice.subjects









    Out[31]:





[<Subject u'History'>]



In [32]:

    
# Who all have taken up the History subject?
history = session.query(Subject).filter_by(name="History").first()
history.students









    Out[32]:





[<Student u'Eve'>, <Student u'Bob'>, <Student u'Alice'>]



In [33]:

    
# Lets do the above with 1 query instead of 2
session.query(Student).join(Student.subjects).filter_by(name="History").all()









    Out[33]:





[<Student u'Eve'>, <Student u'Bob'>, <Student u'Alice'>]



In [35]:

    
# How many subjects is each student taking?
session.query(Student, func.count(Subject.name)).join(Student.subjects).group_by(Student).all()









    Out[35]:





[(<Student u'Bob'>, 2), (<Student u'Eve'>, 3), (<Student u'Alice'>, 1)]



In [36]:

    
# How many students are taking each subject?
session.query(Subject.name, func.count(Subject.name)).join(student_subjects).group_by(Subject).all()









    Out[36]:





[(u'Math', 2), (u'Science', 1), (u'History', 3)]