Essential SQLAlchemy, 2nd Edition

Chapter 5. Reflection

Reflection is a technique that allows us to populate a SQLAlchemy object from an existing database. You can reflect tables, views, indexes, and foreign keys. This chapter will explore how to use reflection on an example database.

For testing, I recommend using Chinook database. You can learn more about it at http://chinookdatabase.codeplex.com/. We’ll be using the SQLite version, which is available in the CH06/ folder of this book’s sample code. That folder also contains an image of the database schema so you can visualize the schema that we’ll be working with throughout this chapter. We’ll begin by reflecting a single table.

Reflecting Individual Tables

For our first reflection, we are going to generate the Artist table. We’ll need a meta-data object to hold the reflected table schema information, and an engine attached to the Chinook database. Example 5-1 demonstrates how to set up both of these things; the process should be very familiar to you now.

Example 5-1. Setting up our initial objects

from sqlalchemy import MetaData, create_engine
metadata = MetaData()
engine = create_engine('sqlite:///Chinook_Sqlite.sqlite') 

This connection string assumes you are in the same directory as the example database.

With the metadata and engine set up, we have everything ...