Hi there,
Just came back to some code after a bit of a break and can't seem to get my
head around how to correctly use the in_ method with a custom comparator and
also the orm subquery functionality.
See attached file for an outline of my approach.
You will see that I am stuck on how to correctly form the last query:
Roughly: select all entities with property (key='association' and value IN
(subquery))
It looks like I need to define an in_() method on my custom comparator and
then possibly use the orm subquery() method for the er...subquery.
It all feels like it should be straight forward, but the gears just aren't
turning at the moment so any pointers would be greatly appreciated.
ta,
Martin
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
import sys
from datetime import datetime
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.interfaces import *
# Database
engine = create_engine('sqlite:///')
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True, transactional=False, bind=engine))
mapper = Session.mapper
# Class: Entity
#----------------------------------------------
class Entity(object):
def __init__(self, properties=None):
self.properties = []
if properties is not None:
self.properties.extend(properties)
# Table: entities
#----------------------------------------------
entityTable = Table('entities', metadata,
Column('id', Integer, primary_key=True),
Column('_entityType', String(30), nullable=False),
)
# Table: entitiesPolymorphicProperties
#----------------------------------------------
entityPolymorphicPropertyTable = Table('entitiesPolymorphicProperties', metadata,
Column('entity_id', Integer, ForeignKey('entities.id')),
Column('polymorphicProperty_id', Integer, ForeignKey('polymorphicProperties.id')),
)
# Class: PolymorphicProperty
#----------------------------------------------
class PolymorphicProperty(object):
typeMap = {
str: ('text','text'),
int: ('integer','integer'),
float: ('float','float'),
datetime: ('datetime','datetime'),
Entity: ('entity','entity'),
type(None): (None, None)
}
def __init__(self, key=None, value=None):
self.key = key
self.value = value
def _getValue(self):
for discriminator, field in self.typeMap.values():
if self.type == discriminator:
return getattr(self, field)
return None
def _setValue(self, value):
# Check type of value can be handled
pyType = type(value)
if pyType not in self.typeMap:
if isinstance(value, Entity):
pyType = Entity
else:
raise TypeError(pyType)
# Set real values and current type of property
# Resets all real values no longer matching type to None
for fieldType in self.typeMap:
discriminator, field = self.typeMap[fieldType]
fieldValue = None
if pyType == fieldType:
self.type = discriminator
fieldValue = value
if field is not None:
setattr(self, field, fieldValue)
def _delValue(self, value):
self._set_value(None)
value = property(_getValue, _setValue, _delValue, doc='''Logical value of this property''')
# Class: Comparator
#----------------------------------------------
class Comparator(PropComparator):
castableMap = {
int: (Integer, Text, Float),
str: (Text,),
float: (Float, Text),
datetime: (DateTime, Text),
type(None): (),
}
castTypeMap = {
int: Integer,
str: Text,
float: Float,
datetime: DateTime,
}
# Function: _case
# Generate CASE (switch) statement in SQL to pick appropriate field
#----------------------------------------------
def _case(self, castType=Text):
cls = self.prop.parent.class_
whens = []
for fieldType in cls.typeMap:
if fieldType in self.castableMap:
if castType in self.castableMap[fieldType]:
prop = cls.typeMap[fieldType]
if prop[1] is not None:
whens.append(( text("'%s'" % prop[0]), cast(getattr(cls, prop[1]), castType) ))
return case(whens, cls.type, null())
# Function: __eq__
# Equality comparison operator.
#----------------------------------------------
def __eq__(self, other):
if isinstance(other, Entity):
return self.prop.parent.class_.entity == other
return self._case() == cast(other, Text)
# Function: __ne__
# In-equality comparison operator.
#----------------------------------------------
def __ne__(self, other):
if isinstance(other, Entity):
return self.prop.parent.class_.entity != other
return self._case() != cast(other, Text)
# Table: polymorphicProperties
#----------------------------------------------
polymorphicPropertyTable = Table('polymorphicProperties', metadata,
Column('id', Integer, primary_key=True),
Column('key', String(20), nullable=False, default='keyword'),
Column('type', String(15), default=None),
Column('text', Text, default=None),
Column('integer', Integer, default=None),
Column('float', Float, default=None),
Column('datetime', DateTime, default=None),
Column('entity_id', Integer, ForeignKey('entities.id')),
)
# Mapping: PolymorphicProperty
#----------------------------------------------
mapper(PolymorphicProperty,
polymorphicPropertyTable,
properties={
'value': comparable_property(PolymorphicProperty.Comparator, PolymorphicProperty.value),
'entity': relation(Entity),
}
)
# Mapping: Entity
#----------------------------------------------
mapper(Entity, entityTable, polymorphic_on=entityTable.c._entityType, polymorphic_identity='Entity',
properties={
'properties': relation(PolymorphicProperty, secondary=entityPolymorphicPropertyTable, backref='entities'),
}
)
metadata.create_all(engine)
# Main
#----------------------------------------------
def main():
session = Session()
session.begin()
prop1 = PolymorphicProperty(key='username', value='bob')
entity1 = Entity(properties=[prop1])
prop2 = PolymorphicProperty(key='resolution', value=5)
prop3 = PolymorphicProperty(key='association', value=entity1)
entity2 = Entity(properties=[prop2, prop3])
session.commit()
# Entities with property (key=username, value='bob')
print session.query(Entity).filter(Entity.properties.any(key='username', value='bob')).all()
# Entities with property (key=resolution, value=5)
print session.query(Entity).filter(Entity.properties.any(key='resolution', value=5)).all()
# Entities with property (key=association, value=entity1)
print session.query(Entity).filter(Entity.properties.any(key='association', value=entity1)).all()
# Entities with property (key=association, value IN [subquery(Entity).filter(Entity.properties.any(key='username', value='bob'))])
entities = session.query(Entity).filter(Entity.properties.any(PolymorphicProperty.key=='asset',
PolymorphicProperty.value.in_(session.query(Entity).filter(Entity.properties.any(key='username', value='bob'))))).all()
Session.remove()
return 0
# Driver
#----------------------------------------------
if __name__ == '__main__':
sys.exit(main())
