Overview

In the previous article, we made a comparison between SQLAlchemy and other Python ORMs. In this article, we are going to take a deeper look at SQLAlchemy's ORM and Expression Language and use an example to showcase their empowering API and easy-to-understand Python structures.

Not only does the SQLAlchemy ORM provide a way to map database concepts into the Python space, it also provides a convenient Pythonic querying API. To find something in an SQLAlchemy database using ORM is pleasant, since everything is straightforward and the query results are returned as Python objects, as well as the query parameters.

The SQLAlchemy Expression Language provides a system for the programmers to write "SQL statements" using Python constructs. These constructs are modeled to resemble those of the underlying database as closely as possible, while hiding the difference between various database backends from the user. Although these constructs aim to represent equivalent concepts between backends with consistent structures, they do not conceal useful backend-specific features. Therefore, the Expression Language provides a way for the programmers to write backend-neutral expressions, while allowing the programmers to take advantage of specific backend features if they really want to.

The Expression Language complements the Object Relational Mapper. Whereas the ORM presents an abstracted pattern of usage mapping database concepts into Python's space, where models are used to map tables and relationships are used to map many-to-many through an association table and one-to-one through a foreign key, the Expression Language is used to directly represent more primitive constructs in a database without opinion.

An example with departments and employees

Let's use an example to illustrate how to use the expression language in a database with two tables department and employee. A department has many employees while an employee belongs to at most one department. Therefore, the database could be designed as follows:

[python]
>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> 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)
... department_id = Column(Integer, ForeignKey('department.id'))
... department = relationship(Department, backref=backref('employees', uselist=True))
...
>>>
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
[/python]

In this example, we created a in-memory sqlite database with two tables 'department' and 'employee'. The column 'employee.department_id' is a foreign key to the column 'department.id' and the relationship 'department.employees' include all the employees in that department. To test our setup, we can simply insert several example records and query them using SQLAlchemy's ORM:

[python]
>>> john = Employee(name='john')
>>> it_department = Department(name='IT')
>>> john.department = it_department
>>> s = session()
>>> s.add(john)
>>> s.add(it_department)
>>> s.commit()
>>> it = s.query(Department).filter(Department.name == 'IT').one()
>>> it.employees
[]
>>> it.employees[0].name
u'john'
[/python]

As you can see, we inserted one employee, john, into the IT department.

Now let's perform the same kind of query using the expression language:

[python]
>>> from sqlalchemy import select
>>> find_it = select([Department.id]).where(Department.name == 'IT')
>>> rs = s.execute(find_it)
>>> rs

>>> rs.fetchone()
(1,)
>>> rs.fetchone() # Only one result is returned from the query, so getting one more returns None.
>>> rs.fetchone() # Since the previous fetchone() returned None, fetching more would lead to a result-closed exception
Traceback (most recent call last):
File "", line 1, in
File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 790, in fetchone
self.cursor, self.context)
File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1027, in _handle_dbapi_exception
util.reraise(*exc_info)
File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 781, in fetchone
row = self._fetchone_impl()
File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 700, in _fetchone_impl
self._non_result()
File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 724, in _non_result
raise exc.ResourceClosedError("This result object is closed.")
sqlalchemy.exc.ResourceClosedError: This result object is closed.
>>> find_john = select([Employee.id]).where(Employee.department_id == 1)
>>> rs = s.execute(find_john)

>>> rs.fetchone() # Employee John's ID
(1,)
>>> rs.fetchone()
[/python]

Since the Expression Language provides lower-level Python structures that mimic a backend-neutral SQL, it feels almost identical to writing actual SQL but in a Pythonic way.

Many-to-many between the departments and the employees

In our previous example, it's simple that one employee belongs to at most one department. What if an employee could belong to multiple departments? Isn't one foreign key not enough to represent this kind of relationship?

Yes, one foreign key is not enough. To model a many-to-many relationship between department and employee, we create a new association table with two foreign keys, one to 'department.id' and another to 'employee.id'.

[python]
>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> 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')
...
>>>
>>> class Employee(Base):
... __tablename__ = 'employee'
... id = Column(Integer, primary_key=True)
... name = Column(String)
... departments = relationship('Department', secondary='department_employee')
...
>>>
>>> class DepartmentEmployee(Base):
... __tablename__ = 'department_employee'
... department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
... employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
...
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>> s = session()
>>> john = Employee(name='john')
>>> s.add(john)
>>> it_department = Department(name='IT')
>>> it_department.employees.append(john)
>>> s.add(it_department)
>>> s.commit()
[/python]

In the previous example, we created an association table with two foreign keys. This association table 'department_employee' links 'department' and 'employee' and the relationships Department.employees and Employee.departments are to-many mapping between the tables. Notice the "magic-trick" to make this happen is the argument "secondary" we passed into the relationship() function in the Department and Employee model classes.

We can test our setup using the following queries:

[python]
>>> john = s.query(Employee).filter(Employee.name == 'john').one()
>>> john.departments
[]
>>> john.departments[0].name
u'IT'
>>> it = s.query(Department).filter(Department.name == 'IT').one()
>>> it.employees
[]
>>> it.employees[0].name
u'john'
[/python]

Now let's insert one more employee and another department into the database:

[python]
>>> marry = Employee(name='marry')
>>> financial_department = Department(name='financial')
>>> financial_department.employees.append(marry)
>>> s.add(marry)
>>> s.add(financial_department)
>>> s.commit()
[/python]

To find all the employees in the IT department, we can write it in ORM:

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

Or the Expression Language:

[python]
>>> find_employees = select([DepartmentEmployee.employee_id]).select_from(Department.__table__.join(DepartmentEmployee)).where(Department.name == 'IT')
>>> rs = s.execute(find_employees)
>>> rs.fetchone()
(1,)
>>> rs.fetchone()
[/python]

Now let's assign employee marry into the IT department so that she will be part of two departments.

[python]
>>> s.refresh(marry)
>>> s.refresh(it)
>>> it.employees
[]
>>> it.employees.append(marry)
>>> s.commit()
>>> it.employees
[, ]
[/python]

To find marry, i.e., all the employees who belong to at least two departments, we use group_by and having in an ORM query:

[python]
>>> from sqlalchemy import func
>>> s.query(Employee).join(Employee.departments).group_by(Employee.id).having(func.count(Department.id) > 1).one().name
[/python]

Similar to the ORM query, we can also use group_by and having in a Expression Language query:

[python]
>>> find_marry = select([Employee.id]).select_from(Employee.__table__.join(DepartmentEmployee)).group_by(Employee.id).having(func.count(DepartmentEmployee.department_id) > 1)
>>> s.execute(find_marry)

>>> rs = _
>>> rs.fetchall()
[(2,)]
[/python]

Of course, always remember to close the database session when you're done.

[python]
>>> s.close()
[/python]

Summary and Tips

In this article, we used an example database with two main tables and one association table to demonstrate how to write queries in SQLAlchemy's ORM and Expression Language. As a carefully designed API, writing queries is as easy as writing normal Python code. Since the Expression Language provides a lower-level API than the ORM, writing a query in the Expression Language feels more like writing one in a DBAPI such as psycopg2 and Python-MySQL. However, the lower-level API provided the Expression Language is more flexible than the ORM and its queries can be mapped into selectable SQL views in Python, which is very helpful as our queries become more and more complex. In the future articles, we are going to further explore how to utilize the Expression Language to make writing complex queries a joy instead of a pain.