Essential SQLAlchemy

Chapter 10. SqlSoup: An Automatic Mapper for SQLAlchemy

This chapter describes SqlSoup, an extension to SQLAlchemy that provides automatic mapping of introspected tables. You will learn how to use SqlSoup to map to an existing database and how to perform queries and updates. Finally, the chapter will describe the pros and cons of using SQLSoup, Elixir, or “bare” SQLAlchemy in your application.

Introduction to SqlSoup

If Elixir is ideally suited for blue sky, legacy-free development, SqlSoup is ideally suited for connecting to legacy databases. In fact, SqlSoup provides no method of defining a database schema through tables, classes, and mappers; it uses extensive autoloading to build the SQLAlchemy constructs (Tables, classes, and mapper()s) automatically from an existing database.

To illustrate the uses of SQLAlchemy in this chapter, we will use the following SQLAlchemy-created schema. Note that, unlike in previous chapters, we will be saving the test database in an on-disk SQLite database rather than using an in-memory database, to illustrate the fact that SqlSoup relies entirely on auto loading:

from sqlalchemy import * engine = create_engine('sqlite:///chapter10.db') metadata = MetaData(engine) product_table = Table( 'product', metadata, Column('sku', String(20), primary_key=True), Column('msrp', Numeric)) store_table = Table( 'store', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(255))) product_price_table = Table( 'product_price', metadata, Column('sku', ...