Column types

SQLAlchemy comes with a set of column types that can be used. These column types will trigger the use of a certain QtGui.QDelegate to visualize them in the views. Camelot extends those SQLAlchemy field types with some of its own.

An overview of field types from SQLAlchemy and Camelot is given in the table below :

SQLAlchemy column types

SQLAlchemy provides a number of column types that map to available data types in SQL, more information on those can be found on the SQLAlchemy website .

The types used mosed common are :

class sqlalchemy.types.Boolean(create_constraint=True, name=None)

A bool datatype.

Boolean typically uses BOOLEAN or SMALLINT on the DDL side, and on the Python side deals in True or False.

class sqlalchemy.types.Date(*args, **kwargs)

A type for datetime.date() objects.

class sqlalchemy.types.DateTime(timezone=False)

A type for datetime.datetime() objects.

Date and time types return objects from the Python datetime module. Most DBAPIs have built in support for the datetime module, with the noted exception of SQLite. In the case of SQLite, date and time types are stored as strings which are then converted back to datetime objects when rows are returned.

class sqlalchemy.types.Float(precision=None, asdecimal=False, **kwargs)

A type for float numbers.

Returns Python float objects by default, applying conversion as needed.

class sqlalchemy.types.Integer(*args, **kwargs)

A type for int integers.

class sqlalchemy.types.Numeric(precision=None, scale=None, asdecimal=True)

A type for fixed precision numbers.

Typically generates DECIMAL or NUMERIC. Returns decimal.Decimal objects by default, applying conversion as needed.

Note

The cdecimal library is a high performing alternative to Python’s built-in decimal.Decimal type, which performs very poorly in high volume situations. SQLAlchemy 0.7 is tested against cdecimal and supports it fully. The type is not necessarily supported by DBAPI implementations however, most of which contain an import for plain decimal in their source code, even though some such as psycopg2 provide hooks for alternate adapters. SQLAlchemy imports decimal globally as well. While the alternate Decimal class can be patched into SQLA’s decimal module, overall the most straightforward and foolproof way to use “cdecimal” given current DBAPI and Python support is to patch it directly into sys.modules before anything else is imported:

import sys
import cdecimal
sys.modules["decimal"] = cdecimal

While the global patch is a little ugly, it’s particularly important to use just one decimal library at a time since Python Decimal and cdecimal Decimal objects are not currently compatible with each other:

>>> import cdecimal
>>> import decimal
>>> decimal.Decimal("10") == cdecimal.Decimal("10")
False

SQLAlchemy will provide more natural support of cdecimal if and when it becomes a standard part of Python installations and is supported by all DBAPIs.

class sqlalchemy.types.Time(timezone=False)

A type for datetime.time() objects.

class sqlalchemy.types.Unicode(length=None, **kwargs)

A variable length Unicode string type.

The Unicode type is a String subclass that assumes input and output as Python unicode data, and in that regard is equivalent to the usage of the convert_unicode flag with the String type. However, unlike plain String, it also implies an underlying column type that is explicitly supporting of non-ASCII data, such as NVARCHAR on Oracle and SQL Server. This can impact the output of CREATE TABLE statements and CAST functions at the dialect level, and can also affect the handling of bound parameters in some specific DBAPI scenarios.

The encoding used by the Unicode type is usually determined by the DBAPI itself; most modern DBAPIs feature support for Python unicode objects as bound values and result set values, and the encoding should be configured as detailed in the notes for the target DBAPI in the Dialects section.

For those DBAPIs which do not support, or are not configured to accommodate Python unicode objects directly, SQLAlchemy does the encoding and decoding outside of the DBAPI. The encoding in this scenario is determined by the encoding flag passed to create_engine().

When using the Unicode type, it is only appropriate to pass Python unicode objects, and not plain str. If a plain str is passed under Python 2, a warning is emitted. If you notice your application emitting these warnings but you’re not sure of the source of them, the Python warnings filter, documented at http://docs.python.org/library/warnings.html, can be used to turn these warnings into exceptions which will illustrate a stack trace:

import warnings
warnings.simplefilter('error')

For an application that wishes to pass plain bytestrings and Python unicode objects to the Unicode type equally, the bytestrings must first be decoded into unicode. The recipe at Coercing Encoded Strings to Unicode illustrates how this is done.

See also:

UnicodeText - unlengthed textual counterpart to Unicode.

Camelot column types