---
title: "Understanding Transactions in SQLAlchemy | CoderPad"
description: "Database transactions are sequences of multiple operations performed on a database. In this blog post, Ezz walks through understanding transactions in SQLAlchemy."
canonical: "https://coderpad.io/blog/development/understanding-transactions-in-sqlalchemy/"
last_modified: "2026-04-15 16:21:07"
---
[Back to blog](https://coderpad.io/blog/)

# Understanding Transactions in SQLAlchemy

October 28, 2022 9 Minute Read

By EzzEddin Abdullah

[Development](https://coderpad.io/blog/development/)

Database transaction in SQLAlchemy follows the rule of [ACID](https://en.wikipedia.org/wiki/Acid) in relational databases. This tutorial shows how to understand transactions in a practical application. You’re going to learn why SQLAlchemy transactions matter in building applications like getting orders from an inventory.

You can use your own IDE and dependencies to try things out, and as always, this tutorial will use the already prepared environment, [CoderPad sandbox](https://app.coderpad.io/sandbox), which has all that you need from the dependencies in this application.

Configure your sandbox by selecting the Python 3 language environment. On the top left corner, click on the three dots and select the SQLAlchemy ( Postgres ) adapter from the list of database adapters.

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) If this is your first time working with SQLAlchemy then you should check out [How to Interact with Databases using SQLAlchemy with PostgreSQL](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/).

## Create the database engine

Start with creating the SQLAlchemy database engine, which is the [home base for the actual database](https://docs.sqlalchemy.org/en/20/core/engines.html) and the DBAPI. The DBAPI (Python Database API Specification) is the low-level API accessed by Python to be able to communicate with the database.

In our case, we’ll use`pyscopg2`, the DBAPI for PostgreSQL. See below how to establish the engine:

```
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

url = URL.create(
    drivername="postgresql"
    username="coderpad",
    host="/tmp/postgresql/socket"
    database="coderpad")
engine = create_engine(url)Code language: JavaScript (javascript)
```

The`url`instance has established the database configuration to talk to the CoderPad database. Feel free to update your own`url`if you’re not using the CoderPad sandbox.

Now, the engine is the starting point for our PostgreSQL database communication.

## Create the session instance

To interact with the database through`SELECT`statements and other queries, you need to instantiate an [SQLAlchemy session](https://docs.sqlalchemy.org/en/20/orm/session_basics.html). Instantiate it with the engine that you defined earlier:

```
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()Code language: JavaScript (javascript)
```

This`session`instance is what you need to do Object Relational Mapping (ORM) operations to emit [SQL statements](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/) like`SELECT`,`INSERT`,`UPDATE`, and`DELETE`.

## Define the database model

This inventory application is inspired by the [Northwind database](https://github.com/pthom/northwind_psql). You can consult the GitHub repo to explore the schema. We need only four tables for our minimal version:`customers`,`products`,`orders`, and`order_details`.

Create a new base class (Base instance) to initiate a declarative mapping to all classes that inherit from it. The example below shows how to inherit the first class representing the`customers`table:

```
from sqlalchemy import Column, String
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'

    customer_id = Column(String(5), primary_key=True)
    company_name = Column(String(30))
    contact_name = Column(String(30))
    country = Column(String(15))

    orders = relationship('Order', backref='customer')Code language: Python (python)
```

Now, the customers table is defined and has metadata of the customer info. This Customer class back-references customer into the orders table. We’ll see how to create a foreign key in the orders table.

If you want to get deeper with defining the data models, you can consult [this SQLAlchemy interaction with database article](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/).

Below is the definition of the other three tables:

```
from sqlalchemy import Integer, Boolean, ForeignKey, CheckConstraint

class Product(Base):
    __tablename__ = 'products'
    __table_args__ = (CheckConstraint(
        'unit_quantity>=0',
        'quantity should exist in the inventory'
        ),)

    id = Column(Integer(), primary_key=True)
    product_name = Column(String(40), nullable=False)
    unit_quantity = Column(Integer(), nullable=False)

    order_details = relationship('OrderDetail', backref='product')
   
class Order(Base):
    __tablename__ = 'orders'

    id = Column(Integer(), primary_key=True)
    customer_id = Column(String(5), ForeignKey('customers.customer_id'))
    shipped = Column(Boolean(), default=False)
    order_details = relationship('OrderDetail', backref='order')

class OrderDetail(Base):
    __tablename__ = 'order_details'

    id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.id'))
    product_id = Column(Integer(), ForeignKey('products.id'))
    unit_price = Column(Integer(), nullable=False)
    quantity = Column(Integer(), nullable=False)

Base.metadata.create_all(engine)Code language: Python (python)
```

The products table is defined with the product name and quantity per unit and the order details, which back-references product instance into the`order_details`table. This means the`orders_details`table would have a`product`column which should refer to a product field in the products table. Similarly, there is an`order_details`column in the products table, which should contain the order details/items of each product.

The constraint defined in the`__table_arg__`metadata ensures that the unit quantity equals or exceeds zero. If the unit quantity is zero, it means the product is finished and no longer exists in the inventory. If it’s bigger than zero, it means the product has a specific quantity in the inventory.

The`orders`table is defined with a shipped boolean value to indicate whether the product is shipped or not. It has the`order_details`back-referencing order to the`order_details`table. The orders table has`customer_id`as a foreign key to refer to the customers table.

Finally, the`order_details`table has metadata of`order_id`as a foreign key to the orders table,`product_id`as a foreign key to the products table,`unit_price`as a price for a product unit, and quantity to show the ordered quantity of the product that the customer has ordered.

All these objects (or tables) are created with the last command using`Base.metadata.create_all(engine)`.

## Inserting data

Now, you can insert data to define customers, products, orders, and order details. The examples below illustrate data insertions for our inventory app:

```
alfki = Customer(
    customer_id = 'ALFKI',
    company_name = 'Around the Horn',
    contact_name = 'Thomas Hardy',
    country='UK'
)

product_chai = Product(
    product_name='Chai',
    unit_quantity=12
    )

product_syrup = Product(
    product_name='Anissed Syrup',
    unit_quantity=2
    )

alfki_order1 = Order(customer = alfki)

order1_items = OrderDetail(
    order = alfki_order1,
    product = product_chai,
    unit_price = 18,
    quantity = 8
)

alfki_order2 = Order(customer = alfki)

order2_items_chai = OrderDetail(
    order = alfki_order2,
    product = product_chai,
    unit_price = 18,
    quantity = 3
)

order2_items_syrup = OrderDetail(
    order = alfki_order2,
    product = product_syrup,
    unit_price = 10,
    quantity = 5
)Code language: Python (python)
```

So we have 12 chai products and 2 anissed syrups. Alfki customer placed two orders. The first order is 8 chai while the second order consists of 3 chai and 5 anissed syrup.

After defining each object with the related table, you can now add the orders and order details in the database using the`.add_all()`method. Then you can commit all these changes to the database:

```
session.add_all([alfki_order1, order1_items, alfki_order2, order2_items_chai, order2_items_syrup])
session.commit()Code language: CSS (css)
```

Note how elegant and easy SQLAlchemy ORM is when inserting data. Every insertion is stored and operated as if it were a native Python object. Database relationships are all respected and a Python developer never has to write a bunch of insertion lines of SQL.

## Implement the transaction logic

Now is the time to ship the order defined by the order id. The following function shows the logic for shipping the order which represents the transaction in the database:

```
def ship_order(order_id):
    products_to_ship = session.query(OrderDetail).filter(OrderDetail.order_id == order_id)

    for product in products_to_ship:
        print(f"Product: {product.product_id}. Quantity: {product.quantity}.")
        ordered_product = session.query(Product).filter(Product.id == product.product_id).first()
        ordered_product.unit_quantity -= product.quantity

    shipped_order = session.query(Order).filter(Order.id == order_id)
    shipped_order.shipped = True
    session.commit()
   
    print(f"Your order is shipped. ID is: {order_id}")Code language: Python (python)
```

As you can see, the`products_to_ship`object is a Query object which selects records from the`order_details`table that match the`order_id`column.

The for loop that follows is looping over each product that should be shipped. The`ordered_product`object is the product record that is requested to be shipped. We then subtract the ordered quantity of the product from the actual quantity that exists in the inventory.

The`shipped_order`is the requested order record from the orders table. We mark the status of the shipped boolean value as True. Finally, we commit the changes to the database.

## Manage transactions

Transactions to the database will be clear to you starting from this section. To understand what’s happening to the database, let’s track the product quantity number before and after the order is placed:

```
print(session.query(Product.product_name, Product.unit_quantity).all())

ship_order(1)

print(session.query(Product.product_name, Product.unit_quantity).all())Code language: Python (python)
```

The output from the query above is:

```
[('Chai', 12), ('Anissed Syrup', 2)]
Product: 1. Quantity: 8.
Your order is shipped. ID is: 1
[('Anissed Syrup', 2), ('Chai', 4)]Code language: CSS (css)
```

As you can see, we ordered 8 products of product id of 1 (the Chai product). After the order is placed, the Chai quantity in our inventory becomes 4 (8 subtracted from 12).

Now, hit the Reset button on the right corner of the CoderPad sandbox and then ship the second order.

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) We’re resetting the environment to remove any change to the current session that happened in our database. You may want to remove the four tables if you’re not following along on the Pad sandbox.

Add the following`ship_order(2)`line:

```
print(session.query(Product.product_name, Product.unit_quantity).all())

ship_order(1)
ship_order(2)

print(session.query(Product.product_name, Product.unit_quantity).all())Code language: Python (python)
```

Before executing the last updated code, remember the second order. It consists of two products; 3 chai and 5 anissed syrup quantities. However, there are 12 chai and only 3 anissed syrup products in the inventory. This means after placing the second order, there is more demand for anissed syrup than the already supplied product in the inventory.

So if you run the code, you’ll see the following error:

```
IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (customer_id)=(ALFKI) already exists.

[SQL: INSERT INTO customers (customer_id, company_name, contact_name, country) VALUES (%(customer_id)s, %(company_name)s, %(contact_name)s, %(country)s)]
[parameters: {'customer_id': 'ALFKI', 'company_name': 'Around the Horn', 'contact_name': 'Thomas Hardy', 'country': 'UK'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)Code language: JavaScript (javascript)
```

The error shows an`IntegrityError`exception because it violates the rule we set for the quantity attribute in the products table.

The problem here does not only exist for this particular order. It also exists for the following orders. Say, another order is placed ordering another Chai from the same customer, Alfki:

```
alfki_order3 = Order(customer = alfki)

order3_items = OrderDetail(
    order = alfki_order3,
    product = product_chai,
    unit_price = 18,
    quantity = 1
)

session.add_all([alfki_order3, order3_items])
session.commit()Code language: Python (python)
```

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) Put the previous snippet to the REPL python window on the right of the Pad sandbox.

When you commit any change to the database like the above, you’ll get the following error:

```
raise sa_exc.PendingRollbackError(PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was...
```

This`PendingRollbackError`shows that the last transaction to the database was not successful. So you need to roll it back to the session first:

```
session.rollback()Code language: Python (python)
```

To request an order of the third order:

```
print(session.query(Product.product_name, Product.unit_quantity).all())

ship_order(3)

print(session.query(Product.product_name, Product.unit_quantity).all())Code language: Python (python)
```

Now the database transaction has been committed to the database:

```
[('Anissed Syrup', 2), ('Chai', 4)]
Product: 1. Quantity: 1.
Your order is shipped. ID is: 4
[('Anissed Syrup', 2), ('Chai', 3)]Code language: CSS (css)
```

But this was an ad-hoc fix to the pending rollback error using the`session.rollback()`. We want to catch this error whenever there is a violation of the database transaction. So use`try/except`and then roll back the session in the except block.

## SQLAlchemy rollback

To do a rollback in the SQLAlchemy ORM, use`session.rollback()`. Let’s revamp the`ship_order()`function to adapt the new try/except statement:

```
def ship_order(order_id):
    # ...
    shipped_order.shipped = True
    try:
        session.commit()
    except IntegrityError as e:
        print(f"QuantityViolationError: {e.orig}")
        session.rollback()

    print(f"Your order is shipped. ID is: {order_id}")Code language: Python (python)
```

Now, let’s place the two orders with the new change. Hit Reset and then hit Run and see the output:

```
[('Chai', 12), ('Anissed Syrup', 2)]
Product: 1. Quantity: 8.
Your order is shipped. ID is: 1
Product: 1. Quantity: 3.
Product: 2. Quantity: 5.QuantityViolationError: new row for relation "products" violates check co
nstraint "quantity should exist in the inventory"
DETAIL:  Failing row contains (2, Anissed Syrup, -3).

Your order is shipped. ID is: 2
[('Anissed Syrup', 2), ('Chai', 4)]Code language: JavaScript (javascript)
```

As you can see, the first order is placed and ready to be shipped. However, the second order is rolled back. So the transaction has not been completed, and there is no change to the quantity of the products.

This means if you place any other order, it will be transacted as long as it satisfies the constraints, as in the case of the unit quantity constraint.

If you placed the third order that we wrote above and committed it to the database, and then shipped this order using the`ship_order(3)`, you’d see a successful commit to the database.

## SQLAlchemy commit vs flush

Session in SQLAlchemy ORM is the start of any transaction. It means you can insert, delete, or update records in the database through the session. If you want to add a new change to the database, you’ll first use`session.add()`, which [places this new instance](https://docs.sqlalchemy.org/en/20/orm/session_basics.html#adding-new-or-existing-items) in the session object. The new instance is not [persistent](https://docs.sqlalchemy.org/en/20/glossary.html#term-persistent) in the database until it’s committed.

When you commit that instance to the database using`session.commit()`, and you’re doing two things under the hood:

1. Flushing the database with that new change so that you can query from the session object
2. Committing the new change to the database so that the database is updated with this new change

So the`session.commit()`calls`session.flush()`by default. To make it more clear, let’s first delete the third order from the database (if you committed that change already to the database):

```
>>> session.delete(alfki_order3)
>>> session.delete(order3_items)Code language: CSS (css)
```

Now,`alfki_order3`is just a Python object. Not attached to the session object, which means it’s in a [detached](https://docs.sqlalchemy.org/en/20/glossary.html#term-detached) state. To check if the third order is already deleted, run the following:

```
>>> session.query(Order.id).all()Code language: CSS (css)
```

You’ll see only the first and second orders.

Before you add these`aflki_order3`and`order3_items`objects again to the session, you need to return their object state to [transient](https://docs.sqlalchemy.org/en/20/glossary.html#term-transient):

```
from sqlalchemy.orm import make_transient

>>> make_transient(alfki_order3)
>>> make_transient(order3_items)Code language: JavaScript (javascript)
```

The transient state is where the object is not associated yet with the session.

The [given mapped instances](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.make_transient)`alfki_order3`and`order3_items`were in the detached state. That’s why you used`make_transient`.

You can add them back to the session:

```
>>> session.add_all([alfki_order3, order3_items])Code language: CSS (css)
```

Before committing the new changes to the database, you can query those changes from the session. That’s because the [autoflush](https://docs.sqlalchemy.org/en/20/orm/session_basics.html#session-flushing) is enabled by default when you declare a session. A quick check would be querying from the orders table:

```
>>> session.query(Order.id).all()Code language: CSS (css)
```

You’ll see the third order is included.

To disable`autoflush`, assign the`autoflush`session attribute to False:

```
>>> session.autoflush = FalseCode language: PHP (php)
```

Let’s experiment with how you can force a flush. Delete the two objects, convert their object states to transient, and then add both back to the session:

```
>>> session.delete(alfki_order3)
>>> session.delete(order3_items)
>>> make_transient(alfki_order3)
>>> make_transient(order3_items)
>>> session.add_all([alfki_order3, order3_items])Code language: CSS (css)
```

Now when you query from the orders table, you’ll not see the third order as before unless you flush the transaction:

```
>>> session.query(Order.id).all()
[[1,), (2,)]

>>> session.flush()

>>> session.query(Order.id).all()
[(1,), (2,), (3,)]Code language: CSS (css)
```

Nothing is changed in the database yet unless you [commit the current transaction](https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.commit) using`session.commit()`. This commit function flushes the pending changes, which means it always calls the`session.flush()`.

## Conclusion

In applications like CRMs or ordering apps, transactions play an important role in the database. This tutorial has covered a practical guide to how transactions work in SQLAlchemy ORM.

You started with building the data model of an inventory app and created the session instance. You inserted practical data and implemented the logic you needed for implementing transactions. You saw how to roll back a transaction when there is an exception like`IntergrityError`. And you finally learned the difference between commit and flush.

I’m Ezz. I’m an AWS Certified Machine Learning Specialist and a Data Platform Engineer. I help SaaS companies rank on Google. Check out my [website](https://ezzeddinabdullah.com/) for more.

## Subscribe for more CoderPad insights

## Trending Articles

Previous slide Next slide [Development

## What is Prompt Engineering?

Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post.

Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development

## A Guide To Using React’s useCallback() Hook

Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together.

Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development

## MySQL vs. PostgreSQL: How Do They Compare?

A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you.

Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development

## PostgreSQL LIKE Operator: A Detailed Guide

When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator.

Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development

## How To Use DataProvider In TestNG With Examples

When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG.

Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development

## How To Get Up And Running With Django Migrations: A Guide

Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects.

Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development

## Writing A Parameterized Test In JUnit With Examples

A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide.

Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development

## How To Use Assert In JUnit With Examples

The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples.

Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development

## A Simple Guide To TypeORM: What It Is And How To Use It

Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is.

Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development

## Using PreparedStatement in JDBC With Examples

In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code.

Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development

## React vs React Native: The Differences Explained

React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice.

Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development

## Logging in Django

The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article.

Read the post](https://coderpad.io/blog/development/logging-in-django/)

## Hire with confidence.

See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows.

[Request a demo](https://coderpad.io/sales/)