Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction to SQLAlchemy

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy is a Python Library created by Mike Bayer to provide a high-level, (idiomatically Python) interface to relational databases such as Oracle, DB2, MySQL, PostgreSQL, and SQLite. SQLAlchemy attempts to be unobtrusive to your Python code, allowing you to map plain old Python objects (POPOs) to database tables without substantially changing your existing Python code. SQLAlchemy includes a database server-independent SQL expression language and an object-relational mapper (ORM) that lets you use SQL to persist your application objects automatically. This chapter will introduce you to SQLAlchemy, illustrating some of its more powerful features. Later chapters will provide more depth for the topics covered here.

If you have used lower-level database interfaces with Python, such as the DB-API, you may be used to writing code such as the following to save your objects to the database:

sql="INSERT INTO user(user_name, password) VALUES (%s, %s)"
cursor = conn.cursor()
cursor.execute(sql, ('rick', 'parrot'))

Although this code gets the job done, it is verbose, error-prone, and tedious to write. Using string manipulation to build up a query as done here can lead to various logical errors and vulnerabilities such as opening your application up to SQL injection attacks. Generating the string to be executed by your database server verbatim also ties your code to the particular DB-API driver you are currently using, making migration to a different database server difficult. For instance, if we wished to migrate the previous example to the Oracle DB-API driver, we would need to write:

sql="INSERT INTO user(user_name, password) VALUES (:1, :2)"
cursor = conn.cursor()
cursor.execute(sql, 'rick', 'parrot')

SQL injection is a type of programming error where carefully crafted user input can cause your application to execute arbitrary SQL code. For instance, suppose that the DB-API code in the earlier listing had been written as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

What Is SQLAlchemy

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy is a Python Library created by Mike Bayer to provide a high-level, (idiomatically Python) interface to relational databases such as Oracle, DB2, MySQL, PostgreSQL, and SQLite. SQLAlchemy attempts to be unobtrusive to your Python code, allowing you to map plain old Python objects (POPOs) to database tables without substantially changing your existing Python code. SQLAlchemy includes a database server-independent SQL expression language and an object-relational mapper (ORM) that lets you use SQL to persist your application objects automatically. This chapter will introduce you to SQLAlchemy, illustrating some of its more powerful features. Later chapters will provide more depth for the topics covered here.

If you have used lower-level database interfaces with Python, such as the DB-API, you may be used to writing code such as the following to save your objects to the database:

sql="INSERT INTO user(user_name, password) VALUES (%s, %s)"
cursor = conn.cursor()
cursor.execute(sql, ('rick', 'parrot'))

Although this code gets the job done, it is verbose, error-prone, and tedious to write. Using string manipulation to build up a query as done here can lead to various logical errors and vulnerabilities such as opening your application up to SQL injection attacks. Generating the string to be executed by your database server verbatim also ties your code to the particular DB-API driver you are currently using, making migration to a different database server difficult. For instance, if we wished to migrate the previous example to the Oracle DB-API driver, we would need to write:

sql="INSERT INTO user(user_name, password) VALUES (:1, :2)"
cursor = conn.cursor()
cursor.execute(sql, 'rick', 'parrot')

SQL injection is a type of programming error where carefully crafted user input can cause your application to execute arbitrary SQL code. For instance, suppose that the DB-API code in the earlier listing had been written as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The Object/Relational “Impedance Mismatch”

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although a SQL database is a powerful and flexible modeling tool, it is not always a good match for the object-oriented programming style. SQL is good for some things, and object-oriented programming is good for others. This is sometimes referred to as the object/relational “impedance mismatch,” and it is a problem that SQLAlchemy tries to address in the ORM. To illustrate the object/relational impedance mismatch, let’s first look at how we might model a system in SQL, and then how we might model it in an object-oriented way.

SQL databases provide a powerful means for modeling data and allowing for arbitrary queries of that data. The model underlying SQL is the relational model. In the relational model, modeled items (entities) can have various attributes, and are related to other entities via relationships. These relationships can be one-to-one, one-to-many, many-to-many, or complex, multientity relationships. The SQL expression of the entity is the table, and relationships are expressed as foreign key constraints, possibly with the use of an auxiliary “join” table. For example, suppose we have a user permission system that has users who may belong to one or more groups. Groups may have one or more permissions. Our SQL to model such a system might be something like the following:

CREATE TABLE tf_user (
        id INTEGER NOT NULL, 
        user_name VARCHAR(16) NOT NULL, 
        email_address VARCHAR(255) NOT NULL, 
        password VARCHAR(40) NOT NULL, 
        first_name VARCHAR(255), 
        last_name VARCHAR(255), 
        created TIMESTAMP, 
        PRIMARY KEY (id), 
         UNIQUE (user_name), 
         UNIQUE (email_address));
