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 in Table 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 namePython type SQL type (for SQLite driver) Arguments
String string TEXT or VARCHARlength (default is unbounded)
Integer int INTEGER none
SmallInteger int SMALLINT none
Numeric float, DecimalNUMERIC 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 BLOBlength (default is unbounded)
Boolean bool BOOLEAN none
Unicode unicode TEXT or VARCHARlength (default is unbounded)
PickleType any object that can be pickledBLOB none
FLOAT(Numeric) float, Decimal NUMERIC precision=10 ,length=2
TEXT(String) string TEXTlength (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 TEXTlength (default is unbounded)
VARCHAR(String) string VARCHAR or TEXTlength (default is unbounded)
CHAR(String) string CHAR or TEXTlength (default is unbounded)
NCHAR(Unicode) string VARCHAR, NCHAR, or TEXTlength (default is unbounded)
BLOB(Binary) byte string BLOBlength (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 namePython typeSQL typeArguments
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 namePython typeSQL typeArguments
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 namePython typeSQL typeArguments
OracleRaw byte string RAW length

Table 4-5. PostgreSQL types

Class namePython typeSQL typeArguments
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( 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.

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

Get Essential SQLAlchemy now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.