1. Home
2. SQLAlchemy Tutorial
3. CRUD using SQLAlchemy ORM

CRUD using SQLAlchemy ORM

Last updated on July 27, 2020

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.

1
2
3

from sqlalchemy.orm import create_engine, Session
engine = create_engine("postgres+psycopg2://postgres:pass@localhost/mydb")
session = Session(bind=engine)

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.

1
2

from sqlalchemy.orm import sessionmaker, Session
Session = sessionmaker(bind=engine)

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.

session = Session()

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

c1 = Customer(first_name = 'Toby', 
              last_name = 'Miller', 
              username = 'tmiller', 
              email = 'tmiller@example.com', 
              address = '1662 Kinney Street',
              town = 'Wolfden'
             )

c2 = Customer(first_name = 'Scott', 
              last_name = 'Harvey', 
              username = 'scottharvey', 
              email = 'scottharvey@example.com', 
              address = '424 Patterson Street',
              town = 'Beckinsdale'
             )
c1, c2

Expected Output:

(<Customer:None-johngreen>, <Customer:None-katwilson>)

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

1
2

c1.first_name, c1.last_name
c2.first_name, c2.last_name

Expected Output:

1
2

('John', 'Green')
('Katherine', 'Wilson')

Next, we add the objects to the session.

1
2

session.add(c1)
session.add(c2)

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.

c1.id, c2.id

Expected Output:

(None, None)

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.

session.add_all([c1, c2])

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.

session.new

Expected Output:

IdentitySet([<Customer:None-johngreen>, <Customer:None-katwilson>])

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

session.commit()

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.

c1.id, c2.id

Expected Output:

(1, 2)

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.

c1.orders, c2.orders

Expected Output:

([], [])

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

c3 = Customer(
            first_name = "John", 
            last_name = "Lara", 
            username = "johnlara", 
            email = "johnlara@mail.com", 
            address = "3073 Derek Drive",
            town = "Norfolk"
)

c4 = Customer(          
            first_name = "Sarah", 
            last_name = "Tomlin", 
            username = "sarahtomlin", 
            email = "sarahtomlin@mail.com",
            address = "3572 Poplar Avenue",
            town = "Norfolk"        
)

c5 = Customer(first_name = 'Toby', 
              last_name = 'Miller', 
              username = 'tmiller', 
              email = 'tmiller@example.com', 
              address = '1662 Kinney Street',
              town = 'Wolfden'
             )

c6 = Customer(first_name = 'Scott', 
              last_name = 'Harvey', 
              username = 'scottharvey', 
              email = 'scottharvey@example.com', 
              address = '424 Patterson Street',
              town = 'Beckinsdale'
             )

session.add_all([c3, c4, c5, c6])
session.commit()

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

i1 = Item(name = 'Chair', cost_price = 9.21, selling_price = 10.81, quantity = 5)
i2 = Item(name = 'Pen', cost_price = 3.45, selling_price = 4.51, quantity = 3)
i3 = Item(name = 'Headphone', cost_price = 15.52, selling_price = 16.81, quantity = 50)
i4 = Item(name = 'Travel Bag', cost_price = 20.1, selling_price = 24.21, quantity = 50)
i5 = Item(name = 'Keyboard', cost_price = 20.1, selling_price = 22.11, quantity = 50)
i6 = Item(name = 'Monitor', cost_price = 200.14, selling_price = 212.89, quantity = 50)
i7 = Item(name = 'Watch', cost_price = 100.58, selling_price = 104.41, quantity = 50)
i8 = Item(name = 'Water Bottle', cost_price = 20.89, selling_price = 25, quantity = 50)

session.add_all([i1, i2, i3, i4, i5, i6, i7, i8])
session.commit()

Create some orders now:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

o1 = Order(customer = c1)
o2 = Order(customer = c1)

