OK, just three tables, then just three mapped classes, that's it. The rest of
the stuff you're trying to do has to be done using standard Python techniques;
classes that implement special __getitem__() / __setitem__(), etc. Here's a
start, does just the "get" side so far:
from sqlalchemy import Integer, ForeignKey, create_engine, String, Column
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Group(Base):
__tablename__ = 'group'
id = Column(Integer, primary_key=True)
name = Column(String)
class GroupGetter(object):
def __init__(self, entity):
self.entity = entity
def __getitem__(self, key):
# TODO: probably want to cache this
return GroupItemGetter(self, key)
class GroupItemGetter(object):
def __init__(self, groupgetter, groupname):
self.entity = groupgetter.entity
self.groupname = groupname
self.data = dict(
(group_entity.key, group_entity.value)
for group_entity in self.entity._group_entities
if group_entity.group.name == self.groupname
)
def __getitem__(self, key):
return self.data[key]
def __setitem__(self, key, value):
raise NotImplementedError("an exercise")
class Entity(Base):
__tablename__ = 'entity'
id = Column(Integer, primary_key=True)
name = Column(String)
_group_entities = relationship("GroupEntity")
@property
def groups(self):
# TODO: probably want to cache this
return GroupGetter(self)
class GroupEntity(Base):
__tablename__ = 'group_entity'
id = Column(Integer, primary_key=True)
entity_id = Column(ForeignKey('entity.id'))
group_id = Column(ForeignKey('group.id'))
key = Column(String)
value = Column(String)
group = relationship("Group")
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
ent = Entity(name="some customer")
g1, g2 = Group(name='g1'), Group(name='g2')
# setter side is raw so far
ent._group_entities.append(GroupEntity(group=g1, key='color', value='red'))
ent._group_entities.append(GroupEntity(group=g2, key='color', value='polkadot'))
sess.add(ent)
sess.commit()
# but here's the getter side
print ent.groups['g1']['color']
On Sep 10, 2014, at 8:08 PM, Anton <anschat...@gmail.com> wrote:
> I already have models for group, entity and group_entity. Now I am trying to
> build a model that would represent an entity for a group with all the custom
> attributes.
>
> On Wednesday, September 10, 2014 5:06:36 PM UTC-7, Anton wrote:
> Mike, the tables are already there, I am trying to create models mapped on
> the tables so it is easier to work with.
> Tables design looks like:
>
> CREATE TABLE group (
> id integer NOT NULL,
> name character varying
> );
>
>
> CREATE TABLE entity (
> id integer NOT NULL,
> name character varying
> );
>
> CREATE TABLE group_entity (
> id integer NOT NULL,
> entity_id integer,
> key character varying,
> value text,
> group_id integer NOT NULL
> );
>
> Best,
> Anton.
>
>
>
>
>
> On Wednesday, September 10, 2014 4:51:54 PM UTC-7, Michael Bayer wrote:
> hi Anton -
>
> if you can show me correct table designs, e.g. as simple CREATE TABLE
> statements, that definitely do the thing you need, I can show you how to map
> to it. So far it's not clear if you're looking for schema design via
> SQLAlchemy's API.
>
> - mike
>
>
> On Sep 10, 2014, at 6:36 PM, Anton <ansch...@gmail.com> wrote:
>
>> Hi Mike,
>>
>> I have two models that I did not provide in the example: Entity and Group.
>> Every group can assign some customer attributes to an entity, this is
>> reflected in GroupEntityAttributes.
>> Let's say GroupEntityAttributes(group_id=1, entity_id=1, key="color",
>> value="red") would mean that Group 1 assigned a customer attribute "color"
>> with a value "red" to
>> the entity with id 1. So when I want to load an entity for a group I want to
>> load the entity object plus all the custom attributes assigned to the entity
>> but the group.
>> Also I need some mechanism mark an entity as loadable for the group, for
>> this I am using a record like
>> GroupEntityAttributes(group_id=1, entity_id=1, key="_has_connection",
>> value=Null), so key "_has_connection" has a special meaning. If it presents,
>> then the entity should be loaded for the group with all other custom
>> attributes, otherwise no. For this purpose I created a model GroupEntity.
>>
>> So this way GroupEntity.query.filter_by(group_id=1).all() will give a list
>> with all entities loadable fir the Group 1. Every item of the list will have
>> an association to Group object and Entity object, I want others property to
>> represent a list of customer attributes, that I would like to implement as
>> described here
>> (https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master).
>> For this I need to have a self referencing relationship to
>> GroupEntityAttributes, which appears to be self-referencing because two
>> models use the same table.
>>
>> SimpleModel is just a mixin that I use in every model I create to make sure
>> that they all have a unique id.
>>
>> Thanks,
>> Anton.
>>
>>
>> On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote:
>>
>> On Sep 10, 2014, at 5:11 PM, Anton <ansch...@gmail.com> wrote:
>>
>>> So I am trying to do the following:
>>> class SimpleModel(object):
>>> __table_args__ = {'extend_existing': True}
>>>
>>> id = db.Column(db.Integer, primary_key=True)
>>>
>>> class GroupEntityAttributes(SimpleModel, db.Model):
>>> __table_args__ = {'extend_existing': True}
>>> __tablename__ = 'group_entity'
>>>
>>> #Table Columns
>>> id = db.Column(db.Integer, primary_key=True)
>>> group_id = db.Column(db.Integer, db.ForeignKey('groups.id'),
>>> nullable=False)
>>> entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'),
>>> nullable=False)
>>> key = db.Column(db.String)
>>> value = db.Column(db.Text)
>>>
>>> #Relationships
>>> entity = db.relationship('Entity', lazy='joined')
>>> group = db.relationship('Group')
>>>
>>> class GroupEntityAttributesPoly(GroupEntityAttributes):
>>> __mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key}
>>>
>>> class GroupEntity(GroupEntityAttributesPoly):
>>> __mapper_args__ = {'polymorphic_identity': '_has_connection'}
>>>
>>> others = db.relationship(
>>> 'GroupEntityAttributes',
>>> primaryjoin=and_(
>>> remote(foreign(GroupEntityAttributes.group_id)) ==
>>> GroupEntityAttributes.group_id,
>>> remote(foreign(GroupEntityAttributes.entity_id)) ==
>>> GroupEntityAttributes.entity_id,
>>> )
>>> )
>>>
>>>
>>
>> What's illustrated here doesn't really make sense. If
>> GroupEntityAttributes.group_id referred to "groups.id", then a relationship
>> extending from GroupEntity would need to refer to whatever class is mapped
>> to the "entities" table, which I don't see here. It looks like you're
>> taking foreign key columns on the "group_entity" table and somehow pointing
>> it to itself in that primaryjoin which doesn't make sense. GroupEntity
>> would need to be mapped to either "entities" or "groups" in order to refer
>> to GroupEntityAttributes using that pattern and this sort of looks like the
>> association object pattern, see
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
>> for an example of what that looks like.
>>
>> Other things that you probably don't want:
>>
>> 1. polymorphic_on on a subclass, that should always go on the mapped class
>> at the bottom of the hierarchy, see
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#joined-table-inheritance
>> for basic examples
>>
>> 2. extend_existing, this is usually only relevant when you're using
>> reflection, otherwise it probably means you're setting things up incorrectly
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> When I try to load the model GroupEntity I get an error:
>>> ArgumentError: Can't determine relationship direction for relationship
>>> 'GroupEntity.others' - foreign key columns within the join condition are
>>> present in both the parent and the child's mapped tables. Ensure that only
>>> those columns referring to a parent column are marked as foreign, either
>>> via the foreign() annotation or via the foreign_keys argument.
>>>
>>> I am not sure how to overcome this error. Would appreciate any help.
>>>
>>> Best,
>>> Anton.
>>>
>>> On Tuesday, September 9, 2014 1:10:40 PM UTC-7, Anton wrote:
>>> Trying to use with multiple table mapping.
>>>
>>> On Tuesday, September 9, 2014 1:03:25 PM UTC-7, Anton wrote:
>>> Hi,
>>>
>>> I need some help choosing the right pattern for the models I have. I have
>>> the following tables:
>>> Entity (INT id, VARCHAR name)
>>> Group (INT id, VARCHAT name)
>>> GroupEntityAttributes(INT entity_id, INT group_id, VARCHAR key, VARCHAR
>>> name)
>>>
>>> Entity and Group models are pretty straight forward. But
>>> GroupEntityAttributes represents Entity within some group with custom
>>> attributes added, so it resembles this pattern (
>>> https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master
>>> ), the only difference is that there is an additional foreign key.
>>> I wonder if there is any approach I can use to adapt vertical pattern for
>>> my needs without modifying tables.
>>>
>>> Best,
>>> Anton.
>>>
>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
