Indexable

“index” means the attribute is associated with an element of an Indexable column with the predefined index to access it. The types include types such as ARRAY, and HSTORE.

The extension provides Column-like interface for any element of an typed column. In simple cases, it can be treated as a Column - mapped attribute.

New in version 1.1.

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:

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 , the change that we make to the indexable structure is also automatically tracked as history; we no longer need to use MutableDict in order to track this change for the unit of work.

Deletions work normally as well:

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

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

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

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

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

For an 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 parameter, overriding the previous rules.

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
        expr = super(pg_json_property, self).expr(model)

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

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 ->:

The above query will render:

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

class sqlalchemy.ext.indexable.``index_property(attr_name, index, default=<object object>, datatype=None, mutable=True, onebased=True)

New in version 1.1.