CREATE TABLE tf_group (
        id INTEGER NOT NULL, 
        group_name VARCHAR(16) NOT NULL, 
        PRIMARY KEY (id), 
         UNIQUE (group_name));
CREATE TABLE tf_permission (
        id INTEGER NOT NULL, 
        permission_name VARCHAR(16) NOT NULL, 
        PRIMARY KEY (id), 
         UNIQUE (permission_name));
-- Relate the user and group tables
CREATE TABLE user_group (
        user_id INTEGER, 
        group_id INTEGER, 
	 PRIMARY KEY(user_id, group_id),
         FOREIGN KEY(user_id) REFERENCES tf_user (id), 
         FOREIGN KEY(group_id) REFERENCES tf_group (id));
-- Relate the group and permission tables
CREATE TABLE group_permission (
        group_id INTEGER, 
        permission_id INTEGER, 
	 PRIMARY KEY(group_id, permission_id),
         FOREIGN KEY(group_id) REFERENCES tf_group (id), 
         FOREIGN KEY(permission_id) REFERENCES tf_permission (id));

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQLAlchemy Philosophy

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy was created with the goal of letting your objects be objects, and your tables be tables. The SQLAlchemy home page puts it this way:

From

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

Using the object mapper pattern (where plain Python objects are mapped to SQL tables via a mapper object, rather than requiring persistent objects to be derived from some Persistable class) achieves much of this separation of concerns. There has also been a concerted effort in SQLAlchemy development to expose the full power of SQL, should you wish to use it.

In SQLAlchemy, your objects are POPOs until you tell SQLAlchemy about them. This means that it is entirely possible to “bolt on” persistence to an existing object model by mapping the classes to tables. For instance, consider an application that uses users, groups, and permissions, as shown. You might prototype your application with the following class definitions:

class User(object):

    def __init__(self, user_name=None, password=None, groups=None):
        if groups is None: groups = []
        self.user_name = user_name
        self.password = password
        self._groups = groups

    def join_group(self, group):
        self._groups.append(group)

    def leave_group(self, group):
        self._groups.remove(group)

class Group(object):

def __init__(self, group_name=None, users=None, permissions=None):
        if users is None: users = []
        if permissions is None: permissions = []
        self.group_name = group_name
        self._users = users
        self._permissions = permissions

    def add_user(self, user):
        self._users.append(user)

    def del_user(self, user):
        self._users.remove(user)

    def add_permission(self, permission):
        self._permissions.append(permission)

    def del_permission(self, permission):
        self._permissions.remove(permission)

class Permission(object):

    def __init__(self, permission_name=None, groups=None):
        self.permission_name = permission_name
        self._groups = groups

    def join_group(self, group):
        self._groups.append(group)

    def leave_group(self, group):
        self._groups.remove(group)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQLAlchemy Architecture

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy consists of several components, including the aforementioned database-independent SQL expression language object-relational mapper. In order to enable these components, SQLAlchemy also provides an Engine class, which manages connection pools and SQL dialects, a MetaData class, which manages your table information, and a flexible type system for mapping SQL types to Python types.

The beginning of any SQLAlchemy application is the Engine. The engine manages the SQLAlchemy connection pool and the database-independent SQL dialect layer. In our previous examples, the engine was created implicitly when the MetaData was created:

metadata=MetaData('sqlite://')
engine = metadata.bind

It is also possible to create an engine manually, using the SQLAlchemy function create_engine():

engine=create_engine('sqlite://')

This engine can later be bound to a MetaData object just by setting the bind attribute on the MetaData:

metadata.bind = engine

The engine can also be used in SQL statements such as table creation if the MetaData is unbound (not connected to a particular engine):

user_table.create(bind=engine)

The engine can be used to execute queries directly on the database via dynamic SQL:

for row in engine.execute("select user_name from tf_user"):
    print 'user name: %s' % row['user_name']

Most of the time, you will be using the higher-level facilities of the SQL expression language and ORM components of SQLAlchemy, but it’s nice to know that you can always easily drop down all the way to raw SQL if you need to.

Connection pooling

Thus far, we have glossed over the use of database connections. In fact, all of our examples up to this point have used SQLAlchemy’s powerful connection pooling subsystem. In order to execute queries against a database, a connection is required, and the establishment of a new connection is typically an expensive operation, involving a network connection, authentication of the user, and any database session setup required. To amortize the costs, the typical solution is to maintain a pool of database connections that are used over and over again in the application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Getting Started

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter guides you through installing version 0.4 of SQLAlchemy (the version documented by this book) via EasyInstall. It will also give you a quick tutorial on the basic features of SQLAlchemy to “get your hands dirty” as soon as possible.

In order to use SQLAlchemy, you need to install the SQLAlchemy package as well as a Python database driver for your database. This section will guide you through installing both.