line_item1 = OrderLine(order = o1, item = i1, quantity =  3)
line_item2 = OrderLine(order = o1, item = i2, quantity =  2)
line_item3 = OrderLine(order = o2, item = i1, quantity =  1)
line_item3 = OrderLine(order = o2, item = i2, quantity =  4)

session.add_all([o1, o2])

session.new
session.commit()

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

o3 = Order(customer = c1)
orderline1 = OrderLine(item = i1, quantity = 5)
orderline2 = OrderLine(item = i2, quantity = 10)

o3.order_lines.append(orderline1)
o3.order_lines.append(orderline2)

session.add_all([o3])

session.commit()

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:

c1.orders

Expected Output:

[<Order:8>, <Order:9>]

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.

o1.customer

Expected Output:

<Customer:1-tmiller>

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

c1.orders[0].order_lines, c1.orders[1].order_lines

Expected Output:

([<OrderLine:1>, <OrderLine:2>], [<OrderLine:3>, <OrderLine:4>])

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

1
2
3
4
5
6
7

for ol in c1.orders[0].order_lines:
    ol.id, ol.item, ol.quantity
    
print('-------')
    
for ol in c1.orders[1].order_lines:
    ol.id, ol.item, ol.quantity

Expected Output:

1
2
3
4
5

(1, <Item:1-Chair>, 3)
(2, <Item:2-Pen>, 2)
-------
(3, <Item:1-Chair>, 1)
(4, <Item:2-Pen>, 4)

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.

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.

session.query(Customer).all()

Expected Output:

1
2
3
4
5
6

[<Customer:1-jongreen>,
 <Customer:2-katwilson>,
 <Customer:3-johnlara>,
 <Customer:4-sarahtomlin>,
 <Customer:5-tmiller>,
 <Customer:6-scottharvey>]

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

1
2

session.query(Item).all()
session.query(Order).all()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[<Item:1-Chair>,
 <Item:2-Pen>,
 <Item:3-Headphone>,
 <Item:4-Travel Bag>,
 <Item:5-Keyboard>,
 <Item:6-Monitor>,
 <Item:7-Watch>,
 <Item:8-Water Bottle>]

[<Order:1>, <Order:2>, <Order:3>]

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

print(session.query(Customer))

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    customers. ID AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers

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::

1
2
3
4

q = session.query(Customer)

for c in q:
    print(c.id, c.first_name)

Expected Output:

1
2
3
4
5
6

1 John
2 Katherine
3 John
4 Sarah
5 Toby
6 Scott

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:

session.query(Customer.id, Customer.first_name).all()

Expected Output:

1
2
3
4
5
6

[(1, 'John'),
 (2, 'Katherine'),
 (3, 'John'),
 (4, 'Sarah'),
 (5, 'Toby'),
 (6, 'Scott')]

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.

1
2
3

session.query(Customer).count() # get the total number of records in the customers table
session.query(Item).count()  # get the total number of records in the items table
session.query(Order).count()  # get the total number of records in the orders table

Expected Output:

1
2
3

6
8
3

first() method #

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

1
2
3

session.query(Customer).first()
session.query(Item).first()
session.query(Order).first()

Expected Output:

1
2
3

<Customer:1-johngreen>
<Item:1-Chair>
<Order:1>

get() method #

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

1
2
3

session.query(Customer).get(1)
session.query(Item).get(1)
session.query(Order).get(100)

Expected Output:

1
2

<Customer:1-johngreen>
<Item:1-Chair>

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:

session.query(Customer).filter(Customer.first_name == 'John').all()

Expected Output:

[<Customer:1-johngreen>, <Customer:3-johnlara>]

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

print(session.query(Customer).filter(Customer.first_name == 'John'))

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
    customers.id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
WHERE
    customers.first_name = %(first_name_1)s

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:

session.query(Customer).filter(Customer.id <= 5, Customer.town == "Norfolk").all()

Expected Output:

[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]

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:

