"""Mapping a polymorphic-valued vertical table as a dictionary.
This example illustrates accessing and modifying a "vertical" (or
"properties", or pivoted) table via a dict-like interface. The 'dictlike.py'
example explains the basics of vertical tables and the general approach. This
example adds a twist- the vertical table holds several "value" columns, one
for each type of data that can be stored. For example::
Table('properties', metadata
Column('owner_id', Integer, ForeignKey('owner.id'),
primary_key=True),
Column('key', UnicodeText),
Column('type', Unicode(16)),
Column('int_value', Integer),
Column('char_value', UnicodeText),
Column('bool_value', Boolean),
Column('decimal_value', Numeric(10,2)))
For any given properties row, the value of the 'type' column will point to the
'_value' column active for that row.
This example approach uses exactly the same dict mapping approach as the
'dictlike' example. It only differs in the mapping for vertical rows. Here,
we'll use a Python @property to build a smart '.value' attribute that wraps up
reading and writing those various '_value' columns and keeps the '.type' up to
date.
"""
from sqlalchemy.orm.interfaces import PropComparator
from sqlalchemy.orm import comparable_property
# Using the VerticalPropertyDictMixin from the base example
from dictlike import VerticalPropertyDictMixin
class PolymorphicVerticalProperty(object):
"""A key/value pair with polymorphic value storage.
Supplies a smart 'value' attribute that provides convenient read/write
access to the row's current value without the caller needing to worry
about the 'type' attribute or multiple columns.
The 'value' attribute can also be used for basic comparisons in queries,
allowing the row's logical value to be compared without foreknowledge of
which column it might be in. This is not going to be a very efficient
operation on the database side, but it is possible. If you're mapping to
an existing database and you have some rows with a value of str('1') and
others of int(1), then this could be useful.
Subclasses must provide a 'type_map' class attribute with the following
form::
type_map = {
<python type> : ('type column value', 'column name'),
# ...
}
For example,::
type_map = {
int: ('integer', 'integer_value'),
str: ('varchar', 'varchar_value'),
}
Would indicate that a Python int value should be stored in the
'integer_value' column and the .type set to 'integer'. Conversely, if the
value of '.type' is 'integer, then the 'integer_value' column is consulted
for the current value.
"""
type_map = {
type(None): (None, None),
}
class Comparator(PropComparator):
"""A comparator for .value, builds a polymorphic comparison via CASE.
Optional. If desired, install it as a comparator in the mapping::
mapper(..., properties={
'value': comparable_property(PolymorphicVerticalProperty.Comparator,
PolymorphicVerticalProperty.value)
})
"""
def _case(self):
cls = self.prop.parent.class_
whens = [(text("'%s'" % p[0]), getattr(cls, p[1]))
for p in cls.type_map.values()
if p[1] is not None]
return case(whens, cls.type, null())
def __eq__(self, other):
return cast(self._case(), String) == cast(other, String)
def __ne__(self, other):
return cast(self._case(), String) != cast(other, String)
def __init__(self, key, value=None):
self.key = key
self.value = value
def _get_value(self):
for discriminator, field in self.type_map.values():
if self.type == discriminator:
return getattr(self, field)
return None
def _set_value(self, value):
py_type = type(value)
if py_type not in self.type_map:
raise TypeError(py_type)
for field_type in self.type_map:
discriminator, field = self.type_map[field_type]
field_value = None
if py_type == field_type:
self.type = discriminator
field_value = value
if field is not None:
setattr(self, field, field_value)
def _del_value(self):
self._set_value(None)
value = property(_get_value, _set_value, _del_value, doc=
"""The logical value of this property.""")
def __repr__(self):
return '<%s %r=%r>' % (self.__class__.__name__, self.key, self.value)
if __name__ == '__main__':
from sqlalchemy import (MetaData, Table, Column, Integer, Unicode,
ForeignKey, UnicodeText, and_, not_, or_, String, Boolean, cast, text,
null, case)
from sqlalchemy.orm import mapper, relationship, Session
from sqlalchemy.orm.collections import attribute_mapped_collection
metadata = MetaData()
animals = Table('animal', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)))
chars = Table('facts', metadata,
Column('animal_id', Integer, ForeignKey('animal.id'),
primary_key=True),
Column('key', Unicode(64), primary_key=True),
Column('type', Unicode(16), default=None),
Column('int_value', Integer, default=None),
Column('char_value', UnicodeText, default=None),
Column('boolean_value', Boolean, default=None))
class AnimalFact(PolymorphicVerticalProperty):
type_map = {
int: (u'integer', 'int_value'),
unicode: (u'char', 'char_value'),
bool: (u'boolean', 'boolean_value'),
type(None): (None, None),
}
class Animal(VerticalPropertyDictMixin):
"""An animal.
Animal facts are available via the 'facts' property or by using
dict-like accessors on an Animal instance::
cat['color'] = 'calico'
# or, equivalently:
cat.facts['color'] = AnimalFact('color', 'calico')
"""
_property_type = AnimalFact
_property_mapping = 'facts'
def __init__(self, name):
self.name = name
def __repr__(self):
return '<%s %r>' % (self.__class__.__name__, self.name)
mapper(Animal, animals, properties={
'facts': relationship(
AnimalFact, backref='animal',
collection_class=attribute_mapped_collection('key')),
})
mapper(AnimalFact, chars, properties={
'value': comparable_property(AnimalFact.Comparator, AnimalFact.value)
})
metadata.bind = 'sqlite:///'
metadata.create_all()
session = Session()
stoat = Animal(u'stoat')
stoat[u'color'] = u'red'
stoat[u'cuteness'] = 7
stoat[u'weasel-like'] = True
session.add(stoat)
session.commit()
critter = session.query(Animal).filter(Animal.name == u'stoat').one()
print critter[u'color']
print critter[u'cuteness']
print "changing cuteness value and type:"
critter[u'cuteness'] = u'very cute'
metadata.bind.echo = True
session.commit()
metadata.bind.echo = False
marten = Animal(u'marten')
marten[u'cuteness'] = 5
marten[u'weasel-like'] = True
marten[u'poisonous'] = False
session.add(marten)
shrew = Animal(u'shrew')
shrew[u'cuteness'] = 5
shrew[u'weasel-like'] = False
shrew[u'poisonous'] = True
session.add(shrew)
session.commit()
q = (session.query(Animal).
filter(Animal.facts.any(
and_(AnimalFact.key == u'weasel-like',
AnimalFact.value == True))))
print 'weasel-like animals', q.all()
# Save some typing by wrapping that up in a function:
with_characteristic = lambda key, value: and_(AnimalFact.key == key,
AnimalFact.value == value)
q = (session.query(Animal).
filter(Animal.facts.any(
with_characteristic(u'weasel-like', True))))
print 'weasel-like animals again', q.all()
q = (session.query(Animal).
filter(Animal.facts.any(with_characteristic(u'poisonous', False))))
print 'animals with poisonous=False', q.all()
q = (session.query(Animal).
filter(or_(Animal.facts.any(
with_characteristic(u'poisonous', False)),
not_(Animal.facts.any(AnimalFact.key == u'poisonous')))))
print 'non-poisonous animals', q.all()
q = (session.query(Animal).
filter(Animal.facts.any(AnimalFact.value == 5)))
print 'any animal with a .value of 5', q.all()
# Facts can be queried as well.
q = (session.query(AnimalFact).
filter(with_characteristic(u'cuteness', u'very cute')))
print q.all()
metadata.drop_all()
