Data types

SQLAlchemy-Utils provides various new data types for SQLAlchemy. In order to gain full advantage of these datatypes you should use automatic data coercion. See force_auto_coercion() for how to set up this feature.

ArrowType

class sqlalchemy_utils.types.arrow.ArrowType(*args, **kwargs)[source]

ArrowType provides way of saving Arrow objects into database. It automatically changes Arrow objects to datetime objects on the way in and datetime objects back to Arrow objects on the way out (when querying database). ArrowType needs Arrow library installed.

from datetime import datetime
from sqlalchemy_utils import ArrowType
import arrow


class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    created_at = sa.Column(ArrowType)



article = Article(created_at=arrow.utcnow())

As you may expect all the arrow goodies come available:

article.created_at = article.created_at.replace(hours=-1)

article.created_at.humanize()
# 'an hour ago'

ChoiceType

class sqlalchemy_utils.types.choice.ChoiceType(choices, impl=None)[source]

ChoiceType offers way of having fixed set of choices for given column. It could work with a list of tuple (a collection of key-value pairs), or integrate with enum in the standard library of Python 3.4+ (the enum34 backported package on PyPI is compatible too for < 3.4).

Columns with ChoiceTypes are automatically coerced to Choice objects while a list of tuple been passed to the constructor. If a subclass of enum.Enum is passed, columns will be coerced to enum.Enum objects instead.

class User(Base):
    TYPES = [
        (u'admin', u'Admin'),
        (u'regular-user', u'Regular user')
    ]

    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    type = sa.Column(ChoiceType(TYPES))


user = User(type=u'admin')
user.type  # Choice(type='admin', value=u'Admin')

Or:

import enum


class UserType(enum.Enum):
    admin = 1
    regular = 2


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    type = sa.Column(ChoiceType(UserType, impl=sa.Integer()))


user = User(type=1)
user.type  # <UserType.admin: 1>

ChoiceType is very useful when the rendered values change based on user’s locale:

from babel import lazy_gettext as _


class User(Base):
    TYPES = [
        (u'admin', _(u'Admin')),
        (u'regular-user', _(u'Regular user'))
    ]

    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    type = sa.Column(ChoiceType(TYPES))


user = User(type=u'admin')
user.type  # Choice(type='admin', value=u'Admin')

print user.type  # u'Admin'

Or:

from enum import Enum
from babel import lazy_gettext as _


class UserType(Enum):
    admin = 1
    regular = 2


UserType.admin.label = _(u'Admin')
UserType.regular.label = _(u'Regular user')


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    type = sa.Column(ChoiceType(TYPES))


user = User(type=UserType.admin)
user.type  # <UserType.admin: 1>

print user.type.label  # u'Admin'

ColorType

class sqlalchemy_utils.types.color.ColorType(max_length=20, *args, **kwargs)[source]

ColorType provides a way for saving Color (from colour package) objects into database. ColorType saves Color objects as strings on the way in and converts them back to objects when querying the database.

from colour import Color
from sqlalchemy_utils import ColorType


class Document(Base):
    __tablename__ = 'document'
    id = sa.Column(sa.Integer, autoincrement=True)
    name = sa.Column(sa.Unicode(50))
    background_color = sa.Column(ColorType)


document = Document()
document.background_color = Color('#F5F5F5')
session.commit()

Querying the database returns Color objects:

document = session.query(Document).first()

document.background_color.hex
# '#f5f5f5'

CountryType

class sqlalchemy_utils.types.country.CountryType(*args, **kwargs)[source]

Changes Country objects to a string representation on the way in and changes them back to Country objects on the way out.

In order to use CountryType you need to install Babel first.

from sqlalchemy_utils import CountryType, Country


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True)
    name = sa.Column(sa.Unicode(255))
    country = sa.Column(CountryType)


user = User()
user.country = Country('FI')
session.add(user)
session.commit()

user.country  # Country('FI')
user.country.name  # Finland

print user.country  # Finland

CountryType is scalar coercible:

user.country = 'US'
user.country  # Country('US')

EncryptedType

class sqlalchemy_utils.types.encrypted.EncryptedType(type_in=None, key=None, engine=None, **kwargs)[source]

EncryptedType provides a way to encrypt and decrypt values, to and from databases, that their type is a basic SQLAlchemy type. For example Unicode, String or even Boolean. On the way in, the value is encrypted and on the way out the stored value is decrypted.

EncryptedType needs Cryptography library in order to work. A simple example is given below.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy_utils import EncryptedType


secret_key = 'secretkey1234'
# setup
engine = create_engine('sqlite:///:memory:')
connection = engine.connect()
Base = declarative_base()