print(session.query(Customer).filter(Customer.id <= 5, Customer.town.like("Nor%"))

Expected Output:

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# find all customers who either live in Peterbrugh or Norfolk

session.query(Customer).filter(or_(
    Customer.town == 'Peterbrugh', 
    Customer.town == 'Norfolk'
)).all()


# find all customers whose first name is John and live in Norfolk

session.query(Customer).filter(and_(
    Customer.first_name == 'John', 
    Customer.town == 'Norfolk'
)).all()


# find all johns who don't live in Peterbrugh

session.query(Customer).filter(and_(
    Customer.first_name == 'John', 
    not_(
        Customer.town == 'Peterbrugh', 
    )
)).all()

Expected Output:

1
2
3
4
5
6
7
8

[<Customer:1-johngreen>,
 <Customer:2-katwilson>,
 <Customer:3-johnlara>,
 <Customer:4-sarahtomlin>]

[<Customer:1-johngreen>, <Customer:3-johnlara>]

[<Customer:1-johngreen>, <Customer:3-johnlara>]

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

IS NULL #

session.query(Order).filter(Order.date_shipped == None).all()

Expected Output:

[<Order:1>, <Order:2>, <Order:3>]

IS NOT NULL #

session.query(Order).filter(Order.date_shipped != None).all()

Expected Output:

[]

IN #

session.query(Customer).filter(Customer.first_name.in_(['Toby', 'Sarah'])).all()

Expected Output:

[<Customer:4-sarahtomlin>, <Customer:5-tmiller>]

NOT IN #

session.query(Customer).filter(Customer.first_name.notin_(['Toby', 'Sarah'])).all()

Expected Output:

1
2
3
4

[<Customer:1-johngreen>,
 <Customer:2-katwilson>,
 <Customer:3-johnlara>,
 <Customer:6-scottharvey>]

BETWEEN #

session.query(Item).filter(Item.cost_price.between(10, 50)).all()

Expected Output:

1
2
3
4

[<Item:3-Headphone>,
 <Item:4-Travel Bag>,
 <Item:5-Keyboard>,
 <Item:8-Water Bottle>]

NOT BETWEEN #

session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()

Expected Output:

[<Item:1-Chair>, <Item:2-Pen>, <Item:6-Monitor>, <Item:7-Watch>]

LIKE #

session.query(Item).filter(Item.name.like("%r")).all()

Expected Output:

[<Item:1-Chair>, <Item:6-Monitor>]

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

session.query(Item).filter(Item.name.ilike("w%")).all()

Expected Output:

[<Item:7-Watch>, <Item:8-Water Bottle>]

NOT LIKE #

session.query(Item).filter(not_(Item.name.like("W%"))).all()

Expected Output:

1
2
3
4
5
6

[<Item:1-Chair>,
 <Item:2-Pen>,
 <Item:3-Headphone>,
 <Item:4-Travel Bag>,
 <Item:5-Keyboard>,
 <Item:6-Monitor>]

limit() method #

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

1
2

session.query(Customer).limit(2).all()
session.query(Customer).filter(Customer.address.ilike("%avenue")).limit(2).all()

Expected Output:

1
2
3

[<Customer:1-johngreen>, <Customer:2-katwilson>]

[<Customer:2-katwilson>, <Customer:4-sarahtomlin>]

The SQL equivalent of the above queries is as follows:

1
2

print(session.query(Customer).limit(2))
print(session.query(Customer).filter(Customer.address.ilike("%avenue")).limit(2))

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

SELECT
    customers. id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
LIMIT %(param_1)s 

SELECT
    customers.id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
WHERE
    customers.address ILIKE % (address_1)s
LIMIT %(param_1)s

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.

session.query(Customer).limit(2).offset(2).all()

Expected Output:

[<Customer:3-johnlara>, <Customer:4-sarahtomlin>]

The SQL equivalent of the above query is as follows:

print(session.query(Customer).limit(2).offset(2))

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    customers. ID AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_addrees,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
LIMIT %(param_1)s OFFSET %(param_2)s

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.

1
2

session.query(Item).filter(Item.name.ilike("wa%")).all()
session.query(Item).filter(Item.name.ilike("wa%")).order_by(Item.cost_price).all()

Expected Output:

1
2

[<Item:7-Watch>, <Item:8-Water Bottle>]
[<Item:8-Water Bottle>, <Item:7-Watch>]

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

1
2

from sqlalchemy import desc
session.query(Item).filter(Item.name.ilike("wa%")).order_by(desc(Item.cost_price)).all()

Expected Output:

[<Item:7-Watch>, <Item:8-Water Bottle>]

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.

session.query(Customer).join(Order).all()

Expected Output:

[<Customer:1-johngreen>]

This query is equivalent to the following SQL:

print(session.query(Customer).join(Order))

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    customers.id AS customers_id,
    customers.first_name AS customers_first_name,
    customers.last_name AS customers_last_name,
    customers.username AS customers_username,
    customers.email AS customers_email,
    customers.address AS customers_address,
    customers.town AS customers_town,
    customers.created_on AS customers_created_on,
    customers.updated_on AS customers_updated_on
FROM
    customers
JOIN orders ON customers.id = orders.customer_id

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

session.query(Customer.id, Customer.username, Order.id).join(Order).all()

Expected Output:

[(1, 'johngreen', 1), (1, 'johngreen', 2), (1, 'johngreen', 3)]

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

session.query(Table1).join(Table2).join(Table3).join(Table4).all()

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

session.query(
    Customer.first_name, 
    Item.name, 
    Item.selling_price, 
    OrderLine.quantity
).join(Order).join(OrderLine).join(Item).filter(
    Customer.first_name == 'John',
    Customer.last_name == 'Green',
    Order.id == 1,
).all()

Expected Output:

[('John', 'Chair', Decimal('10.81'), 3), ('John', 'Pen', Decimal('4.51'), 2)]

outerjoin() method #

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

1
2
3
4

session.query(        
    Customer.first_name,
    Order.id,
).outerjoin(Order).all()

Expected Output:

1
2
3
4
5
6
7
8

[('John', 1),
 ('John', 2),
 ('John', 3),
 ('Katherine', None),
 ('Toby', None),
 ('Scott', None),
 ('Sarah', None),
 ('John', None)]

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:

1
2
3
4

session.query(        
    Customer.first_name,
    Order.id,
).outerjoin(Order, full=True).all()

Expected Output:

1
2
3
4
5
6
7
8

[('John', 1),
 ('John', 2),
 ('John', 3),
 ('Katherine', None),
 ('Toby', None),
 ('Scott', None),
 ('Sarah', None),
 ('John', None)]

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.

1
2
3
4
5
6

from sqlalchemy import func

session.query(func.count(Customer.id)).join(Order).filter(
    Customer.first_name == 'John',
    Customer.last_name == 'Green',    
).group_by(Customer.id).scalar()

Expected Output:

3

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.

1
2
3
4
5
6

# find the number of customers lives in each town

session.query(
    func.count("*").label('town_count'),    
    Customer.town
).group_by(Customer.town).having(func.count("*") > 2).all()

Expected Output:

[(3, 'Norfolk')]

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:

1
2
3
4
5
6
7
8
9

from sqlalchemy import distinct

session.query(Customer.town).filter(Customer.id  < 10).all()
session.query(Customer.town).filter(Customer.id  < 10).distinct().all()

session.query(        
    func.count(distinct(Customer.town)),
    func.count(Customer.town)
).all()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[('Norfolk'),
 ('Peterbrugh'),
 ('Norfolk'),
 ('Norfolk'),
 ('Wolfden'),
 ('Beckinsdale')]

[('Peterbrugh'), ('Beckinsdale'), ('Wolfden'), ('Norfolk')]

[(4, 6)]

Casting #

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

1
2
3
4
5
6
7
8

from sqlalchemy import cast, Date, distinct, union

session.query(
    cast(func.pi(), Integer),
    cast(func.pi(), Numeric(10,2)),
    cast("2010-12-01", DateTime),
    cast("2010-12-01", Date),
).all()

Expected Output:

1
2
3
4

[(3,
  Decimal('3.14'),
  datetime.datetime(2010, 12, 1, 0, 0),
  datetime.date(2010, 12, 1))]

Unions #

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

1
2
3

s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
s1.union(s2).all()

Expected Output:

1
2
3
4
5
6

[(2, 'Pen'),
 (4, 'Travel Bag'),
 (3, 'Headphone'),
 (5, 'Keyboard'),
 (7, 'Watch'),
 (8, 'Water Bottle')]

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

s1.union_all(s2).all()

Expected Output:

1
2
3
4
5
6
7

[(7, 'Watch'),
 (8, 'Water Bottle'),
 (2, 'Pen'),
 (3, 'Headphone'),
 (4, 'Travel Bag'),
 (5, 'Keyboard'),
 (8, 'Water Bottle')]

Updating Data #

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

1
2
3
4

i = session.query(Item).get(8)
i.selling_price = 25.91
session.add(i)
session.commit()

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:

1
2
3
4
5
6

# update quantity of all quantity of items to 60 whose name starts with 'W'

session.query(Item).filter(
    Item.name.ilike("W%")
).update({"quantity": 60}, synchronize_session='fetch')
session.commit()

Expected Output:

2

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.

1
2
3
4

i = session.query(Item).filter(Item.name == 'Monitor').one()
i
session.delete(i)
session.commit()

Expected Output:

<Item:6-Monitor>

This commit removes the Monitor from the items table.

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

1
2
3
4

session.query(Item).filter(
    Item.name.ilike("W%")
).delete(synchronize_session='fetch')
session.commit()

Expected Output:

2

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:

1
2
3
4
5
6
7

from sqlalchemy import text

session.query(Customer).filter(text("first_name = 'John'")).all()

session.query(Customer).filter(text("town like 'Nor%'")).all()

session.query(Customer).filter(text("town like 'Nor%'")).order_by(text("first_name, id desc")).all()

Expected Output:

1
2
3
4
5

[<Customer:1-johngreen>, <Customer:3-johnlara>]

[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]

[<Customer:3-johnlara>, <Customer:1-johngreen>, <Customer:4-sarahtomlin>]

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

from sqlalchemy import update
from sqlalchemy.exc import IntegrityError
from datetime import datetime


def dispatch_order(order_id):

    # check whether order_id is valid or not
    order = session.query(Order).get(order_id)
    
    if not order:
        raise ValueError("Invalid order id: {}.".format(order_id))    
        
    if order.date_shipped:
        print("Order already shipped.")
        return

    try:
        for i in order.order_lines:
            i.item.quantity = i.item.quantity - i.quantity            
        
        order.date_shipped = datetime.now()                            
        session.commit()
        print("Transaction completed.")

    except IntegrityError as e:
        print(e)
        print("Rolling back ...")
        session.rollback()
        print("Transaction failed.")

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

dispatch_order(1)

Expected Output:

Transaction completed.

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.

dispatch_order(2)

Expected Output:

1
2
3
4
5

(psycopg2.IntegrityError) new row for relation "items" violates check constraint "quantity_check"
DETAIL:  Failing row contains (2, Pen, 3.45, 4.51, -3).
 [SQL: 'UPDATE items SET quantity=%(quantity)s WHERE items.id = %(items_id)s'] [parameters: ({'quantity': 1, 'items_id': 1}, {'quantity': -3, 'items_id': 2})] (Background on this error at: http://sqlalche.me/e/gkpj)
Rolling back ...
Transaction failed.

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.