# Range data types

SQLAlchemy-Utils provides wide variety of range data types. All range data types return Interval objects of intervals package. In order to use range data types you need to install intervals with:

    pip install intervals

Intervals package provides good chunk of additional interval operators that for example psycopg2 range objects do not support.

Some good reading for practical interval implementations:

http://wiki.postgresql.org/images/f/f0/Range-types.pdf

## Range type initialization

    from sqlalchemy_utils import IntRangeType

    class Event(Base):
        __tablename__ = 'user'
        id = sa.Column(sa.Integer, autoincrement=True)
        name = sa.Column(sa.Unicode(255))
        estimated_number_of_persons = sa.Column(IntRangeType)

You can also set a step parameter for range type. The values that are not multipliers of given step will be rounded up to nearest step multiplier.

    from sqlalchemy_utils import IntRangeType

    class Event(Base):
        __tablename__ = 'user'
        id = sa.Column(sa.Integer, autoincrement=True)
        name = sa.Column(sa.Unicode(255))
        estimated_number_of_persons = sa.Column(IntRangeType(step=1000))

    event = Event(estimated_number_of_persons=[100, 1200])
    event.estimated_number_of_persons.lower  # 0
    event.estimated_number_of_persons.upper  # 1000

## Range type operators

SQLAlchemy-Utils supports many range type operators. These operators follow the intervals package interval coercion rules.

So for example when we make a query such as:

    session.query(Car).filter(Car.price_range == 300)

It is essentially the same as:

    session.query(Car).filter(Car.price_range == DecimalInterval([300, 300]))

### Comparison operators

All range types support all comparison operators (>, >=, ==, !=, <=, <).

    Car.price_range < [12, 300]

    Car.price_range == [12, 300]

    Car.price_range < 300

    Car.price_range > (300, 500)

    # Whether or not range is strictly left of another range
    Car.price_range << [300, 500]

    # Whether or not range is strictly right of another range
    Car.price_range >> [300, 500]

### Membership operators

    Car.price_range.contains([300, 500])

    Car.price_range.contained_by([300, 500])

    Car.price_range.in_([[300, 500], [800, 900]])

    ~ Car.price_range.in_([[300, 400], [700, 800]])

### Length

SQLAlchemy-Utils provides length property for all range types. The implementation of this property varies on different range types.

In the following example we find all cars whose price range’s length is more than 500.

    session.query(Car).filter(
        Car.price_range.length > 500
    )

## DateRangeType

class `sqlalchemy_utils.types.range.``DateRangeType`(*args, **kwargs)

  DateRangeType provides way for saving ranges of dates into database. On PostgreSQL this type maps to native DATERANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import DateRangeType

      class Reservation(Base):
          __tablename__ = 'user'
          id = sa.Column(sa.Integer, autoincrement=True)
          room_id = sa.Column(sa.Integer))
          during = sa.Column(DateRangeType)

## DateTimeRangeType

class `sqlalchemy_utils.types.range.``DateTimeRangeType`(*args, **kwargs)

## IntRangeType

class `sqlalchemy_utils.types.range.``IntRangeType`(*args, **kwargs)

  IntRangeType provides way for saving ranges of integers into database. On PostgreSQL this type maps to native INT4RANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import IntRangeType

      class Event(Base):
          __tablename__ = 'user'
          id = sa.Column(sa.Integer, autoincrement=True)
          name = sa.Column(sa.Unicode(255))
          estimated_number_of_persons = sa.Column(IntRangeType)

      party = Event(name=u'party')

      # we estimate the party to contain minium of 10 persons and at max
      # 100 persons
      party.estimated_number_of_persons = [10, 100]

      print party.estimated_number_of_persons
      # '10-100'

  IntRangeType returns the values as IntInterval objects. These objects support many arithmetic operators:

      meeting = Event(name=u'meeting')

      meeting.estimated_number_of_persons = [20, 40]

      total = (
          meeting.estimated_number_of_persons +
          party.estimated_number_of_persons
      )
      print total
      # '30-140'

## NumericRangeType

class `sqlalchemy_utils.types.range.``NumericRangeType`(*args, **kwargs)

  NumericRangeType provides way for saving ranges of decimals into database. On PostgreSQL this type maps to native NUMRANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import NumericRangeType

      class Car(Base):
          __tablename__ = 'car'
          id = sa.Column(sa.Integer, autoincrement=True)
          name = sa.Column(sa.Unicode(255)))
          price_range = sa.Column(NumericRangeType)

## RangeComparator

