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
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 TypeEngine
s.
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)
.
TypeEngine
s 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
TypeEngine
s, which are fairly portable across
different database engines. Second, it provides database server-specific
TypeEngine
s, which can be used to exploit
particular types supported by certain databases. Third, SQLAlchemy allows
you to define application-specific custom
TypeEngine
s if you wish to further customize object
conversion to/from the database.
SQLAlchemy provides a fairly complete set of built-in TypeEngine
s for
support of basic SQL column types. The SQLAlchemy-provided
TypeEngine
s 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 TypeEngine
s 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 TypeEngine
s if you wish to
exploit database server-specific types.
The generic TypeEngine
s provided by
SQLAlchemy are found in the sqlalchemy.types package. These TypeEngine
s cover a fairly
complete set of portable column types. The
TypeEngine
s supported, their corresponding Python
type, and their SQL representation, are listed in Table 4-1. Note that there are several
TypeEngine
s defined in all caps (such as CLOB
). These are derived from other
TypeEngine
s 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 TypeEngine
s to specify columns
in Table
s, 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) )
To generate appropriate dialect-specific SQL CREATE TABLE statements
from these generic types, SQLAlchemy compiles those generic
TypeEngine
s into dialect-specific
TypeEngine
s. 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
|
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
()
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.
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.