Inhaltsverzeichnis von Essential SQLAlchemy

Inhaltsvorschau

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')

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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')

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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));

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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>

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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'}

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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"))

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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:

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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']

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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 objects or different tables in different databases) as well as “horizontal” partitioning, also called “sharding” (partitioning the rows of a single table across multiple databases).

In vertical partitioning, different mapped classes are retrieved from different database servers. In the following example, we create product_table in one in-memory sqlite database and product_summary_table in another:

engine1 = create_engine('sqlite://')

engine2 = create_engine('sqlite://')



metadata = MetaData()



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_table.create(bind=engine1)

product_summary_table.create(bind=engine2)



stmt = product_table.insert()

engine1.execute(

    stmt,

    [dict(sku="123", msrp=12.34),

     dict(sku="456", msrp=22.12),

     dict(sku="789", msrp=41.44)])

stmt = product_summary_table.insert()

engine2.execute(

    stmt,

    [dict(sku="123", name="Shoes", description="Some Shoes"),

     dict(sku="456", name="Pants", description="Some Pants"),

     dict(sku="789", name="Shirts", description="Some Shirts")])

Now, we can create and map the Product and ProductSummary classes:

class Product(object):

    def __init__(self, sku, msrp, summary=None):

        self.sku = sku

        self.msrp = msrp

        self.summary = summary

    def __repr__(self):

        return '<Product %s>' % self.sku



class ProductSummary(object):

    def __init__(self, name, description):

        self.name = name

        self.description = description

    def __repr__(self):

        return '<ProductSummary %s>' % self.name



clear_mappers()

mapper(ProductSummary, product_summary_table, properties=dict(

    product=relation(Product, 

                     backref=backref('summary', uselist=False))))

mapper(Product, product_table)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

This chapter introduces the SQLAlchemy Session object. You will learn how to use the Session to perform queries and updates of mapped classes, as well as how to customize the Session class and create a “contextual” session that simplifies session management.

SQLAlchemy manages all querying and updating of objects in the ORM with Session objects. The Session is responsible for implementing the unit of work pattern of synchronization between in-memory objects and database tables. Sessions also provide a rich interface for querying the database based on object attributes rather than the SQL database structure.

The first step in creating a session is to obtain a Session object from SQLAlchemy. One way to do this is to directly instantiate the sqlalchemy.orm.session.Session class. However, this constructor for the SQLAlchemy-provided Session has a number of keyword arguments, making instantiating Sessions in this manner verbose and tedious. In order to alleviate this burden, SQLAlchemy provides the sessionmaker() function, which returns a subclass of orm.session.Session with default arguments set for its constructor.

Once you have this customized Session class, you can instantiate it as many times as necessary in your application without needing to retype the keyword arguments (which in many applications will not change between Session instantiations). If you wish to override the defaults supplied to sessionmaker, you can do so at Session instantiation time. You can also modify the default arguments bound to a particular Session subclass by calling the class method Session.configure():

# Create a Session class with the default

#    options

Session = sessionmaker(bind=engine)



# Create an unbound Session class

Session = sessionmaker()



# Bind the Session class once the engine

#    is available

Session.configure(bind=engine)

The sessionmaker() and the associated

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

SQLAlchemy manages all querying and updating of objects in the ORM with Session objects. The Session is responsible for implementing the unit of work pattern of synchronization between in-memory objects and database tables. Sessions also provide a rich interface for querying the database based on object attributes rather than the SQL database structure.

The first step in creating a session is to obtain a Session object from SQLAlchemy. One way to do this is to directly instantiate the sqlalchemy.orm.session.Session class. However, this constructor for the SQLAlchemy-provided Session has a number of keyword arguments, making instantiating Sessions in this manner verbose and tedious. In order to alleviate this burden, SQLAlchemy provides the sessionmaker() function, which returns a subclass of orm.session.Session with default arguments set for its constructor.

Once you have this customized Session class, you can instantiate it as many times as necessary in your application without needing to retype the keyword arguments (which in many applications will not change between Session instantiations). If you wish to override the defaults supplied to sessionmaker, you can do so at Session instantiation time. You can also modify the default arguments bound to a particular Session subclass by calling the class method Session.configure():

# Create a Session class with the default

#    options

Session = sessionmaker(bind=engine)



# Create an unbound Session class

Session = sessionmaker()



# Bind the Session class once the engine

#    is available

Session.configure(bind=engine)

The sessionmaker() and the associated Session subclass’s configure class method and constructor take the following keyword arguments:

bind=None: The database Engine or Connection to which to bind the session.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

Saving and updating objects via SQLAlchemy’s ORM interface isn’t very useful without the ability to retrieve objects from the database. This is where the Session’s query() method comes in handy. To retrieve an iterator over all the objects of a particular type in the database, simply specify either the class you wish to query or its mapper:

