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)]