SQLAlchemy ORMs

The suuuuuuuper Beginner's guide



In [ ]:

    
from sqlalchemy.ext.declarative import declarative_base



In [ ]:

    
Base = declarative_base()



In [ ]:

    
from sqlalchemy import Column, Integer, String



In [ ]:

    
class BasicStudent(Base):
    
    __tablename__ = "students_basic"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Student %r>" % self.name



In [ ]:

    
BasicStudent.__table__



In [ ]:

    
James = BasicStudent("James")



In [ ]:

    
James.name



In [ ]:

    
James.id is None



In [ ]:

    
from sqlalchemy import create_engine



In [ ]:

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



In [ ]:

    
Base.metadata.tables



In [ ]:

    
Base.metadata.create_all(engine)



In [ ]:

    
from sqlalchemy.orm import Session



In [ ]:

    
session = Session(bind=engine)



In [ ]:

    
session.add(James)



In [ ]:

    
session.new



In [ ]:

    
session.commit()



In [ ]:

    
session.new



In [ ]:

    
James.id



In [ ]:

    
james_from_db = session.query(BasicStudent).get(1)



In [ ]:

    
james_from_db is James



In [ ]:

    
James.name = "Jamos"



In [ ]:

    
session.new



In [ ]:

    
session.dirty



In [ ]:

    
session.rollback()



In [ ]:

    
session.dirty



In [ ]:

    
James.name



In [ ]:

    
session.add_all([
    BasicStudent("Mary"),
    BasicStudent("John")
])



In [ ]:

    
session.commit()



In [ ]:

    
# Get all students
session.query(BasicStudent).all()



In [ ]:

    
# Get students ordered in descending by their IDs
session.query(BasicStudent).order_by(BasicStudent.id.desc()).all()



In [ ]:

    
# Use array slice operators to get the required size of elements
session.query(BasicStudent).all()[:2]



In [ ]:

    
# WHERE clause
session.query(BasicStudent).filter_by(name="Mary").all()



In [ ]:

    
from sqlalchemy import or_

session.query(BasicStudent).filter(or_(BasicStudent.name == "Mary",\
                                       BasicStudent.name == "John")).all()



In [ ]:

    
session.query(BasicStudent).filter_by(name="Booga").first() is None



In [ ]:

    
session.query(BasicStudent).filter_by(name="Booga").one()

Breakkkk



In [ ]:

    
from sqlalchemy.orm import relationship, backref
from sqlalchemy import ForeignKey



In [ ]:

    
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



In [ ]:

    
class Teacher(Base):
    
    __tablename__ = "teachers"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    """
    Create a relationship with the *Subject* class. In addition
    create a back reference in the *Subject* class to this class
    with the name *teacher*
    """
    subjects = relationship("Subject", backref="teacher")
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Teacher %r>" % self.name



In [ ]:

    
Base.metadata.create_all(engine)



In [ ]:

    
Seema = Teacher("Seema")



In [ ]:

    
English = Subject("English")



In [ ]:

    
Seema.subjects.append(English)



In [ ]:

    
Tarun = Teacher("Tarun")



In [ ]:

    
Geography = Subject("Geography")



In [ ]:

    
Geography.teacher = Tarun



In [ ]:

    
session.add_all([
    Seema,
    Tarun
])



In [ ]:

    
session.new



In [ ]:

    
session.commit()



In [ ]:

    
session.query(Teacher, Subject).join(Subject).all()

Breakkkk



In [ ]:

    
from sqlalchemy import Table



In [ ]:

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



In [ ]:

    
class Student(Base):
    
    __tablename__ = "students"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    """
    Create a relationship with the *Subject* table using the 
    *student_subjects* table as a secondary table. The *student_subjects*
    table will just contain the 2 ids that are needed in a many-many 
    relationship.
    
    In addition create a backreference to the *Subject* table with a list
    of students that are taking that course
    """
    subjects = relationship("Subject", secondary=student_subjects, 
                            backref="students")
    
    def __init__(self,name):
        self.name = name
        
    def __repr__(self):
        return "<Student %r>" % self.name



In [ ]:

    
Base.metadata.create_all(engine)



In [ ]:

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



In [ ]:

    
Math = Subject("Math")
Science = Subject("Science")
Cs = Subject("Cs")



In [ ]:

    
Tanuja = Teacher("Tanuja")
Padmaja = Teacher("Padmaja")
Suguna = Teacher("Suguna")



In [ ]:

    
Math.teacher = Padmaja
Science.teacher = Tanuja
Cs.teacher = Suguna



In [ ]:

    
Bob.subjects.append(Math)
Bob.subjects.append(Cs)



In [ ]:

    
Alice.subjects.append(Cs)



In [ ]:

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



In [ ]:

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



In [ ]:

    
session.commit()



In [ ]:

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



In [ ]:

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



In [ ]:

    
from sqlalchemy import func

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