>>> query = session.query(Product)

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp 

FROM product ORDER BY product.oid

>>> for obj in query:

...     print obj

... 

2007-11-16 16:19:42,669 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp 

FROM product ORDER BY product.oid

2007-11-16 16:19:42,669 INFO sqlalchemy.engine.base.Engine.0x..90 []

<Product 123>

<Product 456>

<Product 222>

Notice here that the query is generative, as were the SQL-layer queries mentioned in . This means that SQLAlchemy will not actually execute the query on the database until the results are iterated over. You can also retrieve all the results as a list by calling the all() method on the query object:

>>> query.all()

2007-11-16 16:21:35,349 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp 

FROM product ORDER BY product.oid

2007-11-16 16:21:35,349 INFO sqlalchemy.engine.base.Engine.0x..90 []

[<Product 123>, <Product 456>, <Product 222>]

Because retrieving the entire collection of mapped objects isn’t very useful, SQLAlchemy provides various methods to modify the query object. Note that all of these methods actually generate and return a new query object rather than modifying the existing query object. The most useful of these methods are filter() and filter_by(). These methods work, as their names imply, by restricting the set of objects returned from the query. For instance, to retrieve all the products with an MSRP between $10 and $20, we could use filter() as follows:

>>> session.bind.echo = False

>>> query = query.filter(Product.msrp > 10.00)

>>> query = query.filter(Product.msrp < 20.00)

>>> for product in query:

...     print product.sku, product.msrp

... 

123 11.22

222 15.95

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

Although the SQLAlchemy ORM is extremely flexible and powerful, it can be somewhat repetitive in some cases. One of these cases is constructing the Session object. Fortunately, SQLAlchemy provides the ability to manage sessions for you in such a way that a Session object can be shared among various parts of your application without explicitly passing it around as a parameter. This is useful in web frameworks in particular, where you generally want all the code servicing a given web request to use the same Session object. SQLAlchemy achieves implicit Session object sharing via “contextual” sessions.

The idea of a contextual session is that there is one session that is available in a given “context,” where the default context is the thread. When you need a session, rather than constructing one yourself, you simply ask SQLAlchemy for the session that is appropriate to the current context. You can generate a contextual Session object by using the scoped_session() function:

>>> Session = scoped_session(sessionmaker(

...     bind=engine, autoflush=True, transactional=True))

>>> 

>>> session = Session()

>>> session2 = Session()

>>> session is session2

As mentioned earlier, the default context is the current thread. To override this and supply a different context, simply pass a scopefunc parameter to the scoped_session() function. scopefunc should be a callable that returns a key that uniquely identifies the context. By default, the scopefunc is the get_ident() function from the thread module.

The contextual Session class also supplies class methods for all the Session instance methods. These class methods simply proxy to the contextual Session object. This means that we can use scoped_session() to declare the contextual Session class globally and use it anywhere we would normally need a Session object, without explicitly constructing the Session object. So, if we want to save a new Product to the contextual Session

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

In this chapter, you will learn the different methods of mapping object-oriented inheritance to relational database tables. You will learn how to use different methods of inheritance mapping with SQLAlchemy, as well as how to use inheritance in the presence of mapped relations between classes.

No object-relational mapper would be complete without some method of mapping object-oriented inheritance hierarchies to SQL tables, and so SQLAlchemy provides rich support for modeling inheritance. Inheritance is typically modeled in SQL in one of three ways: single table inheritance, concrete table inheritance, or joined table .

For the purposes of illustrating SQLAlchemy’s support for the various types of inheritance modeling, we will use a simple inheritance hierarchy that models products, including clothing products and accessories. This hierarchy is illustrated in and is implemented by the following Python code:

class Product(object):

    def __init__(self, sku, msrp):

        self.sku = sku

        self.msrp = msrp

    def __repr__(self):

        return '<%s %s>' % (

            self.__class__.__name__, self.sku)



class Clothing(Product):

    def __init__(self, sku, msrp, clothing_info):

        Product.__init__(self, sku, msrp)

        self.clothing_info = clothing_info



class Accessory(Product):

    def __init__(self, sku, msrp, accessory_info):

        Product.__init__(self, sku, msrp)

        self.accessory_info = accessory_info

Figure : Sample inheritance hierarchy

In single table inheritance, a single table is used to represent all the different types in the class hierarchy, as shown in .

Figure : Single table inheritance mapping (unmapped columns masked)

In our preceding example, this table might be defined as follows:

product_table = Table(

    'product', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('clothing_info', String),

    Column('accessory_info', String),

    Column('product_type', String(1), nullable=False))

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

