ORM Recap

In one of the previous articles, we briefly went through an example database with two tables department and employee where one department can have multiple employees and one employee can belong to arbitrary number of departments. We used several code snippets to demonstrate the power of SQLAlchemy's expression language and show how to write ORM queries.

In this article, we are going to take a look at SQLAlchemy's ORM in more detail and find out how we can use it more effectively to solve real-world problems.

Department and Employee

We are going to keep using the previous article's department-employee as the example database in this article. We are also going to add more columns to each table to make our example more interesting to play with.

[python]
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
name = Column(String)

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
# Use default=func.now() to set the default hiring time
# of an Employee to be the current time when an
# Employee record was created
hired_on = Column(DateTime, default=func.now())
department_id = Column(Integer, ForeignKey('department.id'))
# Use cascade='delete,all' to propagate the deletion of a Department onto its Employees
department = relationship(
Department,
backref=backref('employees',
uselist=True,
cascade='delete,all'))

from sqlalchemy import create_engine
engine = create_engine('sqlite:///orm_in_detail.sqlite')

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)
[/python]

Notice we made two changes to the employee table: 1. we inserted a new column 'hired_on' which is a DateTime column that stores when the employee was hired and, 2. we inserted a keyword argument 'cascade' with a value 'delete,all' to the backref of the relationship Employee.department. The cascade allows SQLAlchemy to automatically delete a department's employees when the department itself is deleted.

Now let's write a couple lines of code to play with our new table definitions.

[python]
>>> d = Department(name="IT")
>>> emp1 = Employee(name="John", department=d)
>>> s = session()
>>> s.add(d)
>>> s.add(emp1)
>>> s.commit()
>>> s.delete(d) # Deleting the department also deletes all of its employees.
>>> s.commit()
>>> s.query(Employee).all()
[]
[/python]

Let's create another employee to test our new DateTime column 'hired_on':

[python]
>>> emp2 = Employee(name="Marry")
>>> emp2.hired_on
>>> s.add(emp2)
>>> emp2.hired_on
>>> s.commit()
>>> emp2.hired_on
datetime.datetime(2014, 3, 24, 2, 3, 46)
[/python]

Did you notice something odd about this short snippet? Since Employee.hired_on is defined to have a default value of func.now(), how come emp2.hired_on is None after it has been created?

The answer lies in how func.now() was handled by SQLAlchemy. func generates SQL function expressions. func.now() literally translates into now() in SQL:

[python]
>>> print func.now()
now()
>>> from sqlalchemy import select
>>> rs = s.execute(select([func.now()]))
>>> rs.fetchone()
(datetime.datetime(2014, 3, 24, 2, 9, 12),)
[/python]

As you see, executing the func.now() function through the SQLAlchemy database session object gives us the current datetime based on our machine's time zone.

Before proceeding further, let's delete all the records in the department table and the employee table so that we can start later from a clean database.

[python]
>>> for department in s.query(Department).all():
... s.delete(department)
...
>>> s.commit()
>>> s.query(Department).count()
0
>>> s.query(Employee).count()
0
[/python]

More ORM Queries

Let's keep writing queries to become more familiar with the ORM API. First, we insert several employees into two departments "IT" and "Financial".

[python]
IT = Department(name="IT")
Financial = Department(name="Financial")
john = Employee(name="John", department=IT)
marry = Employee(name="marry", department=Financial)
s.add(IT)
s.add(Financial)
s.add(john)
s.add(marry)
s.commit()
cathy = Employee(name="Cathy", department=Financial)
s.add(cathy)
s.commit()
[/python]

Suppose we want to find all the employees whose name starts with "C", we can use startswith() to achieve our goal:

[python]
>>>s.query(Employee).filter(Employee.name.startswith("C")).one().name
u'Cathy'
[/python]

Making the query harder, suppose we want to find all the employees whose name starts with "C" and who also work for the Financial department, we can use a join query:

[python]
>>> s.query(Employee).join(Employee.department).filter(Employee.name.startswith('C'), Department.name == 'Financial').all()[0].name
u'Cathy'
[/python]

What if we want to search for employees who are hired before a certain datetime? We can use a normal datetime comparison operator in the filter clause.

[python]
>>> from datetime import datetime
# Find all employees who will be hired in the future
>>> s.query(Employee).filter(Employee.hired_on > func.now()).count()
0
# Find all employees who have been hired in the past
>>> s.query(Employee).filter(Employee.hired_on < func.now()).count()
3
[/python]

Many-to-Many between Department and Employee

So far, a Department can have multiple Employees and one Employee belongs to at most one Department. Therefore, there's a one-to-many relationship between Department and Employee. What if an Employee can belong to an arbitrary number of Departments? How do we handle many-to-many relationship?

In order to handle a many-to-many relationship between Department and Employee, we are going to create a new association table "department_employee_link" with foreign key columns to both Department and Employee. We also need to remove the backref definition from Department since we are going to insert a to-many relationship in Employee.

[python]
import os

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
name = Column(String)
employees = relationship(
'Employee',
secondary='department_employee_link'
)

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
hired_on = Column(
DateTime,
default=func.now())
departments = relationship(
Department,
secondary='department_employee_link'
)

class DepartmentEmployeeLink(Base):
__tablename__ = 'department_employee_link'
department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
[/python]

Notice that all the columns in DepartmentEmployeeLink, 'department_id' and 'employee_id', are combined together to form the primary key for the table department_employee_link and the relationship arguments in class Department and class Employee have an additional keyword argument "secondary" which points to the association table.

Once we have defined our models, we can use them in the following way:

[python]
>>> fp = 'orm_in_detail.sqlite'
>>> # Remove the existing orm_in_detail.sqlite file
>>> if os.path.exists(fp):
... os.remove(fp)
...
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///orm_in_detail.sqlite')
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>> s = session()
>>> IT = Department(name="IT")
>>> Financial = Department(name="Financial")
>>> cathy = Employee(name="Cathy")
>>> marry = Employee(name="Marry")
>>> john = Employee(name="John")
>>> cathy.departments.append(Financial)
>>> Financial.employees.append(marry)
>>> john.departments.append(IT)
>>> s.add(IT)
>>> s.add(Financial)
>>> s.add(cathy)
>>> s.add(marry)
>>> s.add(john)
>>> s.commit()
>>> cathy.departments[0].name
u'Financial'
>>> marry.departments[0].name
u'Financial'
>>> john.departments[0].name
u'IT'
>>> IT.employees[0].name
u'John'
[/python]

Notice that we use Employee.departments.append() to append one Department to the list of departments of an Employee.

To find a list of employees in the IT department no matter whether they belong to other departments or not, we can use the relationship.any() function.

[python]
>>> s.query(Employee).filter(Employee.departments.any(Department.name == 'IT')).all()[0].name
u'John'
[/python]

On the other hand, to find a list of departments which have John as one of their employees, we can use the same function.

[python]
>>> s.query(Department).filter(Department.employees.any(Employee.name == 'John')).all()[0].name
u'IT'
[/python]

Summary and Tips

In this article, we take a deeper look at SQLAlchemy's ORM library and wrote more queries to explore the API. Notice that when you want to cascade deletion from the foreign key referred object to the referring object, you can specify cascade='all,delete' in the backref of the refering object's foreign key definition (as what's shown in the example relationship Employee.department).