class `sqlalchemy_utils.types.range.``RangeComparator`(expr)
  `contains`(other, **kwargs)

    Implement the ‘contains’ operator.

    Produces a LIKE expression that tests against a match for the middle of a string value:

        column LIKE '%' || <other> || '%'

    E.g.:

        stmt = select([sometable]).\
            where(sometable.c.column.contains("foobar"))

    Since the operator uses `LIKE`, wildcard characters `"%"` and `"_"` that are present inside the <other> expression will behave like wildcards as well. For literal string values, the :paramref:`.ColumnOperators.contains.autoescape` flag may be set to `True` to apply escaping to occurrences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the :paramref:`.ColumnOperators.contains.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

    Parameters:

    - other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters `%` and `_` are not escaped by default unless the :paramref:`.ColumnOperators.contains.autoescape` flag is set to True.
    - autoescape –

      boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of `"%"`, `"_"` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression.

      An expression such as:

          somecolumn.contains("foo%bar", autoescape=True)

      Will render as:

          somecolumn LIKE '%' || :param || '%' ESCAPE '/'

      With the value of :param as `"foo/%bar"`.

      New in version 1.2.

      Changed in version 1.2.0: The :paramref:`.ColumnOperators.contains.autoescape` parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the :paramref:`.ColumnOperators.contains.escape` parameter.

    - escape –

      a character which when given will render with the `ESCAPE` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of `%` and `_` to allow them to act as themselves and not wildcard characters.

      An expression such as:

          somecolumn.contains("foo/%bar", escape="^")

      Will render as:

          somecolumn LIKE '%' || :param || '%' ESCAPE '^'

      The parameter may also be combined with :paramref:`.ColumnOperators.contains.autoescape`:

          somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)

      Where above, the given literal parameter will be converted to `"foo^%bar^^bat"` before being passed to the database.

    See also

    `ColumnOperators.startswith()`

    `ColumnOperators.endswith()`

    `ColumnOperators.like()`

  `in_`(other)

    Implement the `in` operator.

    In a column context, produces the clause `column IN <other>`.

    The given parameter `other` may be:

    - A list of literal values, e.g.:

          stmt.where(column.in_([1, 2, 3]))

      In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:

          WHERE COL IN (?, ?, ?)

    - A list of tuples may be provided if the comparison is against a `tuple_()` containing multiple expressions:

          from sqlalchemy import tuple_
          stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)]))

    - An empty list, e.g.:

          stmt.where(column.in_([]))

      In this calling form, the expression renders a “false” expression, e.g.:

          WHERE 1 != 1

      This “false” expression has historically had different behaviors in older SQLAlchemy versions, see :paramref:`.create_engine.empty_in_strategy` for behavioral options.

      Changed in version 1.2: simplified the behavior of “empty in” expressions

    - A bound parameter, e.g. `bindparam()`, may be used if it includes the :paramref:`.bindparam.expanding` flag:

          stmt.where(column.in_(bindparam('value', expanding=True)))

      In this calling form, the expression renders a special non-SQL placeholder expression that looks like:

          WHERE COL IN ([EXPANDING_value])

      This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:

          connection.execute(stmt, {"value": [1, 2, 3]})

      The database would be passed a bound parameter for each value:

          WHERE COL IN (?, ?, ?)

      New in version 1.2: added “expanding” bound parameters

      If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:

          WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)

      New in version 1.3: “expanding” bound parameters now support empty lists

    - a `select()` construct, which is usually a correlated scalar select:

          stmt.where(
              column.in_(
                  select([othertable.c.y]).
                  where(table.c.x == othertable.c.x)
              )
          )

      In this calling form, `ColumnOperators.in_()` renders as given:

          WHERE COL IN (SELECT othertable.y
          FROM othertable WHERE othertable.x = table.x)

    Parameters: | other – a list of literals, a `select()` construct, or a `bindparam()` construct that includes the :paramref:`.bindparam.expanding` flag set to True.

  `notin_`(other)

    implement the `NOT IN` operator.

    This is equivalent to using negation with `ColumnOperators.in_()`, i.e. `~x.in_(y)`.

    In the case that `other` is an empty sequence, the compiler produces an “empty not in” expression. This defaults to the expression “1 = 1” to produce true in all cases. The :paramref:`.create_engine.empty_in_strategy` may be used to alter this behavior.

    Changed in version 1.2: The `ColumnOperators.in_()` and `ColumnOperators.notin_()` operators now produce a “static” expression for an empty IN sequence by default.

    See also

    `ColumnOperators.in_()`