No object-relational mapper would be complete without some method of mapping object-oriented inheritance hierarchies to SQL tables, and so SQLAlchemy provides rich support for modeling inheritance. Inheritance is typically modeled in SQL in one of three ways: single table inheritance, concrete table inheritance, or joined table .

For the purposes of illustrating SQLAlchemy’s support for the various types of inheritance modeling, we will use a simple inheritance hierarchy that models products, including clothing products and accessories. This hierarchy is illustrated in and is implemented by the following Python code:

class Product(object):

    def __init__(self, sku, msrp):

        self.sku = sku

        self.msrp = msrp

    def __repr__(self):

        return '<%s %s>' % (

            self.__class__.__name__, self.sku)



class Clothing(Product):

    def __init__(self, sku, msrp, clothing_info):

        Product.__init__(self, sku, msrp)

        self.clothing_info = clothing_info



class Accessory(Product):

    def __init__(self, sku, msrp, accessory_info):

        Product.__init__(self, sku, msrp)

        self.accessory_info = accessory_info

Figure : Sample inheritance hierarchy

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

In single table inheritance, a single table is used to represent all the different types in the class hierarchy, as shown in .

Figure : Single table inheritance mapping (unmapped columns masked)

In our preceding example, this table might be defined as follows:

product_table = Table(

    'product', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('clothing_info', String),

    Column('accessory_info', String),

    Column('product_type', String(1), nullable=False))

Notice that we have constructed a table that contains columns for all of the attributes across the entire hierarchy we wish to model. This means that we incur some overhead for all of the classes in the hierarchy in each row. Although this doesn’t cause too many problems with the simple hierarchy we are using in this example, the space overhead can become significant with larger and more attribute-rich hierarchies.

Also note that we have introduced a new column, the 'product_type' column. This column holds the “polymorphic identity” of each row, so named because it allows SQLAlchemy to return the appropriate class from a query on the parent object. The polymorphic identity is used by SQLAlchemy to determine what type of object is contained in the row. SQLAlchemy supports using any data type desired to hold this information; here we use a single character. 'P' will represent a Product (the parent class), 'C' will represent a Clothing product, and 'A' will represent an Accessory

To map this table onto our inheritance hierarchy, we will use some new keyword arguments to the mapper() function, namely polymorphic_on, inherits, and polymorphic_identity:

mapper(

    Product, product_table,

    polymorphic_on=product_table.c.product_type,

    polymorphic_identity='P')



mapper(Clothing, inherits=Product,

       polymorphic_identity='C')



mapper(Accessory, inherits=Product,

       polymorphic_identity='A')

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

In concrete table inheritance mapping, we use a separate table for each class in the inheritance hierarchy, with each table containing all the columns necessary to represent the object in its entirety, as shown in .

Figure : Concrete table inheritance mapping

So, for the product hierarchy in our example, we would define the following tables in this way:

product_table = Table(

    'product', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric))



clothing_table = Table(

    'clothing', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('clothing_info', String))



accessory_table = Table(

    'accessory', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('accessory_info', String))

Note that in concrete table inheritance, each table contains exactly the amount of data that is required to implement its class; there is no wasted space, unlike single table inheritance. Also note that there is no longer a need for the “polymorphic identity” column, as SQLAlchemy knows that Clothing objects are created from the clothing_table, Accessory objects from the accessory_table, etc.

The mapper configuration is likewise straightforward:

mapper(Product, product_table)

mapper(Clothing, clothing_table)

mapper(Accessory, accessory_table)

In fact, as far as SQLAlchemy is concerned, we aren’t modeling inheritance at all! We’ve just persisted three classes which happen to have an inheritance relationship that is completely ignored by SQLAlchemy. Unfortunately, in doing so, we have lost the ability to query polymorphically. For instance, we may wish to retrieve the Product with sku '222'. Without some extra work, we’d have to query each of the classes in the inheritance hierarchy. Luckily, SQLAlchemy provides support for polymorphic loading if we do a little extra work in the mapper configuration.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

Joined table inheritance is perhaps the closest to directly mapping the inheritance to the database. In joined table inheritance mapping, as in concrete table inheritance mapping, a distinct table is used to map each class. Unlike concrete inheritance mapping, however, each table contains only the columns the attributes added, allowing the row in the “parent” table to take care of inherited attributes, as shown in .

Figure : Joined table inheritance mapping

The total set of attributes required to represent an instance are then retrieved by joining along the inheritance hierarchy. In our product database, this would have the following declaration:

product_table = Table(

    'product', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('product_type', String(1), nullable=False))



clothing_table = Table(

    'clothing', metadata,

    Column('sku', None, ForeignKey('product.sku'),

           primary_key=True),

    Column('clothing_info', String))