Installing the SQLAlchemy is a straightforward process involving the widely used

Installing setup tools

SQLAlchemy is distributed as an EGG file via the Python package index (PyPI), also known as the CheeseShop. If you have installed EGGs before using easy_install, you can skip to the next section. Otherwise, you will need to install SetupTools, a package that enhances the Python standard library-provided distutils package.

SetupTools includes a tool called easy_install, which can be used to install various Python modules from the CheeseShop. easy_install is particularly good at resolving dependencies between Python packages and installing a package’s dependencies along with the package itself. If you intend to take advantage of the rich library of free software available in the CheeseShop, or if you intend to take advantage of the benefits of distributing your own code through SetupTools, it is a good idea to become familiar with all its features. You can find more documentation on SetupTools at .

To install SetupTools, first download the bootstrap script ez_setup.py from . You will then need to run the script to download the rest of SetupTools.

In Windows, you must make certain that you have administrator privileges before running easy_install or ez_setup.py, as both of these scripts modify your Python site-packages directory.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Installing SQLAlchemy

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In order to use SQLAlchemy, you need to install the SQLAlchemy package as well as a Python database driver for your database. This section will guide you through installing both.

Installing the SQLAlchemy is a straightforward process involving the widely used

Installing setup tools

SQLAlchemy is distributed as an EGG file via the Python package index (PyPI), also known as the CheeseShop. If you have installed EGGs before using easy_install, you can skip to the next section. Otherwise, you will need to install SetupTools, a package that enhances the Python standard library-provided distutils package.

SetupTools includes a tool called easy_install, which can be used to install various Python modules from the CheeseShop. easy_install is particularly good at resolving dependencies between Python packages and installing a package’s dependencies along with the package itself. If you intend to take advantage of the rich library of free software available in the CheeseShop, or if you intend to take advantage of the benefits of distributing your own code through SetupTools, it is a good idea to become familiar with all its features. You can find more documentation on SetupTools at .

To install SetupTools, first download the bootstrap script ez_setup.py from . You will then need to run the script to download the rest of SetupTools.

In Windows, you must make certain that you have administrator privileges before running easy_install or ez_setup.py, as both of these scripts modify your Python site-packages directory.

In Windows, it’s also a good idea to make sure that Python and your Python scripts directories are on your path. In the default Python installation, these directories are c:\python25 and c:\python25\scripts

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQLAlchemy Tutorial

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once you have installed SQLAlchemy and the SQLite driver (either pysqlite or sqlite3), you can start really exploring SQLAlchemy. This tutorial shows off some of the basic features of SQLAlchemy that you can use to become immediately productive. This tutorial is based on a stripped-down version of a user authentication module that might be used in a web application.

Before doing anything, we need to import the modules we will use. In this case, for simplicity’s sake, we will simply import everything from the sqlalchemy package. We will also import the datetime class from the datetime package for use in defining default values for our tables.

from sqlalchemy import *
from datetime import datetime

To connect to the database, we will create a MetaData object, which is used by to keep track of the tables we define:

metadata = MetaData('sqlite:///tutorial.sqlite')

The MetaData object we create is bound to a particular database Engine, in this case a SQLite engine connected to the database in the file tutorial.sqlite. If tutorial.sqlite does not already exist, it will be created automatically by SQLite.

Once we have created our MetaData, we can define our tables. The first table defined is the user table:

user_table = Table(
    'tf_user', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_name', Unicode(16),
           unique=True, nullable=False),
    Column('password', Unicode(40), nullable=False),
    Column('display_name', Unicode(255), default=''),
    Column('created', DateTime, default=datetime.now))

