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.
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)
- SQLAlchemy Expression Language, Advanced Usage
- Migrate SQLAlchemy Databases with Alembic
- SQLAlchemy Expression Language, More Advanced Usage
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.
>>> 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() []
Let’s create another employee to test our new DateTime column ‘hired_on’:
>>> 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)
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:
>>> print func.now() now() >>> from sqlalchemy import select >>> rs = s.execute(select([func.now()])) >>> rs.fetchone() (datetime.datetime(2014, 3, 24, 2, 9, 12),)
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.
>>> for department in s.query(Department).all(): ... s.delete(department) ... >>> s.commit() >>> s.query(Department).count() 0 >>> s.query(Employee).count() 0
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”.
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()
Suppose we want to find all the employees whose name starts with “C”, we can use startswith()
to achieve our goal:
>>>s.query(Employee).filter(Employee.name.startswith("C")).one().name u'Cathy'
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.
>>> 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
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 Department
s? 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
.
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)
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:
>>> 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'
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.
>>> s.query(Employee).filter(Employee.departments.any(Department.name == 'IT')).all()[0].name u'John'
On the other hand, to find a list of departments which have John as one of their employees, we can use the same function.
>>> s.query(Department).filter(Department.employees.any(Employee.name == 'John')).all()[0].name u'IT'
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
).