accessory_table = Table(

    'accessory', metadata,

    Column('sku', None, ForeignKey('product.sku'),

           primary_key=True),

    Column('accessory_info', String))

Notice that we have reintroduced the 'product_type' polymorphic identity column from single table inheritance mapping. In joined table inheritance, this column is only required on the “root” table of the inheritance hierarchy, again to let SQLAlchemy know what type of object to create in a polymorphic load.

The mappers we build are almost identical to the ones we used in the single table inheritance mapping, except that each mapper references a distinct table, whereas all the mappers shared a table in the single-table inheritance case:

mapper(

    Product, product_table, 

    polymorphic_on=product_table.c.product_type,

    polymorphic_identity='P')



mapper(Clothing, clothing_table, inherits=Product,

       polymorphic_identity='C')



mapper(Accessory, accessory_table, inherits=Product,

       polymorphic_identity='A')

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

In the cases of single table and joined table inheritance mapping, relations “just work” in SQLAlchemy. In particular, it is possible for a mapped class to declare a relation to a class that is part of an inheritance hierarchy (a “polymorphic class”), and have that relation comprise instances of various child classes. This setup is shown in the following listing, where inventory information is added to our schema:

store_table = Table(

    'store', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String))



inventory_table = Table(

    'inventory', metadata,

    Column('store_id', None, ForeignKey('store.id')),

    Column('product_id', None, ForeignKey('product.sku')),

    Column('quantity', Integer, default=0)



class Store(object): pass



class Inventory(object): pass



mapper(Store, store_table, properties=dict(

    inventory=relation(Inventory, backref='store')))



mapper(Inventory, inventory_table, properties=dict(

    product=relation(Product, backref='inventory')))

It is also possible to declare relations on a polymorphic class at any level of the inheritance hierarchy, and those relations will be inherited by the child classes. In the previous example, for instance, the Clothing and Accessory classes inherit the backref to their Inventory records.

In concrete table inheritance, mapping relations to a “parent class” is more difficult because there is no unique table to join to. For instance, it is possible to implement one-to-many and one-to-one joins where the polymorphic class has a foreign key into another table. As an example, if we introduced a “vendor” table identifying the manufacturer of all products, we could relate it to the Product hierarchy as follows:

vendor_table = Table(

    'vendor', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String))



