Essential SQLAlchemy, 2nd Edition

Chapter 10. Reflection with SQLAlchemy ORM and Automap

As you learned in Chapter 5, reflection lets you populate a SQLAlchemy object from an existing database; reflection works on tables, views, indexes, and foreign keys. But what if you want to reflect a database schema into ORM-style classes? Fortunately, the handy SQLAlchemy extension automap lets you do just that.

Reflection via automap is a very useful tool; however, as of version 1.0 of SQLAlchemy we cannot reflect CheckConstraints, comments, or triggers. You also can’t reflect client-side defaults or an association between a sequence and a column. However, it is possible to add them manually using the methods we learned in Chapter 6.

Just like in Chapter 5, we are going to use the Chinook database for testing. We’ll be using the SQLite version, which is available in the CH11/ folder of this book’s sample code. That folder also contains an image of the database schema so you can visualize the schema we’ll be working with throughout this chapter.

Reflecting a Database with Automap

In order to reflect a database, instead of using the declarative_base we’ve been using with the ORM so far, we’re going to use the automap_base. Let’s start by creating a Base object to work with, as shown in Example 10-1

Example 10-1. Creating a Base object with automap_base

from sqlalchemy.ext.automap import automap_base 

Base = automap_base ...