Chapter 3. Exceptions and Transactions

In the previous chapter, we did a lot of work with data in single statements, and we avoided doing anything that could result in an error. In this chapter, we will purposely perform some actions incorrectly so that we can see the types of errors that occur and how we should respond to them. We’ll conclude the chapter by learning how to group statements that need to succeed together into transactions so that we can ensure that either the group executes properly or is cleaned up correctly. Let’s start by blowing things up!


There are numerous exceptions that can occur in SQLAlchemy, but we’ll focus on the most common ones: AttributeErrors and IntegrityErrors. By learning how to handle these common exceptions, you’ll be better prepared to deal with the ones that occur less frequently.

To follow along with this chapter, make sure you start a new Python shell and load the tables that we built in Chapter 1 into your shell. Example 3-1 contains those tables and the connection again for reference.

Example 3-1. Setting up our shell environment
from datetime import datetime

from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
                        DateTime, ForeignKey, Boolean, create_engine,
metadata = MetaData()

cookies = Table('cookies', metadata,
    Column('cookie_id', Integer(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(255)),
    Column('cookie_sku', String(55)), ...

Get Essential SQLAlchemy, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.