product_table = Table(

    'product', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('vendor_id', None, ForeignKey('vendor.id'))



clothing_table = Table(

    'clothing', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('vendor_id', None, ForeignKey('vendor.id'),

    Column('clothing_info', String))



accessory_table = Table(

    'accessory', metadata,

    Column('sku', String(20), primary_key=True),

    Column('msrp', Numeric),

    Column('vendor_id', None, ForeignKey('vendor.id'),

    Column('accessory_info', String))



punion = polymorphic_union(

    dict(P=product_table,

         C=clothing_table,

         A=accessory_table),

    'type_')



mapper(

    Product, product_table, select_table=punion,

    polymorphic_on=punion.c.type_,

    polymorphic_identity='P')



mapper(Clothing, clothing_table, inherits=Product,

       polymorphic_identity='C',

       concrete=True)



mapper(Accessory, accessory_table, inherits=Product,

       polymorphic_identity='A',

       concrete=True)



class Vendor(object): pass



mapper(Vendor, vendor_table, properties=dict(

    products=relation(Product)))

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

This chapter describes Elixir, a module developed to automate some of the more common tasks in SQLAlchemy by providing a declarative layer atop “base” or “raw” SQLAlchemy. This chapter also describes the various extensions to Elixir that provide features such as encryption and versioning.

The Elixir module was developed as a declarative layer on top of SQLAlchemy, implementing the “active record” pattern described in . Elixir goes out of its way to make all of the power of SQLAlchemy available, while providing sensible default behavior with significantly less code than “raw” SQLAlchemy. This chapter describes versions 0.4 and 0.5 of Elixir, corresponding to the 0.4 version of SQLAlchemy. Differences between versions 0.4 and 0.5 are discussed in the upcoming sidebar, .”

So, what exactly does Elixir do? Well, consider a simple product database. In , we might set up the products, stores, and prices with the following code:

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', None, ForeignKey('product.sku'), primary_key=True),

Column('store_id', None, ForeignKey('store.id'), primary_key=True),

    Column('price', Numeric, default=0))



class Product(object):

    def __init__(self, sku, msrp):

        self.sku = sku

        self.msrp = msrp

        self.prices = []

    def __repr__(self):

        return '<Product %s>' % self.sku



class Store(object):

    def __init__(self, name):

        self.name = name

    def __repr__(self):

        return '<Store %s>' % self.name



class Price(object):

    def __init__(self, product, store, price):

        self.product = product

        self.store = store

        self.price = price

    def __repr__(self):

        return '<Price %s at %s for $%.2f>' % (

            self.product.sku, self.store.name, self.price)



mapper(Product, product_table, properties=dict(

    prices=relation(Price, backref='product')))

mapper(Store, store_table, properties=dict(

    prices=relation(Price, backref='store')))

mapper(Price, product_price_table)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

The Elixir module was developed as a declarative layer on top of SQLAlchemy, implementing the “active record” pattern described in . Elixir goes out of its way to make all of the power of SQLAlchemy available, while providing sensible default behavior with significantly less code than “raw” SQLAlchemy. This chapter describes versions 0.4 and 0.5 of Elixir, corresponding to the 0.4 version of SQLAlchemy. Differences between versions 0.4 and 0.5 are discussed in the upcoming sidebar, .”

So, what exactly does Elixir do? Well, consider a simple product database. In , we might set up the products, stores, and prices with the following code:

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', None, ForeignKey('product.sku'), primary_key=True),

Column('store_id', None, ForeignKey('store.id'), primary_key=True),

    Column('price', Numeric, default=0))



class Product(object):

    def __init__(self, sku, msrp):

        self.sku = sku

        self.msrp = msrp

        self.prices = []

    def __repr__(self):

        return '<Product %s>' % self.sku



class Store(object):

    def __init__(self, name):

        self.name = name

    def __repr__(self):

        return '<Store %s>' % self.name



class Price(object):

    def __init__(self, product, store, price):

        self.product = product

        self.store = store

        self.price = price

    def __repr__(self):

        return '<Price %s at %s for $%.2f>' % (

            self.product.sku, self.store.name, self.price)



mapper(Product, product_table, properties=dict(

    prices=relation(Price, backref='product')))

mapper(Store, store_table, properties=dict(

    prices=relation(Price, backref='store')))

mapper(Price, product_price_table)

In Elixir, the corresponding setup is much simpler:

class Product(Entity):

    sku=Field(String(20), primary_key=True)

    msrp=Field(Numeric)

    prices=OneToMany('Price')

    

    def __repr__(self):

        return '<Product %s>' % self.sku



class Store(Entity):

    name=Field(Unicode(255))

    prices=OneToMany('Price')

    

    def __repr__(self):

        return '<Store %s>' % self.name



class Price(Entity):

    price=Field(Numeric, default=0)

    product=ManyToOne('Product')

    store=ManyToOne('Store')

    

    def __repr__(self):

        return '<Price %s at %s for $%.2f>' % (

            self.product.sku, self.store.name, self.price)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

Elixir, like SQLAlchemy, is best installed using SetupTools and easy_install. Assuming you have already installed SetupTools and SQLAlchemy as described in , you can install Elixir on Unix-like systems—including Linux, BSD, and OS X—as follows:

$ sudo easy_install -UZ Elixir

On Windows, the command is similar:

c:\>easy_install -UZ Elixir

To verify that Elixir is installed properly, open up an interactive Python interpreter, import the module, and verify its version:

>>> import elixir

>>> elixir.__version__

'0.4.0'

And that’s all there is to it. Elixir is installed!

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

Elixir has two syntaxes for defining your classes: an attribute-based syntax (shown previously) and a “domain specific language” (DSL) syntax. Both have similar power; which one you use is mostly a matter of personal style. The DSL-based syntax may be phased out in the future, as it is no longer the “default” syntax, but it is not currently deprecated, so it is covered in this chapter. If we were to define the product database using the DSL syntax, for instance, we would write the following (with the methods for each class omitted for clarity):

from elixir import *



metadata.bind = 'sqlite://'



class Product(Entity):

    has_field('sku', String(20), primary_key=True)

    has_field('msrp', Numeric)

    has_many('prices', of_kind='Price')



class Store(Entity):

    has_field('name', Unicode(255))

    has_many('prices', of_kind='Price')



class Price(Entity):

    has_field('price', Numeric, default=0)

    belongs_to('product', of_kind='Product')

    belongs_to('store', of_kind='Store')

There is a rough correspondence between the functionality of the attribute-based syntax for defining entities and the DSL syntax. The attribute-based classes are listed in along with their corresponding DSL function. Note that the mapping from attribute-based syntax to DSL syntax is not perfect; consult the rest of this chapter for the specific differences.

Table : Correspondence between attribute-based syntax and DSL syntax
Attribute class	DSL function
`Field`	`has_field`
`ColumnProperty`, `GenericProperty`	`has_property`
`ManyToOne`	`belongs_to`
`OneToMany`	`has_many`
`OneToOne`	`has_one`
`ManyToMany`	`has_and_belongs_to_many`

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

In addition to its base functionality, Elixir provides a number of extensions that allow for more advanced uses.

In many database schemas, there may be one table that relates to many others via a many-to-many or a many-to-one join. The elixir.ext.associable extension provides a convenient way to specify this pattern and to generate the appropriate association . This is accomplished by the associable() function, which returns a DSL statement that can be used in the definition of the related entities.

For instance, suppose we have a schema that represents brands and retailers, each of which may have multiple addresses stored in the database. This can be accomplished as follows:

class Address(Entity):

    has_field('street', Unicode(255))

    has_field('city', Unicode(255))

    has_field('postal_code', Unicode(10))



# Create the DSL statement.

is_addressable = associable(Address, 'addresses')



class Brand(Entity):

    has_field('name', Unicode(255)),

    has_field('description', Unicode)

    is_addressable()



class Retailer(Entity):

    has_field('name', Unicode(255)),

    has_field('description', Unicode)

    is_addressable()

To implement this pattern, the associable extension actually implements something like joined table inheritance mapping, where the entity being associated joins to an intermediate association table, which is in turn joined to a “virtual base class” for each associable class. The tables created for the previous schema show this more clearly in .

Figure : Associable table relationships

The associable() function takes one required argument, the Entity to be associated, as well as some optional arguments:

plural_name=None: The default name to be used for the property generated by the returned DSL statement. By default, this is the lowercased name of the associable

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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.

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 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', None, ForeignKey('product.sku'), primary_key=True),

Column('store_id', None, ForeignKey('store.id'), primary_key=True),

    Column('price', Numeric, default=0))



