sqlalchemy.dialects.postgresql.hstore.HSTORE

Represent the PostgreSQL HSTORE type.

The .HSTORE type stores dictionaries containing strings, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', HSTORE)
)

with engine.connect() as conn:
    conn.execute(
        data_table.insert(),
        data = {"key1": "value1", "key2": "value2"}
    )

.HSTORE provides for a wide range of operations, including:

• Index operations:

  data_table.c.data['some key'] == 'some value'
  

• Containment operations:

  data_table.c.data.has_key('some key')
  
  data_table.c.data.has_all(['one', 'two', 'three'])
  

• Concatenation:

  data_table.c.data + {"k1": "v1"}
  

For a full list of special methods see .HSTORE.comparator_factory.

For usage with the SQLAlchemy ORM, it may be desirable to combine the usage of .HSTORE with .MutableDict dictionary now part of the sqlalchemy.ext.mutable extension. This extension will allow "in-place" changes to the dictionary, e.g. addition of new keys or replacement/removal of existing keys to/from the current dictionary, to produce events which will be detected by the unit of work:

from sqlalchemy.ext.mutable import MutableDict

class MyClass(Base):
    __tablename__ = 'data_table'

    id = Column(Integer, primary_key=True)
    data = Column(MutableDict.as_mutable(HSTORE))

my_object = session.query(MyClass).one()

# in-place mutation, requires Mutable extension
# in order for the ORM to detect
my_object.data['some_key'] = 'some value'

session.commit()

When the sqlalchemy.ext.mutable extension is not used, the ORM will not be alerted to any changes to the contents of an existing dictionary, unless that dictionary value is re-assigned to the HSTORE-attribute itself, thus generating a change event.