Indexable

Indexable

Define attributes on ORM-mapped classes that have “index” attributes forcolumns with Indexable types.

“index” means the attribute is associated with an element of anIndexable column with the predefined index to access it.The Indexable types include types such asARRAY, JSON andHSTORE.

The indexable extension providesColumn-like interface for any element of anIndexable typed column. In simple cases, it can betreated as a Column - mapped attribute.

New in version 1.1.

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 individuallyas 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. Wecan 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 dictionaryand 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 indexablestructure is also automatically tracked as history; we no longer needto use MutableDict in order to track this changefor 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 generatean 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 producemultiple 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 indexeddata 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 itsplace in the list. This means for an index value of zero, the listwill 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 elementto the given value. Note that an existing list is not extendedin place to receive a value.
for an index_property that is given any other kind of indexvalue (e.g. strings usually), a Python dictionary is used as thedefault data structure.
The default data structure can be set to any Python callable using theindex_property.datatype parameter, overriding the previousrules.

Subclassing

index_property can be subclassed, in particular for the commonuse case of providing coercion of values or SQL expressions as they areaccessed. 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-specificversion 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; howeverwhen rendering SQL, PostgreSQL’s ->> operator will be usedfor 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

API Reference

class sqlalchemy.ext.indexable.indexproperty(_attr_name, index, default=