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

Class name

Python type

SQL type (for SQLite driver)

Arguments

String

string

TEXT or VARCHAR

length (default is unbounded)

Integer

int

INTEGER

none

SmallInteger

int

SMALLINT

none

Numeric

float, Decimal

NUMERIC

precision=10 , length=2

Float(Numeric)

float

NUMERIC

precision=10

DateTime

datetime.datetime

TIMESTAMP

none

Date

datetime.date

DATE

none

Time

datetime.time

TIME

none

Binary

byte string

BLOB

length (default is unbounded)

Boolean

bool

BOOLEAN

none

Unicode

unicode

TEXT or VARCHAR

length (default is unbounded)

PickleType

any object that can be pickled

BLOB

none

FLOAT(Numeric)

float, Decimal

NUMERIC

precision=10 ,length=2

TEXT(String)

string

TEXT

length (default is unbounded)

DECIMAL(Numeric)

float, Decimal

NUMERIC

precision=10,length=2

INT, INTEGER(Integer)

int

INTEGER

none

TIMESTAMP(DateTime)

datetime.datetime

TIMESTAMP

none

DATETIME(DateTime)

datetime.datetime

TIMESTAMP

none

CLOB(String)

string

TEXT

length (default is unbounded)

VARCHAR(String)

string

VARCHAR or TEXT

length (default is unbounded)

CHAR(String)

string

CHAR or TEXT

length (default is unbounded)

NCHAR(Unicode)

string

VARCHAR, NCHAR, or TEXT

length (default is unbounded)

BLOB(Binary)

byte string

BLOB

length (default is unbounded)

BOOLEAN(Boolean)

bool

BOOLEAN

none

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

Class name

Python type

SQL type

Arguments

MSMoney

none

MONEY

none

MSSmallMoney

none

SMALLMONEY

none

AdoMSNVarchar

unicode

NVARCHAR

length

MSBigInteger

int

BIGINT

none

MSTinyInteger

int

TINYINT

none

MSVariant

none

SQL_VARIANT

none

MSUniqueIdentifier

none

UNIQUEIDENTIFIER

none

Table 4-3. MySQL types

Class name

Python type

SQL type

Arguments

MSEnum

string

ENUM

values

MSTinyInteger

int

TINYINT

length

MSBigInteger

int

BIGINT

length

MSDouble

float

DOUBLE

length=10,precision=2

MSTinyText

string

TINYTEXT

none

MSMediumText

string

MEDIUMTEXT

none

MSLongText

string

LONGTEXT

none

MSNVarChar

unicode

NATIONAL VARCHAR

length

MSTinyBlob

byte string

TINYBLOB

none

MSMediumBlob

byte string

MEDIUMBLOB

none

MSLongBlob

byte string

LONGBLOB

none

MSBinary

byte string

BINARY

length

MSVarBinary

byte string

VARBINARY

length

MSSet

set

SET

set values

MSYear

int

YEAR

length

MSBit

long

BIT

length

Table 4-4. Oracle types

Class name

Python type

SQL type

Arguments

OracleRaw

byte string

RAW

length

Table 4-5. PostgreSQL types

Class name

Python type

SQL type

Arguments

PGArray

any TypeEngine

type engine[]

TypeEngine

PGBigInteger

int, long

BIGINT

none

PGInet

none

INET

none

PGInterval

none

INTERVAL

none

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( selfvalueengine) is used to convert Python values to SQL values suitable for the DB-API driver, and convert_result_value( selfvalueengine) 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