Notice how the Table constructor is given the SQL name of the table ('tf_user'), a reference to the metadata object, and a list of columns. The columns are similarly defined with their SQL names, data types, and various optional constraints. In this case, since we defined an 'id' column as a primary key, SQLAlchemy will automatically create the column with an auto-increment default value. Also note that we can specify uniqueness and nullability constraints on columns, provide literal defaults, or provide Python callables (e.g.,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Engines and MetaData

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduces SQLAlchemy’s Engine and MetaData classes. The Engine class provides database connectivity, including a connection pool with various strategies for acquiring connections from the pool. The MetaData class maintains information about your database schema, including any tables and indexes defined. In this chapter, you will learn how to define a new database schema using MetaData as well as how to connect a MetaData instance to an existing schema.

The SQLAlchemy-provided Engine class is responsible for managing the connection to the database. It does this by incorporating a database connection pool and a database-specific Dialect layer to translate the SQL expression language () into database-specific SQL.

To get started using an Engine, you use the create_engine() function:

# Create a connection to a SQLite in-memory database
engine = create_engine('sqlite://')

# Create a connection to a SQLite on-disk database "data.sqlite"
engine = create_engine('sqlite:///data.sqlite')

# Create a connection to a PostGreSQL database
engine = create_engine('postgres://rick:foo@localhost:5432/pg_db')

# Create a connection to a MySQL database
engine = create_engine('mysql://localhost/mysql_db')

# Create a connection to an Oracle database (via TNS)
engine = create_engine('oracle://rick:foo@oracle_tns')

# Create a connection to an Oracle database (without a TNS name)
engine =
... create_engine('oracle://rick:foo@localhost:1521/oracle_sid')

The first argument to create_engine() is the RFC-1738 style URL specifying the database. The general form of the url is: driver://username:password@host:port/database. Of course, the various database drivers interpret these URLs in slightly different ways, as illustrated here. It is also possible to pass additional arguments to the low-level DB-API driver created by SQLAlchemy via either a URL query string:

url='postgres://rick:foo@localhost/pg_db?arg1=foo&arg2=bar'
engine = create_engine(url)

or via the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Engines and Connectables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The SQLAlchemy-provided Engine class is responsible for managing the connection to the database. It does this by incorporating a database connection pool and a database-specific Dialect layer to translate the SQL expression language () into database-specific SQL.

To get started using an Engine, you use the create_engine() function:

# Create a connection to a SQLite in-memory database
engine = create_engine('sqlite://')

# Create a connection to a SQLite on-disk database "data.sqlite"
engine = create_engine('sqlite:///data.sqlite')

# Create a connection to a PostGreSQL database
engine = create_engine('postgres://rick:foo@localhost:5432/pg_db')

# Create a connection to a MySQL database
engine = create_engine('mysql://localhost/mysql_db')

# Create a connection to an Oracle database (via TNS)
engine = create_engine('oracle://rick:foo@oracle_tns')

# Create a connection to an Oracle database (without a TNS name)
engine =
... create_engine('oracle://rick:foo@localhost:1521/oracle_sid')

The first argument to create_engine() is the RFC-1738 style URL specifying the database. The general form of the url is: driver://username:password@host:port/database. Of course, the various database drivers interpret these URLs in slightly different ways, as illustrated here. It is also possible to pass additional arguments to the low-level DB-API driver created by SQLAlchemy via either a URL query string:

url='postgres://rick:foo@localhost/pg_db?arg1=foo&arg2=bar'
engine = create_engine(url)

or via the connect_args parameter to create_engine():

engine = create_engine('postgres://rick:foo@localhost/pg_db',
    connect_args=dict(arg1='foo', arg2='bar'))

If you wish complete control over the connection creation process, you can even pass a function (or other callable object) that returns a DB-API connection to create_engine() in the moreinfo="none">creator argument:

import psycopg
def connect_pg():
    return psycopg.connect(user='rick', host='localhost')
engine = create_engine('postgres://', creator=connect_pg)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

MetaData

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy provides the MetaData class, which collects objects that describe tables, indexes, and other schema-level objects. Before using any of the higher-level features of SQLAlchemy, such as the SQL query language and the ORM, the schema of the database must be described using metadata. In some cases, you can reflect the structure of schema items into the MetaData from the database. In this case, you need only specify the name of the entity, and its structure will be loaded from the database directly.

To create a new MetaData object, you simply call its constructor, possibly with information about how to connect to the database. If the constructor is called with no arguments, it is considered to be unbound; if it is called with either an Engine or a SQL connection URI, it is considered bound. Shortcuts are available to bound MetaData and to objects within a bound MetaData to facilitate the execution of statements against the bound engine. Most of the time you will probably use a bound MetaData object. However, it is sometimes useful to use an unbound MetaData if you need to connect to multiple database servers, where each server contains the same database schema.

The various ways to construct MetaData objects are illustrated in the following

# create an unbound MetaData
unbound_meta = MetaData()

# create an Engine and bind the MetaData to it
db1 = create_engine('sqlite://')
unbound_meta.bind = db

# Create an engine and then a bound MetaData
db2 = MetaData('sqlite:///test1.db')
bound_meta1 = MetaData(db2)

# Create a bound MetaData with an implicitly created engine
bound_meta2 = MetaData('sqlite:///test2.db')

Note that you are never required to bind the MetaData object; all operations that rely on a database connection can also be executed by passing the Engine explicitly as the keyword parameter bind. This is referred to as explicit execution. If a MetaData instance is bound, then the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 4: SQLAlchemy Type Engines

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduces the SQLAlchemy type system. It covers the built-in types provided by SQLAlchemy: database-independent types and database-specific types. It then tells you how to create your own custom types for use in mapping application data onto your database schema.

When defining the MetaData used by your application, it is necessary to supply the SQL data type used by each column of each table (unless the tables are defined with , in which case SQLAlchemy provides the data types for you). These SQL data types are actually instances of SQLAlchemy-provided classes known as TypeEngines. TypeEngine objects convert Python values to native database values and vice versa. For instance, String(40) is an instance of a TypeEngine that represents a VARCHAR(40). TypeEngines also supply SQL text for use when creating tables using or table.create().

SQLAlchemy provides three different ways of constructing types for use in your application. First, it provides a set of generic TypeEngines, which are fairly portable across different database engines. Second, it provides database server-specific TypeEngines, which can be used to exploit particular types supported by certain databases. Third, SQLAlchemy allows you to define application-specific custom TypeEngines if you wish to further customize object conversion to/from the database.

SQLAlchemy provides a fairly complete set of built-in TypeEngines for support of basic SQL column types. The SQLAlchemy-provided TypeEngines are broken into the generic types (those portable across multiple database engines) and the dialect-specific types, which work only on particular databases.

If you want to keep your application portable across database servers, it is a good idea to stick to the generic types and (possibly) application-specific custom types, as any code that relies on database dialect-specific TypeEngines will need to be modified if the database changes. In the SQLAlchemy tradition of not getting in your way, however, full support is provided for dialect-specific

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Type System Overview

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When defining the MetaData used by your application, it is necessary to supply the SQL data type used by each column of each table (unless the tables are defined with , in which case SQLAlchemy provides the data types for you). These SQL data types are actually instances of SQLAlchemy-provided classes known as TypeEngines. TypeEngine objects convert Python values to native database values and vice versa. For instance, String(40) is an instance of a TypeEngine that represents a VARCHAR(40). TypeEngines also supply SQL text for use when creating tables using or table.create().

SQLAlchemy provides three different ways of constructing types for use in your application. First, it provides a set of generic TypeEngines, which are fairly portable across different database engines. Second, it provides database server-specific TypeEngines, which can be used to exploit particular types supported by certain databases. Third, SQLAlchemy allows you to define application-specific custom TypeEngines if you wish to further customize object conversion to/from the database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Built-in Types

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy provides a fairly complete set of built-in TypeEngines for support of basic SQL column types. The SQLAlchemy-provided TypeEngines are broken into the generic types (those portable across multiple database engines) and the dialect-specific types, which work only on particular databases.

If you want to keep your application portable across database servers, it is a good idea to stick to the generic types and (possibly) application-specific custom types, as any code that relies on database dialect-specific TypeEngines will need to be modified if the database changes. In the SQLAlchemy tradition of not getting in your way, however, full support is provided for dialect-specific TypeEngines if you wish to exploit database server-specific types.

The generic TypeEngines provided by SQLAlchemy are found in the sqlalchemy.types package. These TypeEngines cover a fairly complete set of portable column types. The TypeEngines supported, their corresponding Python type, and their SQL representation, are listed in . Note that there are several TypeEngines defined in all caps (such as CLOB). These are derived from other TypeEngines and may or may not be further specialized to allow finer-grained specification of the underlying database type.

Table : Built-in generic TypeEngines

Class name	Python type		Arguments
String	string	TEXT or VARCHAR	length (default is unbounded)
Integer	int	INTEGER	none
SmallInteger	int	SMALLINT	none
Numeric	float, Decimal	NUMERIC

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Application-Specific Custom Types

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although SQLAlchemy provides a rich set of generic and database-specific types, it is sometimes helpful to be able to create application-specific custom types. For instance, you may wish to emulate enumerations in a database engine that does not support enumerations by restricting the values that can be stored in a column.

In SQLAlchemy, there are two ways to create an application-specific custom type. If you wish to implement a type that is similar to an existing TypeEngine, you would implement a TypeDecorator. If your implementation is more involved, you can directly subclass TypeEngine.

To implement a TypeDecorator, you must provide the base TypeEngine you are “implementing” as well as two functions, convert_bind_param( self, value, engine) is used to convert Python values to SQL values suitable for the DB-API driver, and convert_result_value( self, value, engine) is used to convert SQL values from the DB-API driver back into Python values. The implemented TypeEngine is specified in the impl attribute on the TypeDecorator.

For instance, if you wish to implement a type for validating that a particular Integer column contains only the values 0, 1, 2, and 3 (e.g., to implement an enumerated type in a database that does not support enumerated types), you would implement the following TypeDecorator:

from sqlalchemy import types

class MyCustomEnum(types.TypeDecorator):

    impl=types.Integer

    def __init__(self, enum_values, *l, **kw):
        types.TypeDecorator.__init__(self, *l, **kw)
        self._enum_values = enum_values

    def convert_bind_param(self, value, engine):
        result = self.impl.convert_bind_param(value, engine)
        if result not in self._enum_values:
            raise TypeError, (
"Value %s must be one of %s" % (result, self._enum_values))
        return result

    def convert_result_value(self, value, engine):
        'Do nothing here'
        return self.impl.convert_result_value(value, engine)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 5: Running Queries and Updates

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQLAlchemy provides a rich Pythonic interface for constructing SQL updates and queries, known as the SQL Expression Language. This language is based around the concept of an SQL statement, which represents some database-independent SQL syntax that may have one or more bind variables, and that can be executed on an SQL Engine or other Connectable. This chapter introduces the various kinds of data manipulation supported by SQLAlchemy (SQL INSERT, UPDATE, and DELETE) and performed on the query interface (SQL SELECT).

Insert, Update, and Delete constructs are created in SQLAlchemy via the Table methods insert, update, and delete, or via the insert, update, and delete functions. The functionality of these data manipulation language (DML) constructs is equivalent, regardless of whether they are constructed via methods or functions; the distinction is a question of style more than substance.

Although each DML construct has its own particulars regarding construction, they all end up generating a Statement. We can inspect the SQL text corresponding to the statement by printing it out:

>>> metadata=MetaData()
>>> 
>>> simple_table = Table(
...     'simple', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('col1', Unicode(20)))
>>> 
>>> stmt = simple_table.insert()
>>> print stmt
INSERT INTO simple (id, col1) VALUES (:id, :col1)

Note in the previous example that SQLAlchemy has created bind parameters for each of the columns in the table we created in the insert statement. We can examine the bind parameters in a statement by compiling the statement and looking at its params

>>> compiled_stmt = stmt.compile()
>>> print compiled_stmt.params
ClauseParameters:{'id': None, 'col1': None}

To execute the statement, we can directly execute it on an Engine, or we can bind the MetaData used to construct the statement and use the MetaData’s engine:

>>> engine = create_engine('sqlite://')
>>> simple_table.create(bind=engine)
>>> engine.execute(stmt, col1="Foo")
<sqlalchemy.engine.base.ResultProxy object at 0x2b3210b00f10>
>>> metadata.bind = engine
>>> stmt.execute(col1="Bar")
<sqlalchemy.engine.base.ResultProxy object at 0x2b3210b020d0>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Inserts, Updates, and Deletes

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Insert, Update, and Delete constructs are created in SQLAlchemy via the Table methods insert, update, and delete, or via the insert, update, and delete functions. The functionality of these data manipulation language (DML) constructs is equivalent, regardless of whether they are constructed via methods or functions; the distinction is a question of style more than substance.

Although each DML construct has its own particulars regarding construction, they all end up generating a Statement. We can inspect the SQL text corresponding to the statement by printing it out:

>>> metadata=MetaData()
>>> 
>>> simple_table = Table(
...     'simple', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('col1', Unicode(20)))
>>> 
>>> stmt = simple_table.insert()
>>> print stmt
INSERT INTO simple (id, col1) VALUES (:id, :col1)

Note in the previous example that SQLAlchemy has created bind parameters for each of the columns in the table we created in the insert statement. We can examine the bind parameters in a statement by compiling the statement and looking at its params

>>> compiled_stmt = stmt.compile()
>>> print compiled_stmt.params
ClauseParameters:{'id': None, 'col1': None}

To execute the statement, we can directly execute it on an Engine, or we can bind the MetaData used to construct the statement and use the MetaData’s engine:

>>> engine = create_engine('sqlite://')
>>> simple_table.create(bind=engine)
>>> engine.execute(stmt, col1="Foo")
<sqlalchemy.engine.base.ResultProxy object at 0x2b3210b00f10>
>>> metadata.bind = engine
>>> stmt.execute(col1="Bar")
<sqlalchemy.engine.base.ResultProxy object at 0x2b3210b020d0>

Note that the bind parameter values are supplied to the execute() method as keyword parameters. These parameters can either be supplied either directly to the execute() method or in the statement construction phase:

>>> stmt = simple_table.insert(values=dict(col1="Initial value"))
>>> print stmt
INSERT INTO simple (col1) VALUES (?)
>>> compiled_stmt = stmt.compile()
>>> print compiled_stmt.params
ClauseParameters:{'col1': 'Initial value'}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Queries

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The real power of the SQL expression language is in its query interface. This includes the actual queries (SQL “SELECT” statements) as well as the syntax for specifying “WHERE” clauses (which may be used in UPDATEs and DELETEs, as well).

The goal of the SQL expression language, like the goal of SQLAlchemy in general, is to provide functionality that doesn’t “get in your way” when you need to be more specific about the SQL you need. In that vein, you can always use the Text construct (used previously in the UPDATE and DELETE examples) to specify the exact SQL text you would like to use. For most operations, however, the SQL expression language makes for a succinct, secure, and less error-prone way of expressing your queries.

SQLAlchemy makes simple SQL queries easy to express, while also enabling the construction of quite complex queries in a straightforward manner. This section describes the basic building blocks of query construction in SQLAlchemy.

The select⁠(⁠ ⁠) function versus the select⁠(⁠ ⁠) method

Like the DML statements INSERT, UPDATE, and DELETE, SELECT statements can be generated using either a function or a Table method. Unlike the DML statements, however, there is a minor difference in functionality between the select() function and the Table.select() method. The select() function requires you to specify which columns you want in your result set. So, to select one column from the product_table shown previously, you could use the select() function:

>>> stmt = select([product_table.c.sku])
>>> for row in stmt.execute():
...     print row
... 
(u'123',)
(u'456',)
(u'789',)

To select all columns from the product_table, you would use the Table.select()

>>> stmt = product_table.select()
>>> for row in stmt.execute():
...     print row
... 
(u'123', Decimal("12.34"))
(u'456', Decimal("22.12"))
(u'789', Decimal("41.44"))

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 6: Building an Object Mapper

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Atop the SQL expression language, SQLAlchemy provides an object-relational mapper (ORM). The purpose of an ORM is to provide a convenient way to store your application data objects in a relational database. Generally, an ORM will provide a way to define the method of storing your object in the database. This chapter focuses on the SQLAlchemy methods that do this.

ORMs provide methods of updating the database by using your application objects. For instance, to update a column in a mapped table in SQLAlchemy, you merely have to update the object, and SQLAlchemy will take care of making sure that the change is reflected in the database. ORMs also allow you to construct application objects based on database queries. Chapter 7 will focus on how to use SQLAlchemy’s ORM to update and query objects in the database.

There are two major patterns used in the ORM you should become familiar with in order to understand how to best use the ORM. These are the data mapper pattern and the unit of work pattern.

The data mapper pattern

In the data mapper pattern (shown in ), database tables, views, and other “selectable” objects are mapped onto “plain old Python objects” (POPOs) by “mapper” objects. This is different from the “active record” pattern (shown in ), where the objects themselves are responsible for mapping themselves to database views. The data mapper pattern can, of course, be used to emulate the active record pattern by merging the mapper with the application objects.

Figure : Data mapper pattern

Figure : Active record pattern

One benefit of using the data mapper pattern as implemented in SQLAlchemy is that it allows the database design to be decoupled from the object hierarchy. In , this decoupling can be nearly complete: you can define your classes in one module and your tables in another with no references from one to the other. The mapping can then be performed by a third module, which imports the other two modules and instantiates the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction to ORMs

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ORMs provide methods of updating the database by using your application objects. For instance, to update a column in a mapped table in SQLAlchemy, you merely have to update the object, and SQLAlchemy will take care of making sure that the change is reflected in the database. ORMs also allow you to construct application objects based on database queries. Chapter 7 will focus on how to use SQLAlchemy’s ORM to update and query objects in the database.

There are two major patterns used in the ORM you should become familiar with in order to understand how to best use the ORM. These are the data mapper pattern and the unit of work pattern.

The data mapper pattern

In the data mapper pattern (shown in ), database tables, views, and other “selectable” objects are mapped onto “plain old Python objects” (POPOs) by “mapper” objects. This is different from the “active record” pattern (shown in ), where the objects themselves are responsible for mapping themselves to database views. The data mapper pattern can, of course, be used to emulate the active record pattern by merging the mapper with the application objects.

Figure : Data mapper pattern

Figure : Active record pattern

One benefit of using the data mapper pattern as implemented in SQLAlchemy is that it allows the database design to be decoupled from the object hierarchy. In , this decoupling can be nearly complete: you can define your classes in one module and your tables in another with no references from one to the other. The mapping can then be performed by a third module, which imports the other two modules and instantiates the Mapper objects, which do the work of mapping the selectables to your objects.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Declaring Object Mappers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In order to use the SQLAlchemy ORM, we need three things: a database schema defined on a MetaData object, an object model (no special preparation of the object model is required for use by SQLAlchemy), and a mapper configuration. In this section, we will use the following schema, designed to maintain information about a retail product catalog:

level_table = Table(
    'level', metadata,
    Column('id', Integer, primary_key=True),
    Column('parent_id', None, ForeignKey('level.id')),
    Column('name', String(20)))
category_table = Table(
    'category', metadata,
    Column('id', Integer, primary_key=True),
    Column('level_id', None, ForeignKey('level.id')),
    Column('parent_id', None, ForeignKey('category.id')),
    Column('name', String(20)))
product_table = Table(
    'product', metadata,
    Column('sku', String(20), primary_key=True), 
    Column('msrp', Numeric))
product_summary_table = Table(
    'product_summary', metadata,
    Column('sku', None, ForeignKey('product.sku'), primary_key=True),
    Column('name', Unicode(255)),
    Column('description', Unicode))
product_category_table = Table(
    'product_category', metadata,
    Column('product_id', None, ForeignKey('product.sku'), primary_key=True),
    Column('category_id', None, ForeignKey('category.id'), primary_key=True))
region_table = Table(
    'region', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(255)))
