CRUD using SQLAlchemy ORM

Creating Session

When using SQLAlchemy ORM, we interact with the database using the Session object. The Session object also wraps the database connection and transaction. The transaction implicitly starts as soon as the Session starts communicating with the database and will remain open until the Session is committed, rolled back or closed.

One way to create a Session object is to use the Session class from the sqlalchemy.orm package.

You will have to create the Session object everytime you want to communicate with the database.

The Session constructor accepts a number of argument to customize its working. If we choose to create Session using this method, we would have to call the Session constructor with the same set of parameters over and over again throughout the application.

To make things easier, SQLAlchemy provides sessionmaker class which creates Session class with default arguments set for its constructor.

You should call call sessionmaker once in your application at the global scope.

Once we have access to the custom Session class you can instantiate it as many time as you need without passing any arguments to it.

Note that instantiating Session object doesn’t instantly establish connection to the database. The connection is established only when you start sending queries to the database.

Note: There is more to Session than this tutorial covers. For more details checkout the official documentation.

Inserting Data

To create a new record using SQLAlchemy ORM, we follow these steps:

  1. Create an object.
  2. Add the object to the session.
  3. Commit the session.

Let’s create two new Customer objects as follows:

Expected Output:

Here we have created two Customer objects. We can access the attributes of an object using the dot(.) operator as follows:

Expected Output:

Next, we add the objects to the session.

Adding objects to the session doesn’t actually writes them to the database, it only prepares the objects to be saved in the next commit. We can verify this by checking the primary key of the objects.

Expected Output:

The value of id attribute of both the objects is None. That means our objects are not yet saved in the database.

Instead of adding one object to the session at a time, we can use add_all() method. The add_all() method accepts a list of objects to be added to the session.

Adding an object to the session multiple times doesn’t throw any errors. At any time, you can view the objects added to the session using session.new.

Expected Output:

Finally, to save the objects to the database call commit() method as follows:

Once you commit the transaction, the connection resources referenced by the Session object is returned to the connection pool. Subsequent operations will occur in a new transaction.

Accessing the id attribute of the Customer object will now return the primary key instead of None.

Expected Output:

At this point, the Customer table should look like this:

Our customers haven’t ordered anything. So c1.orders and c2.orders would return an empty list.

Expected Output:

Let’s add some more customers to the customers table:

Before we can take orders, let’s add some products to the items table.

Create some orders now:

Notice that we are only adding Order objects (i.e o1 and o2) to the session. The Order and OrderLine object are associated with a one-to-many relationship. Adding an Order object to the session implicitly adds related OrderLine object to the session as well. However, even you still add the OrderLine object manually to the session, you will not get any error.

Instead of passing Order object at the time of creating OrderLine instance, we can also do this:

After this commit, the orders and order_lines table should now look like this:

[]

Access the orders attribute of the Customer object again, this time you will get a non-empty list like this:

Expected Output:

From the other side of the relationship, we can access the Customer object to which the order belongs to using the customer attribute on the Order object.

Expected Output:

The customer c1 now has two orders. To view the order lines in an order use the order_lines attribute of the Order object.

Expected Output:

To access the item in an order line use item attribute.

Expected Output:

Remember that, all of this became possible because of the relationship() we defined in our models.

Querying Data

To query database we use the query() method of the session object. The query() method returns an object of type sqlalchemy.orm.query.Query, simply called Query. The Query object represents the SELECT statement that will be used to query the database. The following table lists some common methods of the Query class.

Method Description
all() returns the result of the query (represented by Query) as a list.
count() returns the total number of records in the query.
first() returns the first result of the query or None, if there are no rows in the result.
scalar() returns the first column of the first row or None if the result set is empty. If multiple rows are encountered it throws MultipleResultsFound exception.
one returns exactly only row. If it encounters multiple rows it throws MultipleResultsFound exception. If the result set is empty it throws NoResultFound exception.
get(pk) returns an object that matches the given primary key (pk), or None, if no such object is found.
filter(*criterion) returns a new Query instance after applying the WHERE clause to the query.
limit(limit) return a new Query instance after applying the LIMIT clause to the query.
offset(offset) return a new Query instance after applying the OFFSET clause to the query.
order_by(*criterion) return a new Query instance after applying ORDER BY clause to the query.
join(*props, **kwargs) return a new Query instance after creating SQL INNER JOIN on the query.
outerjoin(*props, **kwargs) return a new Query instance after creating SQL LEFT OUTER JOIN on the query.
group_by(*criterion) return a new Query instance after adding GROUP BY clause to the query.
having(criterion) return a new Query instance after adding HAVING clause to the query.

all() method

In its simplest form, the query() method can take one or more model class or columns as arguments. The following code returns all the records from the customers table.

Expected Output:

Similarly, the following code returns all the records from the items and orders table.

Expected Output:

To get the raw SQL used to query the database simply print the sqlalchemy.orm.query.Query object as follows:

Expected Output:

Calling all() method on a large result set is inefficient instead we can use a for loop to iterate over the Query object as follows::

Expected Output:

The preceding queries have returned data from all columns of the table. We can prevent this by passing the column names explicitly to the query() method as follows:

Expected Output:

Notice that now each item in the list is a tuple instead of a model instance.

count() method

The count() method returns the number of results returned by the query.

Expected Output:

first() method

The first() method returns the first result of the query or None if the query returns zero results.

Expected Output:

get() method

