This article is part 6 of 11 in the series Python SQLAlchemy Tutorial

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.

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.

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

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:

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.

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".

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

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:

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.

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.

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:

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.

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

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

About The Author

  • ShinChan

    Hi Xiaonuo!

    Thanks for the useful post, it helped me, but I still have a problem, because in my database, one of the tables which are involved in the ‘many-to-many’ relationship has two columns defined as primary key (in your example could be Department with ‘id’ and ‘countryId’), how would you do the relationship and the backrefs?

  • sbq234

    Hi Xiaonuo, thanks for nicely written tutorial. I’m trying to build my database in sqlalchemy but i have 4 tables and i’m not sure how to use many-to-many relation between my Category-with-Book (As single book can belong to multiple Category..Horror,Crime). Each category have 5 Books. Here is table : http://pastebin.com/NQGY209n and here is sqlfiddle where last case shows that a user rates a book that belong to multiple category. http://sqlfiddle.com/#!2/1fdea

    • Xiaonuo Gantan

      You need another table ‘category_link’ which is a link table between ‘category’ and ‘book’. I have created a new version of the table in pastebin: http://pastebin.com/qHGBiVNC .