sqlalchemy.ext.indexable

Synopsis

Given Person as a model with a primary key and JSON data field. While this field may have any number of elements encoded within it, we would like to refer to the element called name individually as a dedicated attribute which behaves like a standalone column:

from sqlalchemy import Column, JSON, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.indexable import index_property

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    data = Column(JSON)

    name = index_property('data', 'name')

Above, the name attribute now behaves like a mapped column. We can compose a new Person and set the value of name:

>>> person = Person(name='Alchemist')

The value is now accessible:

>>> person.name
'Alchemist'

Behind the scenes, the JSON field was initialized to a new blank dictionary and the field was set:

>>> person.data
{"name": "Alchemist'}

The field is mutable in place:

>>> person.name = 'Renamed'
>>> person.name
'Renamed'
>>> person.data
{'name': 'Renamed'}

When using .index_property, the change that we make to the indexable structure is also automatically tracked as history; we no longer need to use ~.mutable.MutableDict in order to track this change for the unit of work.

Deletions work normally as well:

>>> del person.name
>>> person.data
{}

Above, deletion of person.name deletes the value from the dictionary, but not the dictionary itself.

A missing key will produce AttributeError:

>>> person = Person()
>>> person.name
...
AttributeError: 'name'

Unless you set a default value:

>>> class Person(Base):
>>>     __tablename__ = 'person'
>>>
>>>     id = Column(Integer, primary_key=True)
>>>     data = Column(JSON)
>>>
>>>     name = index_property('data', 'name', default=None)  # See default

>>> person = Person()
>>> print(person.name)
None

The attributes are also accessible at the class level. Below, we illustrate Person.name used to generate an indexed SQL criteria:

>>> from sqlalchemy.orm import Session
>>> session = Session()
>>> query = session.query(Person).filter(Person.name == 'Alchemist')

The above query is equivalent to:

>>> query = session.query(Person).filter(Person.data['name'] == 'Alchemist')

Multiple .index_property objects can be chained to produce multiple levels of indexing:

from sqlalchemy import Column, JSON, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.indexable import index_property

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    data = Column(JSON)

    birthday = index_property('data', 'birthday')
    year = index_property('birthday', 'year')
    month = index_property('birthday', 'month')
    day = index_property('birthday', 'day')

Above, a query such as:

q = session.query(Person).filter(Person.year == '1980')

On a PostgreSQL backend, the above query will render as:

SELECT person.id, person.data
FROM person
WHERE person.data -> %(data_1)s -> %(param_1)s = %(param_2)s

Default Values

.index_property includes special behaviors for when the indexed data structure does not exist, and a set operation is called:

• For an .index_property that is given an integer index value, the default data structure will be a Python list of None values, at least as long as the index value; the value is then set at its place in the list. This means for an index value of zero, the list will be initialized to [None] before setting the given value, and for an index value of five, the list will be initialized to [None, None, None, None, None] before setting the fifth element to the given value. Note that an existing list is not extended in place to receive a value.
• for an .index_property that is given any other kind of index value (e.g. strings usually), a Python dictionary is used as the default data structure.
• The default data structure can be set to any Python callable using the :paramref:`.index_property.datatype` parameter, overriding the previous rules.

Subclassing

.index_property can be subclassed, in particular for the common use case of providing coercion of values or SQL expressions as they are accessed. Below is a common recipe for use with a PostgreSQL JSON type, where we want to also include automatic casting plus astext():

class pg_json_property(index_property):
    def __init__(self, attr_name, index, cast_type):
        super(pg_json_property, self).__init__(attr_name, index)
        self.cast_type = cast_type

    def expr(self, model):
        expr = super(pg_json_property, self).expr(model)
        return expr.astext.cast(self.cast_type)

The above subclass can be used with the PostgreSQL-specific version of _postgresql.JSON:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSON

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, primary_key=True)
    data = Column(JSON)

    age = pg_json_property('data', 'age', Integer)

The age attribute at the instance level works as before; however when rendering SQL, PostgreSQL's ->> operator will be used for indexed access, instead of the usual index operator of ->:

>>> query = session.query(Person).filter(Person.age < 20)

The above query will render:

SELECT person.id, person.data
FROM person
WHERE CAST(person.data ->> %(data_1)s AS INTEGER) < %(param_1)s