Essential SQLAlchemy, 2nd Edition (2010)

Chapter 4. SQLAlchemy Type Engines

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.

Type System Overview

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 autoload=True, 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 metadata.create_all() 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.

Built-in Types

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.

Note

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.

Generic 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 inTable 4-1. 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 4-1. Built-in generic TypeEngines

When using TypeEngines to specify columns in Tables, you can use an instance of the TypeEngine class or the class itself. If you use the class, the default parameters will be used when constructing the SQL type. For instance, the following Python code:

test_table3 = Table(

'test3', metadata,

Column('c0', Numeric),

Column('c1', Numeric(4,6)),

Column('c3', String),

Column('c4', String(10)))

yields the following SQL creation (in SQLite):

CREATE TABLE test3 (

c0 NUMERIC(10, 2),

c1 NUMERIC(4, 6),

c3 TEXT,

c4 VARCHAR(10)

)

Dialect-Specific Types

To generate appropriate dialect-specific SQL CREATE TABLE statements from these generic types, SQLAlchemy compiles those generic TypeEngines into dialect-specific TypeEngines. In some cases, in addition to implementing the generic types, a dialect may provide dialect-specific types (such as IP address, etc.).

Some of the dialect-specific types don’t actually provide any special support for converting between database values and Python values; these are generally used for completeness, particularly when reflecting tables. In this case, no conversion is done between the value supplied by the DB-API implementation and the application. This behavior is indicated in the following tables by listing “none” as the Python type for that TypeEngine. Tables 4-2 through 4-5 list some of the types provided by particular database engines that are not automatically used by SQLAlchemy.

Table 4-2. MS SQL server types

Table 4-3. MySQL types

Table 4-4. Oracle types

Table 4-5. PostgreSQL types

Application-Specific Custom Types

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.

Implementing a TypeDecorator

To implement a TypeDecorator, you must provide the base TypeEngine you are “implementing” as well as two functions, convert_bind_param() and convert_result_value(). 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)

It is not necessary to specify in a TypeDecorator the SQL type used to implement the column, as this will be obtained from the impl attribute. The TypeDecorator is used only when an existing TypeEngine provides the correct SQL type for the type you are implementing.

Performance-Conscious TypeDecorators

SQLAlchemy has a second, undocumented (at the time of this book’s writing) interface for providing bind parameter and result value conversion. If you provide a bind_processor() or result_processor() method in your TypeDecorator, then these will be used instead of theconvert_bind_param() and convert_result_value() methods. The new “processor” interface methods take a database dialect as a parameter and return a conversion function (a “processor”) that takes a single value parameter and returns the (possibly converted) value. If no processing is necessary, you can simply return None rather than a new processor:

>>> from sqlalchemy import types

>>> import sqlalchemy.databases.sqlite as sqlite

>>>

>>> 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 bind_processor(self, dialect):

... impl_processor = self.impl.bind_processor(dialect)

... if impl_processor:

... def processor(value):

... result = impl_processor(value)

... assert value in self._enum_values, \

... "Value %s must be one of %s" % (result,

... self._enum_values)

... return result

... else:

... def processor(value):

... assert value in self._enum_values, \

... "Value %s must be one of %s" % (value,

... self._enum_values)

... return value

... return processor

...

>>> mce=MyCustomEnum([1,2,3])

>>> processor = mce.bind_processor(sqlite.dialect())

>>> print processor(1)

1

>>> print processor(5)

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

File "<stdin>", line 17, in processor

AssertionError: Value 5 must be one of [1, 2, 3]

Creating a New TypeEngine

If creating a TypeDecorator is insufficient for your new type (such as when supporting a new SQL type), you can directly subclass the TypeEngine class. In this case, in addition to providing the convert_bind_param() and convert_result_value() methods, you must also provide the get_col_spec method for SQLAlchemy to use in its create_table() implementation.

To create a new TypeEngine to implement the SQL type “NEWTYPE”, for instance, you might use the following class declaration:

class NewType(types.TypeEngine):

def __init__(self, *args):

self._args = args

def get_col_spec(self):

return 'NEWTYPE(%s)' % ','.join(self._args)

def convert_bind_param(self, value, engine):

return value

def convert_result_value(self, value, engine):

return value