class User(Base):
    __tablename__ = "user"
    id = sa.Column(sa.Integer, primary_key=True)
    username = sa.Column(EncryptedType(sa.Unicode, secret_key))
    access_token = sa.Column(EncryptedType(sa.String, secret_key))
    is_active = sa.Column(EncryptedType(sa.Boolean, secret_key))
    number_of_accounts = sa.Column(EncryptedType(sa.Integer,
                                                 secret_key))

sa.orm.configure_mappers()
Base.metadata.create_all(connection)

# create a configured "Session" class
Session = sessionmaker(bind=connection)

# create a Session
session = Session()

# example
user_name = u'secret_user'
test_token = 'atesttoken'
active = True
num_of_accounts = 2

user = User(username=user_name, access_token=test_token,
            is_active=active, accounts_num=accounts)
session.add(user)
session.commit()

print('id: {}'.format(user.id))
print('username: {}'.format(user.username))
print('token: {}'.format(user.access_token))
print('active: {}'.format(user.is_active))
print('accounts: {}'.format(user.accounts_num))

# teardown
session.close_all()
Base.metadata.drop_all(connection)
connection.close()
engine.dispose()

The key parameter accepts a callable to allow for the key to change per-row instead of be fixed for the whole table.

::
def get_key():
return ‘dynamic-key’
class User(Base):

__tablename__ = ‘user’ id = sa.Column(sa.Integer, primary_key=True) username = sa.Column(EncryptedType(

sa.Unicode, get_key))

JSONType

class sqlalchemy_utils.types.json.JSONType(*args, **kwargs)[source]

JSONType offers way of saving JSON data structures to database. On PostgreSQL the underlying implementation of this data type is ‘json’ while on other databases its simply ‘text’.

from sqlalchemy_utils import JSONType


class Product(Base):
    __tablename__ = 'product'
    id = sa.Column(sa.Integer, autoincrement=True)
    name = sa.Column(sa.Unicode(50))
    details = sa.Column(JSONType)


product = Product()
product.details = {
    'color': 'red',
    'type': 'car',
    'max-speed': '400 mph'
}
session.commit()

LocaleType

class sqlalchemy_utils.types.locale.LocaleType[source]

LocaleType saves Babel Locale objects into database. The Locale objects are converted to string on the way in and back to object on the way out.

In order to use LocaleType you need to install Babel first.

from sqlalchemy_utils import LocaleType
from babel import Locale


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True)
    name = sa.Column(sa.Unicode(50))
    locale = sa.Column(LocaleType)


user = User()
user.locale = Locale('en_US')
session.add(user)
session.commit()

Like many other types this type also supports scalar coercion:

user.locale = 'de_DE'
user.locale  # Locale('de_DE')

IPAddressType

class sqlalchemy_utils.types.ip_address.IPAddressType(max_length=50, *args, **kwargs)[source]

Changes IPAddress objects to a string representation on the way in and changes them back to IPAddress objects on the way out.

IPAddressType uses ipaddress package on Python >= 3 and ipaddr package on Python 2. In order to use IPAddressType with python you need to install ipaddr first.

from sqlalchemy_utils import IPAddressType


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True)
    name = sa.Column(sa.Unicode(255))
    ip_address = sa.Column(IPAddressType)


user = User()
user.ip_address = '123.123.123.123'
session.add(user)
session.commit()

user.ip_address  # IPAddress object

PasswordType

class sqlalchemy_utils.types.password.PasswordType(max_length=None, **kwargs)[source]

PasswordType hashes passwords as they come into the database and allows verifying them using a pythonic interface.

All keyword arguments (aside from max_length) are forwarded to the construction of a passlib.context.CryptContext object.

The following usage will create a password column that will automatically hash new passwords as pbkdf2_sha512 but still compare passwords against pre-existing md5_crypt hashes. As passwords are compared; the password hash in the database will be updated to be pbkdf2_sha512.

class Model(Base):
    password = sa.Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
            'md5_crypt'
        ],

        deprecated=['md5_crypt']
    ))

Verifying password is as easy as:

target = Model()
target.password = 'b'
# '$5$rounds=80000$H.............'

target.password == 'b'
# True

PhoneNumberType

class sqlalchemy_utils.types.phone_number.PhoneNumberType(country_code='US', max_length=20, *args, **kwargs)[source]

Changes PhoneNumber objects to a string representation on the way in and changes them back to PhoneNumber objects on the way out. If E164 is used as storing format, no country code is needed for parsing the database value to PhoneNumber object.