metadata.create_all()



stmt = product_table.insert()

stmt.execute([dict(sku="123", msrp=12.34),

              dict(sku="456", msrp=22.12),

              dict(sku="789", msrp=41.44)])

stmt = store_table.insert()

stmt.execute([dict(name="Main Store"),

              dict(name="Secondary Store")])

stmt = product_price_table.insert()

stmt.execute([dict(store_id=1, sku="123"),

              dict(store_id=1, sku="456"),

              dict(store_id=1, sku="789"),

              dict(store_id=2, sku="123"),

              dict(store_id=2, sku="456"),

              dict(store_id=2, sku="789")])

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

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 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', None, ForeignKey('product.sku'), primary_key=True),

Column('store_id', None, ForeignKey('store.id'), primary_key=True),

    Column('price', Numeric, default=0))



metadata.create_all()



stmt = product_table.insert()

stmt.execute([dict(sku="123", msrp=12.34),

              dict(sku="456", msrp=22.12),

              dict(sku="789", msrp=41.44)])

stmt = store_table.insert()

stmt.execute([dict(name="Main Store"),

              dict(name="Secondary Store")])

stmt = product_price_table.insert()

stmt.execute([dict(store_id=1, sku="123"),

              dict(store_id=1, sku="456"),

              dict(store_id=1, sku="789"),

              dict(store_id=2, sku="123"),

              dict(store_id=2, sku="456"),

              dict(store_id=2, sku="789")])

In order to use SqlSoup, we must first create an instance of the SqlSoup class. This instance must be created either with an existing MetaData instance as its first argument, or with the same arguments as SQLAlchemy’s

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

You may have noticed in the previous section that when we queried the db.product table, rather than being served with RowProxy objects as in regular SQLAlchemy, we were served with MappedProduct instances. This is because technically we’re not selecting from the product table; we’re selecting from the automatically created and mapped MappedProduct class, created from the product table.

The MappedProduct class provides a basic mapping of the columns of the table to the properties of the class. It also provides a query property, similar to the Elixir query property, which provides access to a session query for the MappedProduct. It also provides insert(), delete(), and update() methods for modifying the underlying data. To create a new product, for instance, we can do the following:

>>> newprod = db.product.insert(sku='111', msrp=22.44)

>>> db.flush()

>>> db.clear()

>>> db.product.all()

[MappedProduct(sku='123',msrp=Decimal("12.34")),

... MappedProduct(sku='456',msrp=Decimal("22.12")),

... MappedProduct(sku='789',msrp=Decimal("41.44")),

... MappedProduct(sku='111',msrp=Decimal("22.44"))]

You may have noticed in the previous example that we accessed the session-like methods flush() and clear() on the SqlSoup instance. SqlSoup strives to provide a rich set of functionality with a limited set of interfaces, namely the SqlSoup instance and automatically mapped classes. As such, the SqlSoup instance provides several session-like functions as well as providing access to the automatically mapped classes:

bind(attribute): The underlying Engine or Connectable for this SqlSoup instance.
schema(attribute): Use the specified schema name for auto loading and automatically mapping tables.
clear(self ): Call the underlying contextual session’s clear() method.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

