SQLAlchemy 0.7 Documentation

Release: 0.7.4 | Release Date: December 9, 2011 | Download PDF

Drizzle

Support for the Drizzle database.

Supported Versions and Features

SQLAlchemy supports the Drizzle database starting with 2010.08. with capabilities increasing with more modern servers.

Most available DBAPI drivers are supported; see below.

Feature Minimum Version
sqlalchemy.orm 2010.08
Table Reflection 2010.08
DDL Generation 2010.08
utf8/Full Unicode Connections 2010.08
Transactions 2010.08
Two-Phase Transactions 2010.08
Nested Transactions 2010.08

See the official Drizzle documentation for detailed information about features supported in any given server release.

Connecting

See the API documentation on individual drivers for details on connecting.

Connection Timeouts

Drizzle features an automatic connection close behavior, for connections that have been idle for eight hours or more. To circumvent having this issue, use the pool_recycle option which controls the maximum age of any connection:

engine = create_engine('drizzle+mysqldb://...', pool_recycle=3600)

Storage Engines

Drizzle defaults to the InnoDB storage engine, which is transactional.

Storage engines can be elected when creating tables in SQLAlchemy by supplying a drizzle_engine='whatever' to the Table constructor. Any Drizzle table creation option can be specified in this syntax:

Table('mytable', metadata,
      Column('data', String(32)),
      drizzle_engine='InnoDB',
     )

Keys

Not all Drizzle storage engines support foreign keys. For BlitzDB and similar engines, the information loaded by table reflection will not include foreign keys. For these tables, you may supply a ForeignKeyConstraint at reflection time:

Table('mytable', metadata,
      ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
      autoload=True
     )

When creating tables, SQLAlchemy will automatically set AUTO_INCREMENT on an integer primary key column:

>>> t = Table('mytable', metadata,
...   Column('mytable_id', Integer, primary_key=True)
... )
>>> t.create()
CREATE TABLE mytable (
        id INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id)
)

You can disable this behavior by supplying autoincrement=False to the Column. This flag can also be used to enable auto-increment on a secondary column in a multi-column key for some storage engines:

Table('mytable', metadata,
      Column('gid', Integer, primary_key=True, autoincrement=False),
      Column('id', Integer, primary_key=True)
     )

Drizzle SQL Extensions

Many of the Drizzle SQL extensions are handled through SQLAlchemy’s generic function and operator support:

table.select(table.c.password==func.md5('plaintext'))
table.select(table.c.username.op('regexp')('^[a-d]'))

And of course any valid Drizzle statement can be executed as a string as well.