class User(self.Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    phone_number = sa.Column(PhoneNumberType())


user = User(phone_number='+358401234567')

user.phone_number.e164  # u'+358401234567'
user.phone_number.international  # u'+358 40 1234567'
user.phone_number.national  # u'040 1234567'

ScalarListType

class sqlalchemy_utils.types.scalar_list.ScalarListType(coerce_func=<type 'unicode'>, separator=u', ')[source]

ScalarListType type provides convenient way for saving multiple scalar values in one column. ScalarListType works like list on python side and saves the result as comma-separated list in the database (custom separators can also be used).

Example

from sqlalchemy_utils import ScalarListType


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, autoincrement=True)
    hobbies = sa.Column(ScalarListType())


user = User()
user.hobbies = [u'football', u'ice_hockey']
session.commit()

You can easily set up integer lists too:

from sqlalchemy_utils import ScalarListType


class Player(Base):
    __tablename__ = 'player'
    id = sa.Column(sa.Integer, autoincrement=True)
    points = sa.Column(ScalarListType(int))


player = Player()
player.points = [11, 12, 8, 80]
session.commit()

TimezoneType

class sqlalchemy_utils.types.timezone.TimezoneType(backend='dateutil')[source]

TimezoneType provides a way for saving timezones (from either the pytz or the dateutil package) objects into database. TimezoneType saves timezone objects as strings on the way in and converts them back to objects when querying the database.

from sqlalchemy_utils import TimezoneType

class User(Base):
    __tablename__ = 'user'

    # Pass backend='pytz' to change it to use pytz (dateutil by
    # default)
    timezone = sa.Column(TimezoneType(backend='pytz'))

TSVectorType

class sqlalchemy_utils.types.ts_vector.TSVectorType(*args, **kwargs)[source]

Note

This type is PostgreSQL specific and is not supported by other dialects.

Provides additional functionality for SQLAlchemy PostgreSQL dialect’s TSVECTOR type. This additional functionality includes:

  • Vector concatenation
  • regconfig constructor parameter which is applied to match function if no postgresql_regconfig parameter is given
  • Provides extensible base for extensions such as SQLAlchemy-Searchable
from sqlalchemy_utils import TSVectorType


class Article(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(100))
    search_vector = sa.Column(TSVectorType)


# Find all articles whose name matches 'finland'
session.query(Article).filter(Article.search_vector.match('finland'))

TSVectorType also supports vector concatenation.

class Article(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(100))
    name_vector = sa.Column(TSVectorType)
    content = sa.Column(sa.String)
    content_vector = sa.Column(TSVectorType)

# Find all articles whose name or content matches 'finland'
session.query(Article).filter(
    (Article.name_vector | Article.content_vector).match('finland')
)

You can configure TSVectorType to use a specific regconfig.

class Article(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(100))
    search_vector = sa.Column(
        TSVectorType(regconfig='pg_catalog.simple')
    )

Now expression such as:

Article.search_vector.match('finland')

Would be equivalent to SQL:

search_vector @@ to_tsquery('pg_catalog.simgle', 'finland')

URLType

class sqlalchemy_utils.types.url.URLType(*args, **kwargs)[source]

URLType stores furl objects into database.

from sqlalchemy_utils import URLType
from furl import furl


class User(Base):
    __tablename__ = 'user'

    id = sa.Column(sa.Integer, primary_key=True)
    website = sa.Column(URLType)


user = User(website=u'www.example.com')

# website is coerced to furl object, hence all nice furl operations come
# available
user.website.args['some_argument'] = '12'

print user.website
# www.example.com?some_argument=12

UUIDType

class sqlalchemy_utils.types.uuid.UUIDType(binary=True, native=True)[source]

Stores a UUID in the database natively when it can and falls back to a BINARY(16) or a CHAR(32) when it can’t.

from sqlalchemy_utils import UUIDType
import uuid

class User(Base):
    __tablename__ = 'user'

    # Pass `binary=False` to fallback to CHAR instead of BINARY
    id = sa.Column(UUIDType(binary=False), primary_key=True)

WeekDaysType

class sqlalchemy_utils.types.weekdays.WeekDaysType(*args, **kwargs)[source]

WeekDaysType offers way of saving WeekDays objects into database. The WeekDays objects are converted to bit strings on the way in and back to WeekDays objects on the way out.

In order to use WeekDaysType you need to install Babel first.

from sqlalchemy_utils import WeekDaysType, WeekDays
from babel import Locale


class Schedule(Base):
    __tablename__ = 'schedule'
    id = sa.Column(sa.Integer, autoincrement=True)
    working_days = sa.Column(WeekDaysType)


schedule = Schedule()
schedule.working_days = WeekDays('0001111')
session.add(schedule)
session.commit()

print schedule.working_days  # Thursday, Friday, Saturday, Sunday

WeekDaysType also supports scalar coercion:

schedule.working_days = '1110000'
schedule.working_days  # WeekDays object