# 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)

  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)

  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.

  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 = [
              ('admin', 'Admin'),
              ('regular-user', '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='admin')
      user.type  # Choice(code='admin', value='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 = [
              ('admin', _('Admin')),
              ('regular-user', _('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='admin')
      user.type  # Choice(code='admin', value='Admin')

      print user.type  # 'Admin'

  Or:

      from enum import Enum
      from babel import lazy_gettext as _

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

      UserType.admin.label = _('Admin')
      UserType.regular.label = _('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(UserType, impl=sa.Integer()))

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

      print user.type.label  # 'Admin'

## ColorType

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

  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'

## CompositeType

CompositeType provides means to interact with PostgreSQL composite types. Currently this type features:

- Easy attribute access to composite type fields

- Supports SQLAlchemy TypeDecorator types

- Ability to include composite types as part of PostgreSQL arrays

- Type creation and dropping

### Installation

CompositeType automatically attaches before_create and after_drop DDL listeners. These listeners create and drop the composite type in the database. This means it works out of the box in your test environment where you create the tables on each test run.

When you already have your database set up you should call `register_composites()` after you’ve set up all models.

    register_composites(conn)

### Usage

    from collections import OrderedDict

    import sqlalchemy as sa
    from sqlalchemy_utils import CompositeType, CurrencyType

    class Account(Base):
        __tablename__ = 'account'
        id = sa.Column(sa.Integer, primary_key=True)
        balance = sa.Column(
            CompositeType(
                'money_type',
                [
                    sa.Column('currency', CurrencyType),
                    sa.Column('amount', sa.Integer)
                ]
            )
        )

#### Creation

When creating CompositeType, you can either pass in a tuple or a dictionary.

::

  account1 = Account() account1.balance = (‘USD’, 15)

  account2 = Account() account2.balance = {‘currency’: ‘USD’, ‘amount’: 15}

  session.add(account1) session.add(account2) session.commit()

### Accessing fields

CompositeType provides attribute access to underlying fields. In the following example we find all accounts with balance amount more than 5000.

    session.query(Account).filter(Account.balance.amount > 5000)

### Arrays of composites

    from sqlalchemy.dialects.postgresql import ARRAY

    class Account(Base):
        __tablename__ = 'account'
        id = sa.Column(sa.Integer, primary_key=True)
        balances = sa.Column(
            ARRAY(
                CompositeType(
                    'money_type',
                    [
                        sa.Column('currency', CurrencyType),
                        sa.Column('amount', sa.Integer)
                    ]
                ),
                dimensions=1
            )
        )

Related links:

https://schinckel.net/2014/09/24/using-postgres-composite-types-in-django/

class sqlalchemy_utils.types.pg_composite.CompositeType(name, columns, quote=None, **kwargs)

  Represents a PostgreSQL composite type.

  Parameters:

    - name – Name of the composite type.

    - columns – List of columns that this composite type consists of

## CountryType

class sqlalchemy_utils.types.country.CountryType(*args: Any, **kwargs: Any)

  Changes `Country` objects to a string representation on the way in and changes them back to :class:`.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')

class sqlalchemy_utils.primitives.country.Country(code_or_country)

  Country class wraps a 2 to 3 letter country code. It provides various convenience properties and methods.

      from babel import Locale
      from sqlalchemy_utils import Country, i18n

      # First lets add a locale getter for testing purposes
      i18n.get_locale = lambda: Locale('en')

      Country('FI').name  # Finland
      Country('FI').code  # FI

      Country(Country('FI')).code  # 'FI'

  Country always validates the given code if you use at least the optional dependency list ‘babel’, otherwise no validation are performed.

      Country(None)  # raises TypeError

      Country('UnknownCode')  # raises ValueError

  Country supports equality operators.

      Country('FI') == Country('FI')
      Country('FI') != Country('US')

  Country objects are hashable.

      assert hash(Country('FI')) == hash('FI')

## CurrencyType

class sqlalchemy_utils.types.currency.CurrencyType(*args, **kwargs)

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

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

      from sqlalchemy_utils import CurrencyType, Currency

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

      user = User()
      user.currency = Currency('USD')
      session.add(user)
      session.commit()

      user.currency  # Currency('USD')
      user.currency.name  # US Dollar

      str(user.currency)  # US Dollar
      user.currency.symbol  # $

  CurrencyType is scalar coercible:

      user.currency = 'US'
      user.currency  # Currency('US')

class sqlalchemy_utils.primitives.currency.Currency(code)

  Currency class wraps a 3-letter currency code. It provides various convenience properties and methods.

      from babel import Locale
      from sqlalchemy_utils import Currency, i18n

      # First lets add a locale getter for testing purposes
      i18n.get_locale = lambda: Locale('en')

      Currency('USD').name  # US Dollar
      Currency('USD').symbol  # $

      Currency(Currency('USD')).code  # 'USD'

  Currency always validates the given code if you use at least the optional dependency list ‘babel’, otherwise no validation are performed.

      Currency(None)  # raises TypeError

      Currency('UnknownCode')  # raises ValueError

  Currency supports equality operators.

      Currency('USD') == Currency('USD')
      Currency('USD') != Currency('EUR')

  Currencies are hashable.

      len(set([Currency('USD'), Currency('USD')]))  # 1

## EmailType

class sqlalchemy_utils.types.email.EmailType(length=255, *args, **kwargs)

  Provides a way for storing emails in a lower case.

  Example:

      from sqlalchemy_utils import EmailType

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

      user = User()
      user.email = 'John.Smith@foo.com'
      user.name = 'John Smith'
      session.add(user)
      session.commit()
      # Notice - email in filter() is lowercase.
      user = (session.query(User)
                     .filter(User.email == 'john.smith@foo.com')
                     .one())
      assert user.name == 'John Smith'

## EncryptedType

Deprecated since version 0.36.6: Use StringEncryptedType instead

class sqlalchemy_utils.types.encrypted.encrypted_type.EncryptedType(*args, **kwargs)

  The ‘EncryptedType’ class will change implementation from ‘LargeBinary’ to ‘String’ in a future version. Use ‘StringEncryptedType’ to use the ‘String’ implementation.

## JSONType

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

  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

  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', territory='DE')

## LtreeType

class sqlalchemy_utils.types.ltree.LtreeType

  Postgresql LtreeType type.

  The LtreeType datatype can be used for representing labels of data stored in hierarchical tree-like structure. For more detailed information please refer to https://www.postgresql.org/docs/current/ltree.html

      from sqlalchemy_utils import LtreeType, Ltree

      class DocumentSection(Base):
          __tablename__ = 'document_section'
          id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
          path = sa.Column(LtreeType)

      section = DocumentSection(path=Ltree('Countries.Finland'))
      session.add(section)
      session.commit()

      section.path  # Ltree('Countries.Finland')

  Note

  Using `LtreeType`, `LQUERY` and `LTXTQUERY` types may require installation of Postgresql ltree extension on the server side. Please visit https://www.postgresql.org/ for details.

class sqlalchemy_utils.primitives.ltree.Ltree(path_or_ltree)

  Ltree class wraps a valid string label path. It provides various convenience properties and methods.

      from sqlalchemy_utils import Ltree

      Ltree('1.2.3').path  # '1.2.3'

  Ltree always validates the given path.

      Ltree(None)  # raises TypeError

      Ltree('..')  # raises ValueError

  Validator is also available as class method.

      Ltree.validate('1.2.3')
      Ltree.validate(None)  # raises TypeError

  Ltree supports equality operators.

      Ltree('Countries.Finland') == Ltree('Countries.Finland')
      Ltree('Countries.Germany') != Ltree('Countries.Finland')

  Ltree objects are hashable.

      assert hash(Ltree('Finland')) == hash('Finland')

  Ltree objects have length.

      assert len(Ltree('1.2')) == 2
      assert len(Ltree('some.one.some.where'))  # 4

  You can easily find subpath indexes.

      assert Ltree('1.2.3').index('2.3') == 1
      assert Ltree('1.2.3.4.5').index('3.4') == 2

  Ltree objects can be sliced.

      assert Ltree('1.2.3')[0:2] == Ltree('1.2')
      assert Ltree('1.2.3')[1:] == Ltree('2.3')

  Finding longest common ancestor.

      assert Ltree('1.2.3.4.5').lca('1.2.3', '1.2.3.4', '1.2.3') == '1.2'
      assert Ltree('1.2.3.4.5').lca('1.2', '1.2.3') == '1'

  Ltree objects can be concatenated.

      assert Ltree('1.2') + Ltree('1.2') == Ltree('1.2.1.2')

## IPAddressType

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

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

      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)

  PasswordType hashes passwords as they come into the database and allows verifying them using a Pythonic interface. This Pythonic interface relies on setting up automatic data type coercion using the `force_auto_coercion()` function.

  All keyword arguments (aside from max_length) are forwarded to the construction of a passlib.context.LazyCryptContext object, which also supports deferred configuration via the onload callback.

  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

  Lazy configuration of the type with Flask config:

      import flask
      from sqlalchemy_utils import PasswordType, force_auto_coercion

      force_auto_coercion()

      class User(db.Model):
          __tablename__ = 'user'

          password = db.Column(
              PasswordType(
                  # The returned dictionary is forwarded to the CryptContext
                  onload=lambda **kwargs: dict(
                      schemes=flask.current_app.config['PASSWORD_SCHEMES'],
                      **kwargs
                  ),
              ),
              unique=False,
              nullable=False,
          )

## PhoneNumberType

Note

The phonenumbers package must be installed to use PhoneNumber types.

class sqlalchemy_utils.types.phone_number.PhoneNumber(raw_number, region=None, check_region=True)

  Extends a PhoneNumber class from Python phonenumbers library. Adds different phone number formats to attributes, so they can be easily used in templates. Phone number validation method is also implemented.

  Takes the raw phone number and country code as params and parses them into a PhoneNumber object.

      from sqlalchemy_utils import PhoneNumber

      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(sa.Unicode(20))
          country_code = sa.Column(sa.Unicode(8))

          phone_number = sa.orm.composite(
              PhoneNumber,
              _phone_number,
              country_code
          )

      user = User(phone_number=PhoneNumber('0401234567', 'FI'))

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

  Parameters:

    - raw_number – String representation of the phone number.

    - region – Region of the phone number.

    - check_region – Whether to check the supplied region parameter; should always be True for external callers. Can be useful for short codes or toll free

class sqlalchemy_utils.types.phone_number.PhoneNumberType(region='US', max_length=20, *args, **kwargs)

  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  # '+358401234567'
      user.phone_number.international  # '+358 40 1234567'
      user.phone_number.national  # '040 1234567'

## ScalarListType

class sqlalchemy_utils.types.scalar_list.ScalarListType(coerce_func=<class 'str'>, separator=', ')

  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 = ['football', '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()

  ScalarListType is always stored as text. To use an array field on PostgreSQL database use variant construct:

      from sqlalchemy_utils import ScalarListType

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

## StringEncryptedType

class sqlalchemy_utils.types.encrypted.encrypted_type.StringEncryptedType(type_in=None, key=None, engine=None, padding=None, **kwargs)

  StringEncryptedType 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.

  StringEncryptedType needs Cryptography library in order to work.

  When declaring a column which will be of type StringEncryptedType it is better to be as precise as possible and follow the pattern below.

      a_column = sa.Column(StringEncryptedType(sa.Unicode,
                                         secret_key,
                                         FernetEngine))

      another_column = sa.Column(StringEncryptedType(sa.Unicode,
                                         secret_key,
                                         AesEngine,
                                         'pkcs5'))

  A more complete example is given below.

      import sqlalchemy as sa
      from sqlalchemy import create_engine
      try:
          from sqlalchemy.orm import declarative_base
      except ImportError:
          # sqlalchemy 1.3
          from sqlalchemy.ext.declarative import declarative_base
      from sqlalchemy.orm import sessionmaker

      from sqlalchemy_utils import StringEncryptedType
      from sqlalchemy_utils.types.encrypted.encrypted_type import AesEngine

      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(StringEncryptedType(sa.Unicode,
                                             secret_key,
                                             AesEngine,
                                             'pkcs5'))
          access_token = sa.Column(StringEncryptedType(sa.String,
                                                 secret_key,
                                                 AesEngine,
                                                 'pkcs5'))
          is_active = sa.Column(StringEncryptedType(sa.Boolean,
                                              secret_key,
                                              AesEngine,
                                              'zeroes'))
          number_of_accounts = sa.Column(StringEncryptedType(sa.Integer,
                                                       secret_key,
                                                       AesEngine,
                                                       'oneandzeroes'))

      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 = 'secret_user'
      test_token = 'atesttoken'
      active = True
      num_of_accounts = 2

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

      user_id = user.id

      session.expunge_all()

      user_instance = session.query(User).get(user_id)

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

      # 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 being 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(StringEncryptedType(
              sa.Unicode, get_key))

## TimezoneType

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

  TimezoneType provides a way for saving timezones 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. Other values:
          # 'dateutil' (default), and 'zoneinfo'.
          timezone = sa.Column(TimezoneType(backend='pytz'))

  Parameters:

    backend – Whether to use ‘dateutil’, ‘pytz’ or ‘zoneinfo’ for timezones. ‘zoneinfo’ uses the standard library module in Python 3.9+, but requires the external ‘backports.zoneinfo’ package for older Python versions.

## TSVectorType

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

  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.simple', 'finland')

## URLType

class sqlalchemy_utils.types.url.URLType(*args: Any, **kwargs: Any)

  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='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)

  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,
              default=uuid.uuid4
          )

## WeekDaysType

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

  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