Some limited direct support for Drizzle extensions to SQL is currently available.

  • SELECT pragma:

    select(..., prefixes=['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
  • UPDATE with LIMIT:

    update(..., drizzle_limit=10)

Drizzle Data Types

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with Drizzle are importable from the top level dialect:

from sqlalchemy.dialects.drizzle import \
        BIGINT, BINARY, BLOB, BOOLEAN, CHAR, DATE, DATETIME,
        DECIMAL, DOUBLE, ENUM, FLOAT, INT, INTEGER,
        NUMERIC, TEXT, TIME, TIMESTAMP, VARBINARY, VARCHAR

Types which are specific to Drizzle, or have Drizzle-specific construction arguments, are as follows:

class sqlalchemy.dialects.drizzle.BIGINT(**kw)

Bases: sqlalchemy.types.BIGINT

Drizzle BIGINTEGER type.

__init__(**kw)

Construct a BIGINTEGER.

class sqlalchemy.dialects.drizzle.CHAR(length=None, **kwargs)

Bases: sqlalchemy.dialects.drizzle.base._StringType, sqlalchemy.types.CHAR

Drizzle CHAR type, for fixed-length character data.

__init__(length=None, **kwargs)

Construct a CHAR.

Parameters:
  • length – Maximum data length, in characters.
  • binary – Optional, use the default binary collation for the national character set. This does not affect the type of data stored, use a BINARY type for binary data.
  • collation – Optional, request a particular collation. Must be compatible with the national character set.
class sqlalchemy.dialects.drizzle.DECIMAL(precision=None, scale=None, asdecimal=True, **kw)

Bases: sqlalchemy.dialects.drizzle.base._NumericType, sqlalchemy.types.DECIMAL

Drizzle DECIMAL type.

__init__(precision=None, scale=None, asdecimal=True, **kw)

Construct a DECIMAL.

Parameters:
  • precision – Total digits in this number. If scale and precision are both None, values are stored to limits allowed by the server.
  • scale – The number of digits after the decimal point.
class sqlalchemy.dialects.drizzle.DOUBLE(precision=None, scale=None, asdecimal=True, **kw)

Bases: sqlalchemy.dialects.drizzle.base._FloatType

Drizzle DOUBLE type.

__init__(precision=None, scale=None, asdecimal=True, **kw)

Construct a DOUBLE.

Parameters:
  • precision – Total digits in this number. If scale and precision are both None, values are stored to limits allowed by the server.
  • scale – The number of digits after the decimal point.
class sqlalchemy.dialects.drizzle.ENUM(*enums, **kw)

Bases: sqlalchemy.dialects.mysql.base.ENUM

Drizzle ENUM type.

__init__(*enums, **kw)

Construct an ENUM.

Example:

Column(‘myenum’, ENUM(“foo”, “bar”, “baz”))
Parameters:
  • enums – The range of valid values for this ENUM. Values will be quoted when generating the schema according to the quoting flag (see below).
  • strict – Defaults to False: ensure that a given value is in this ENUM’s range of permissible values when inserting or updating rows. Note that Drizzle will not raise a fatal error if you attempt to store an out of range value- an alternate value will be stored instead. (See Drizzle ENUM documentation.)
  • collation – Optional, a column-level collation for this string value. Takes precedence to ‘binary’ short-hand.
  • binary – Defaults to False: short-hand, pick the binary collation type that matches the column’s character set. Generates BINARY in schema. This does not affect the type of data stored, only the collation of character data.
  • quoting

    Defaults to ‘auto’: automatically determine enum value quoting. If all enum values are surrounded by the same quoting character, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.

    ‘quoted’: values in enums are already quoted, they will be used directly when generating the schema - this usage is deprecated.

    ‘unquoted’: values in enums are not quoted, they will be escaped and surrounded by single quotes when generating the schema.

    Previous versions of this type always required manually quoted values to be supplied; future versions will always quote the string literals for you. This is a transitional option.

class sqlalchemy.dialects.drizzle.FLOAT(precision=None, scale=None, asdecimal=False, **kw)

Bases: sqlalchemy.dialects.drizzle.base._FloatType, sqlalchemy.types.FLOAT

Drizzle FLOAT type.

__init__(precision=None, scale=None, asdecimal=False, **kw)

Construct a FLOAT.

Parameters:
  • precision – Total digits in this number. If scale and precision are both None, values are stored to limits allowed by the server.
  • scale – The number of digits after the decimal point.
class sqlalchemy.dialects.drizzle.INTEGER(**kw)

Bases: sqlalchemy.types.INTEGER

Drizzle INTEGER type.

__init__(**kw)

Construct an INTEGER.

class sqlalchemy.dialects.drizzle.NUMERIC(precision=None, scale=None, asdecimal=True, **kw)

Bases: sqlalchemy.dialects.drizzle.base._NumericType, sqlalchemy.types.NUMERIC

Drizzle NUMERIC type.

__init__(precision=None, scale=None, asdecimal=True, **kw)

Construct a NUMERIC.

Parameters:
  • precision – Total digits in this number. If scale and precision are both None, values are stored to limits allowed by the server.
  • scale – The number of digits after the decimal point.
class sqlalchemy.dialects.drizzle.REAL(precision=None, scale=None, asdecimal=True, **kw)

Bases: sqlalchemy.dialects.drizzle.base._FloatType, sqlalchemy.types.REAL

Drizzle REAL type.

__init__(precision=None, scale=None, asdecimal=True, **kw)

Construct a REAL.

Parameters:
  • precision – Total digits in this number. If scale and precision are both None, values are stored to limits allowed by the server.
  • scale – The number of digits after the decimal point.
class sqlalchemy.dialects.drizzle.TEXT(length=None, **kw)

Bases: sqlalchemy.dialects.drizzle.base._StringType, sqlalchemy.types.TEXT

Drizzle TEXT type, for text up to 2^16 characters.

__init__(length=None, **kw)

Construct a TEXT.

Parameters:
  • length – Optional, if provided the server may optimize storage by substituting the smallest TEXT type sufficient to store length characters.
  • collation – Optional, a column-level collation for this string value. Takes precedence to ‘binary’ short-hand.
  • binary – Defaults to False: short-hand, pick the binary collation type that matches the column’s character set. Generates BINARY in schema. This does not affect the type of data stored, only the collation of character data.
class sqlalchemy.dialects.drizzle.TIMESTAMP(timezone=False)

Bases: sqlalchemy.types.TIMESTAMP

Drizzle TIMESTAMP type.

__init__(timezone=False)

Construct a new DateTime.

Parameters:timezone – boolean. If True, and supported by the

backend, will produce ‘TIMESTAMP WITH TIMEZONE’. For backends that don’t support timezone aware timestamps, has no effect.

class sqlalchemy.dialects.drizzle.VARCHAR(length=None, **kwargs)

Bases: sqlalchemy.dialects.drizzle.base._StringType, sqlalchemy.types.VARCHAR

Drizzle VARCHAR type, for variable-length character data.

__init__(length=None, **kwargs)

Construct a VARCHAR.

Parameters:
  • collation – Optional, a column-level collation for this string value. Takes precedence to ‘binary’ short-hand.
  • binary – Defaults to False: short-hand, pick the binary collation type that matches the column’s character set. Generates BINARY in schema. This does not affect the type of data stored, only the collation of character data.

MySQL-Python Notes

Support for the Drizzle database via the Drizzle-python adapter.

Drizzle-Python is available at:

At least version 1.2.1 or 1.2.2 should be used.

Connecting

Connect string format:

drizzle+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

Unicode

Drizzle accommodates Python unicode objects directly and uses the utf8 encoding in all cases.

Known Issues

Drizzle-python at least as of version 1.2.2 has a serious memory leak related to unicode conversion, a feature which is disabled via use_unicode=0. The recommended connection form with SQLAlchemy is:

engine = create_engine('mysql://scott:tiger@localhost/test?charset=utf8&use_unicode=0', pool_recycle=3600)