The get() method returns the instance which matches the primary key passed to it or None if no such object found.

Expected Output:

filter() method

The filter() method allows us to filter the result by adding WHERE clause to the query. At the minimum, it accepts a column, an operator and a value. Here is an example:

Expected Output:

This query returns all the customers whose first name is John. The SQL equivalent of the query is:

Expected Output:

The string %(first_name_1)s in the WHERE clause is a placeholder and will be replaced by the actual value (i.e John) when the query is executed.

We can pass multiple filters to the filter() method and they will be joined together using SQL AND operator. For example:

Expected Output:

This query returns all the customers whose primary key is less than or equal to 5 and town name starts with Nor. Its SQL equivalent is:

Expected Output:

Another way to combine conditions is to use conjunctions (i.e. and_(), or_() and not_()). Here are some examples:

Expected Output:

The following listing shows how to use some common comparison operators with the filter() method.

IS NULL

Expected Output:

IS NOT NULL

Expected Output:

IN

Expected Output:

NOT IN

Expected Output:

BETWEEN

Expected Output:

NOT BETWEEN

Expected Output:

LIKE

Expected Output:

The like() method performs a case-sensitive match. For case-insensitive match use ilike().

Expected Output:

NOT LIKE

Expected Output:

limit() method

The limit() method adds LIMIT clause to the query. It accepts the number of rows you want to return from the query.

Expected Output:

The SQL equivalent of the above queries is as follows:

Expected Output:

offset() method

The offset() method adds the OFFSET clause to the query. It accepts offset as an argument. It is commonly used with the limit() clause.

Expected Output:

The SQL equivalent of the above query is as follows:

Expected Output:

order_by() method

The order_by() method is used to order the result by adding ORDER BY clause to the query. It accepts column names on which the order should be based. By default, it sorts in ascending order.

Expected Output:

To sort in descending order use desc() function as follows:

Expected Output:

join() method

The join() method is used to create SQL INNER JOIN. It accepts table name for which you want to create SQL JOIN.

Let’s use join() method to find all the customers who have one or more orders.

Expected Output:

This query is equivalent to the following SQL:

Expected Output:

The join() method is commonly used to get the data from one or more table in a single query. For example:

Expected Output:

We can create SQL JOIN for more than two table by chaining join() method as follows:

Here is another example, which uses 3 joins to find all the items in the John Green‘s first order.

Expected Output:

outerjoin() method

The outerjoin() method works just like join() but creates LEFT OUTER JOIN.

Expected Output:

In this query, the left table is the customers table. Thus, it will return all the rows from customers table (the left table), and only the rows that meet the join condition are returned from the orders table (the right table).

You can also create a FULL OUTER JOIN by passing full=True to outerjoin() method. For example:

Expected Output:

group_by() method

We group results using the group_by() method. It accepts one or more columns and groups the rows according to the values in the column.

The following query uses join() and group_by() to count the number of orders made by john green.

Expected Output:

having() method

To filter out the results based on the values returned by aggregate functions we use having() method which adds the HAVING clause to the SELECT statement. Just like the where() clause, it accepts a condition.

Expected Output:

Dealing with Duplicates

To deal with the duplicate rows in the result set we use the DISTINCT option. We can add DISTINCT option to the SELECT statement using the distinct() method. For example:

Expected Output:

Casting

Casting (converting) data from one type to another is a common operation and is done via cast() function from the sqlalchemy package.

Expected Output:

Unions

To union queries we use the union() method of the Query object. It takes one or more queries. For example:

Expected Output:

By default, union() removes all the duplicate rows from the result set. If you want to keep the duplicates use union_all().

Expected Output:

Updating Data

To update an object simply set its attribute to a new value, add the object to the session and commit the changes.

This way we can only update a single object at a time. To update multiple rows at once use update() method of the Query object. It returns the total number of records updated. For example:

Expected Output:

Deleting Data

To delete an object use the delete() method of the session object. It accepts an object and marks it to be deleted in the next commit.

Expected Output:

This commit removes the Monitor from the items table.

To delete multiple records at once use the delete() method of the Query object.

Expected Output:

This commit deletes all the items whose name start with W.

Raw Queries

ORM also give you the flexibility to directly to use directly use SQL using the text() function. For example:

Expected Output:

Transactions

A transaction is a way to execute a set of SQL statements such that either all of the statements are executed successfully or nothing at all. If any of the statement involved in the transaction fails then the database is returned to the state it was in before the transaction was initiated.

We currently have two orders in the database. The process of dispatching the order is as follows:

  1. Set the shipping date in the date_shipped column in orders table
  2. Subtract the quantity of ordered items from the items table

Both of these actions must be performed as a unit to ensure that the data in the tables are correct.

In the following listing, we define dispatch_order() method which accepts order_id as an argument, and performs the above-mentioned tasks in a transaction.

Our first order is for 3 chairs and 2 pens. Calling dispatch_order() function with order id of 1, will return the following output:

Expected Output:

At this point, items and order_lines tables should look like this:

[]

[]

Our next order is for 1 chair and 4 pens, but we now only have 3 pens in the stock!

Let’s try running dispatch_order() for second order and see what happens.

Expected Output:

As expected, our shipment failed because we don’t have enough pens in the stock and because we are using transaction our database is returned to the state it was in before the transaction was started.

I hope this tutorial would be informative for you. If you have encountered any typo or error. Get in touch using the contact page or comment box below.

Leave a Comment