As mentioned in the list of automatically mapped class attributes and methods, mapped classes contain insert(), update(), and delete() methods. These are just thin wrappers around the corresponding methods on the underlying table. If we wanted to set the price for all products in all stores to their MSRP, for instance, we could do the following:

>>> msrp=select([db.product.c.msrp],

...             db.product.sku==db.product_price.sku)

>>> db.product_price.update(

...     values=dict(price=msrp))

>>> db.product_price.all()

[MappedProduct_price(sku='123',store_id=1,price=Decimal("12.34")),

... MappedProduct_price(sku='456',store_id=1,price=Decimal("22.12")),

... MappedProduct_price(sku='789',store_id=1,price=Decimal("41.44")),

... MappedProduct_price(sku='123',store_id=2,price=Decimal("12.34")),

... MappedProduct_price(sku='456',store_id=2,price=Decimal("22.12")),

... MappedProduct_price(sku='789',store_id=2,price=Decimal("41.44"))]

We can similarly use the insert() and delete() method to perform SQL-level inserts and deletes.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

As we’ve discussed before, SqlSoup is useful when it’s necessary to use an existing database, whereas Elixir is most useful when Elixir is the primary definition of the schema. This section compares SqlSoup and Elixir with “Bare” SQLAlchemy and gives the advantages and disadvantages of each.

Generally speaking, SqlSoup has the following pros and cons in comparison to “base” SQLAlchemy:

Succinct usage: SqlSoup requires very little code to get started: just a database URI, and you’re ready to go. Raw SQLAlchemy is much more verbose, requiring setup for tables, mappers, and mapped classes. Even if you’re using autoloading with SQLAlchemy, it still requires you to set up your mappers and mapped classes if you wish to use the ORM.
Ad-hoc queries and mappers: Due to the ease of setting up SqlSoup, it is much more convenient to create queries and mappings from joins and other selectable objects.

Rich relation support: Because SqlSoup’s method of inference does not support SQLAlchemy , it is not well-suited to schemas where it is more convenient to use mapped properties to implement relations between tables.
Adding behavior to mapped objects: Because SqlSoup creates its own automatically mapped classes, it is inconvenient to have domain logic from your application attached as methods. In this regard, SqlSoup mapped objects are little more than “smart rows” allowing convenient access to the database, with little ability to model domain objects.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

SQLAlchemy provides an extremely powerful method of defining schemas, performing queries, and manipulating data, both at the ORM level and at the SQL level. also provides several extensions to this core behavior. We have already seen one of these extensions, SqlSoup, discussed in . One of the nice things about the SQLAlchemy extensions package is that it provides a “proving ground” for functionality that may eventually make it into the core SQLAlchemy packages. When this occurs (the functionality of an extension is absorbed into the core feature set of SQLAlchemy), the extension is deprecated and eventually removed.

This chapter discusses the two remaining nondeprecated extensions available in SQLAlchemy 0.4, sqlalchemy.ext.associationproxy and sqlalchemy.ext.orderinglist. We will also describe the deprecated extensions, focusing on how to achieve the same functionality using “core” SQLAlchemy.

The association proxy extension allows our mapped classes to have attributes that are proxied from related objects. One place where this is useful is when we have two tables related via an association table that contains extra information in addition to linking the two tables. For instance, suppose we have a database containing the following schema:

user_table = Table(

    'user', metadata,

    Column('id', Integer, primary_key=True),

    Column('user_name', String(255), unique=True),

    Column('password', String(255)))



brand_table = Table(

    'brand', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String(255)))



sales_rep_table = Table(

    'sales_rep', metadata,

Column('brand_id', None, ForeignKey('brand.id'), primary_key=True),

Column('user_id', None, ForeignKey('user.id'), primary_key=True),

    Column('commission_pct', Integer, default=0))

In this case, we might want to create User, Brand, and SalesRep classes to represent our domain objects. The basic mapper setup would then be the following:

class User(object): pass

class Brand(object): pass

class SalesRep(object): pass



mapper(User, user_table, properties=dict(

    sales_rep=relation(SalesRep, backref='user', uselist=False)))

mapper(Brand, brand_table, properties=dict(

    sales_reps=relation(SalesRep, backref='brand')))

mapper(SalesRep, sales_rep_table)

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

The association proxy extension allows our mapped classes to have attributes that are proxied from related objects. One place where this is useful is when we have two tables related via an association table that contains extra information in addition to linking the two tables. For instance, suppose we have a database containing the following schema:

user_table = Table(

    'user', metadata,

    Column('id', Integer, primary_key=True),

    Column('user_name', String(255), unique=True),

    Column('password', String(255)))