store_table = Table(
    'store', metadata,
    Column('id', Integer, primary_key=True),
    Column('region_id', None, ForeignKey('region.id')),
    Column('name', Unicode(255)))
product_price_table = Table(
    'product_price', metadata,
    Column('sku', None, ForeignKey('product.sku'), primary_key=True),
    Column('store_id', None, ForeignKey('store.id'), primary_key=True),
    Column('price', Numeric, default=0))

The application object model in the following listing is extremely basic. In a real the classes would probably have additional methods defined for performing domain-specific operations:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Declaring Relationships Between Mappers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although the features that SQLAlchemy provides for mapping tables and other selectables to classes are powerful in their own right, SQLAlchemy also allows you to model relationships between tables as simple Python lists and properties using the relation() function in the properties parameter of the mapper() function.

The three main relationships modeled by SQLAlchemy are 1:N, M:N, and 1:1 (which is actually a special case of 1:N). In a 1:N relationship, one table (the “N” side) generally has a foreign key to another table (the “1” side). In M:N, two tables (the “primary” tables) are related via a scondary, “join” table that has foreign keys into both primary tables. A 1:1 relationship is simply a 1:N relationship where there is only one “N”-side row with a foreign key to any particular “1”-side row.

1:N relations

To model each type of relationship, SQLAlchemy uses the relation() function in the properties dict of the mapper. In many cases, SQLAlchemy is able to infer the proper join condition for 1:N relations. For instance, since the stores in our data model are members of regions (a 1:N relationship region:store), we can model this on our Region class as follows:

