linkedin Skip to Main Content
Just announced: We now support interviewing in spreadsheets!
CoderPad Open Menu
Back to blog

Understanding Transactions in SQLAlchemy

• Development

By EzzEddin Abdullah

Database transaction in SQLAlchemy follows the rule of 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, 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.

ℹ️ If this is your first time working with SQLAlchemy then you should check out How to Interact with Databases using SQLAlchemy with PostgreSQL.

Create the database engine

Start with creating the SQLAlchemy database engine, which is the home base for the actual database 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. 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 like SELECT, INSERT, UPDATE, and DELETE.

Define the database model

This inventory application is inspired by the Northwind database. 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.

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.

ℹ️ 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)

ℹ️ 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 in the session object. The new instance is not 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 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:

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 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 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 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 for more.