brand_table = Table(

    'brand', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String(255)))



sales_rep_table = Table(

    'sales_rep', metadata,

Column('brand_id', None, ForeignKey('brand.id'), primary_key=True),

Column('user_id', None, ForeignKey('user.id'), primary_key=True),

    Column('commission_pct', Integer, default=0))

In this case, we might want to create User, Brand, and SalesRep classes to represent our domain objects. The basic mapper setup would then be the following:

class User(object): pass

class Brand(object): pass

class SalesRep(object): pass



mapper(User, user_table, properties=dict(

    sales_rep=relation(SalesRep, backref='user', uselist=False)))

mapper(Brand, brand_table, properties=dict(

    sales_reps=relation(SalesRep, backref='brand')))

mapper(SalesRep, sales_rep_table)

In such a case, we have completely mapped the data in our schema to the object model. But what if we want to have a property on the Brand object that lists all of the Users who are SalesReps for that Brand? One way we could do this in “base” SQLAlchemy is by using a property in the Brand class:

class Brand(object):

    @property

    def users(self):

        return [ sr.user for sr in self.sales_reps ]

This is not very convenient, however. It doesn’t allow us to append to or remove from the list of users, for instance. The association proxy provides a convenient solution to this problem. Using the association_proxy() function, we can add the

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

A common pattern in many applications is the use of ordered collections. For instance, consider a simple to-do list application with multiple lists, each containing an (ordered) set of items. We might start with the following schema:

todo_list_table = Table(

    'todo_list', metadata,

    Column('name', Unicode(255), primary_key=True))



todo_item_table = Table(

    'todo_item', metadata,

    Column('id', Integer, primary_key=True),

    Column('list_name', None, ForeignKey('todo_list.name')),

    Column('list_position', Integer),

    Column('value', Unicode))

SQLAlchemy provides nice support for mapping the list items to a property and sorting them via the order_by parameter:

class TodoList(object):

    def __init__(self, name):

        self.name = name

    def __repr__(self):

        return '<TodoList %s>' % self.name



class TodoItem(object):

    def __init__(self, value, position=None):

        self.value = value

        self.list_position = position

    def __repr__(self):

        return '<%s: %s>' % (self.list_position, self.value)



mapper(TodoList, todo_list, properties=dict(

    items=relation(TodoItem,

                   backref='list',

                   order_by=[todo_item_table.c.list_position])))

mapper(TodoItem, todo_item)

We can now create a list with some items:

>>> lst = TodoList('list1')

>>> session.save(lst)

>>> lst.items = [ TodoItem('Buy groceries', 0),

...               TodoItem('Do laundry', 1) ]

>>> session.flush()

>>> session.clear()

>>> 

>>> lst = session.get(TodoList, 'list1')

>>> print lst.items

[<0: Buy groceries>, <1: Do laundry>]

This approach is certainly workable, but it requires you to manually keep track of the positions of all the list items. For instance, suppose we wanted to mow the lawn buying groceries and doing laundry. To do this using base SQLAlchemy, we would need to do something like the following:

>>> lst.items.insert(1, TodoItem('Mow lawn'))

>>> for pos, it in enumerate(lst.items):

...     it.list_position = pos

Rather than “fixing up” the list after each insert or remove operation, we can instead use

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.

Inhaltsvorschau

As mentioned previously, SQLAlchemy extensions function as a sort of “proving ground” for new functionality that may someday “graduate” into SQLAlchemy proper. There are several such extensions that have graduated in the transition from the 0.3.x release series to the 0.4.x release series. These extensions are briefly described here:

sqlalchemy.ext.selectresults: The sqlalchemy.ext.selectresults extension provided generative query support for ORM queries. Since version 0.3.6, this support has been built in to the native Query class. sqlalchemy.ext.selectresults also provides a MapperExtension that adds generative query behavior on a per-mapper basis.
sqlalchemy.ext.sessioncontext: The sqlalchemy.ext.sessioncontext extension provided contextual session support. This has been deprecated in favor of the scoped_session() support in core
sqlalchemy.ext.assignmapper: The sqlalchemy.ext.assignmapper extension provided the ability to automatically save mapped objects and additional instrumentation on mapped classes above what the mapper() function normally does. This has been deprecated in favor of the Session.mapper() function available with contextual sessions created by scoped_session() in core SQLAlchemy.
sqlalchemy.ext.activemapper: The sqlalchemy.ext.activemapper extension provided a declarative layer implementing the active record pattern on SQLAlchemy. This has been deprecated in favor of the external package Elixir (), a more comprehensive declarative layer.

Ende der Inhaltsvorschau. Der weiterere Inhalt dieses Abschnitts ist hier nicht einsehbar.