>>> mapper(Store, store_table)
<sqlalchemy.orm.mapper.Mapper object at 0x2b794eb2f610>
>>> mapper(Region, region_table, properties=dict(
...     stores=relation(Store)))


<sqlalchemy.orm.mapper.Mapper object at 0x2b794eb3af90>
>>> rgn = session.query(Region).get(1)
2007-10-13 12:59:47,876 INFO sqlalchemy.engine.base.Engine.0x..90
... SELECT region.id AS region_id, region.name AS region_name 
FROM region 
WHERE region.id = ? ORDER BY region.oid 
 LIMIT 1 OFFSET 0
2007-10-13 12:59:47,877 INFO sqlalchemy.engine.base.Engine.0x..90
... [1]
>>> s0 = Store(name='3rd and Juniper')
>>> rgn.stores.append(s0)
2007-10-13 13:00:06,339 INFO sqlalchemy.engine.base.Engine.0x..90
... SELECT store.id AS store_id, store.region_id AS store_region_id,
... store.name AS store_name 
FROM store 
WHERE ? = store.region_id ORDER BY store.oid
2007-10-13 13:00:06,339 INFO sqlalchemy.engine.base.Engine.0x..90
... [1]
>>> session.flush()
2007-10-13 13:00:14,344 INFO sqlalchemy.engine.base.Engine.0x..90
... INSERT INTO store (region_id, name) VALUES (?, ?)
2007-10-13 13:00:14,345 INFO sqlalchemy.engine.base.Engine.0x..90
... [1, '3rd and Juniper']

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Extending Mappers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although the mapper function—combined with the various property creation functions—is extremely powerful, it is sometimes useful to extend the functionality of a mapper. To that end, SQLAlchemy provides the MapperExtension class, which can be extended to provide mapper behavior modification via a series of hooks. Multiple MapperExtensions can be registered on a mapper, allowing a chain of responsibility for modifying the mapper behavior. MapperExtensions are registered either in the mapper() function call via the extension parameter, or by using an extension() argument to the option() method in queries (covered in ).

Each hook should return either or . (Any other value will be interpreted by SQLAlchemy as .) If is returned, processing continues, either to the next MapperExtension or by the mapper itself. If is returned, then the mapper will not call any other extensions in the chain.

Some of the useful hooks in MapperExtension are described in the following list:

before_delete(self, mapper, connection, instance)

Called with an object instance before that instance is deleted.

before_insert(self, mapper, connection, instance)

Called with an object instance before that instance is inserted.

before_update(self, mapper, connection, instance)

Called with an object instance before that instance is updated.

after_delete(self, mapper, connection, instance)

Called with an object instance after that instance is deleted.

after_insert(self, mapper, connection, instance)

Called with an object instance after that instance is inserted.

after_update(self, mapper, connection, instance)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ORM Partitioning Strategies

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sometimes you want to use the ORM to map objects that may exist in multiple databases. SQLAlchemy provides support for “vertical” partitioning (placing different kinds of