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