Why does SA (only 0.6.x) read data from n:m relationships when updating the parent table?

[Ralph Heinkel]

Hi,

is SA < 0.6 this problem did not occur. The sample code to demonstrate
the problem is taken from http://www.sqlalchemy.org/docs/mappers.html#many-to-many
and repeated here for completeness. First let's setup a sample db (all
done in sqlite in my case):

class Parent(object):
def __init__(self, ID, data):
self.id = ID
self.data = data

class Child(object):
def __init__(self, ID):
self.id = ID

left_table = Table('left', metadata,
Column('id', Integer, primary_key=True),
Column('data', Integer))

right_table = Table('right', metadata,
Column('id', Integer, primary_key=True))

association_table = Table('association', metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id')),
)

mapper(Parent, left_table, properties={
'children': relationship(Child, secondary=association_table,
backref='parents')
})

mapper(Child, right_table)

metadata.create_all(engine)

# create some sample data
p = Parent(1, 100)
session.add(p)
p.children.append(Child(20))
p.children.append(Child(30))
session.commit()

Now let's load the parent obj, modify the 'data' column, and commit
the change. This should not affect the associated tables, however
their data is loaded:

>>> p = session.query(Parent).get(1)
2010-07-07 11:08:42,116 INFO sqlalchemy.engine.base.Engine.0x...360c
BEGIN
2010-07-07 11:08:42,117 INFO sqlalchemy.engine.base.Engine.0x...360c
SELECT "left".id AS left_id, "left".data AS left_data
FROM "left"
WHERE "left".id = ?
2010-07-07 11:08:42,118 INFO sqlalchemy.engine.base.Engine.0x...360c
(1,)
>>> p.data = 101
>>> session.commit()
2010-07-07 11:08:50,020 INFO sqlalchemy.engine.base.Engine.0x...360c
SELECT "right".id AS right_id
FROM "right", association
WHERE ? = association.left_id AND "right".id = association.right_id
2010-07-07 11:08:50,020 INFO sqlalchemy.engine.base.Engine.0x...360c
(1,)
2010-07-07 11:08:50,023 INFO sqlalchemy.engine.base.Engine.0x...360c
UPDATE "left" SET data=? WHERE "left".id = ?
2010-07-07 11:08:50,024 INFO sqlalchemy.engine.base.Engine.0x...360c
(101, 1)
2010-07-07 11:08:50,025 INFO sqlalchemy.engine.base.Engine.0x...360c
COMMIT


After the 'commit' above a select statement is executed on the
association table. This is new behavior in the 0.6.x series on SA,
wasn't there in versions below.. And it is very stupid for us as we
have huge relations on very few parent tables. All of them will be
loaded just because data is changed in the parent.

How is it possible to avoid this?

Any help is appreciated.

Thanks

Ralph

[Michael Bayer]

I so desperately wished you had reported this yesterday before I released. It's an extra load that forgot to get factored out. I may very well release 0.6.3 today as I consider this a very severe issue.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Ralph Heinkel]

oh, I'm sorry, but better now than later ;-) Strange that nobody got
hit by that bug (or the side effects were not as tremendous as in my
case and so they were silently going through with no harm ...)

Thanks anyway for looking at this and fixing the problem.

Ciao ciao

Ralph

[Michael Bayer]

can you try out the latest hg tip for me please, I still feel like releasing this today:

http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz

[Chris Withers]

Michael Bayer wrote:
> can you try out the latest hg tip for me please, I still feel like releasing this today:
>
> http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz

I've got a brown bag you can borrow if it's that serious ;-)

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Ralph Heinkel]

Hi Michael,

I've just installed this package, and it works fine for me. Thanks for

fixing the problem.

Ciao ciao

Ralph

[David Gardner]

I just noticed this behavior as well, and confirm that the tar.gz that
was posted fixed the problem,
and doesn't introduce any problems either.

--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgar...@creatureshop.com

[Martijn Moeling]

Hi

I have done the following:

class Affiliation(Base):
__tablename__ = "affiliations"
Id = Column(Integer, primary_key=True)
FullName = Column(Unicode(255), index = True)
discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}

class Person(Affiliation):
__tablename__ = 'persons'
__mapper_args__ = {'polymorphic_identity' : u'person'}
Id = Column(Integer,ForeignKey('affiliations.Id'), primary_key=True)

class Company(Affiliation):
__tablename__ = 'companies'
__mapper_args__ = {'polymorphic_identity' : u'company'}
Id = Column(Integer,ForeignKey('affiliations.Id'), primary_key=True)

which is straight foreward.

What I want do do now is a bit more troublesome, I have been trying many different things but....

class relation(Base):

Parent = reference to one of the Affiliations
Child = reference to one of the Affiliations
Relation_type = column(Integer) (like two persons can be Father and Son, Two companies can be supplier and customer)

It would be very nice if I can change the Affiliation class to self reference M:N

Problem with this is that both Parent and child (or "left and right "side") have to be checked and updated

Martijn

[Martijn Moeling]

Update, (Still need help, I'm really puzzled on how to do this)

since both Person and Company are polymorphic from Affiliation the relations are not really self referencing,

So I need help defining the relation and a reference table. The reference table should however be something like this:

TablenameL LId TablenameR Rid

person 1 company 2
company 2 person 3

So the reference table "relations" should have a compound key "person:1" to connect to "company:2"
at the same time "company:2" should connect to "person:3"

I would like to add something like this to my Affiliation class:

relationships = relation(Relation,primaryjoin=or_(and_(Relation.LTable==discriminator,Relation.LId ==Id),and_(Relation.RTable==discriminator,Relation.RId==Id)))

I know this is wrong but the reference table should also work backwards. There will also be an Relationtype indicating

Company->employee->Person,
Company->customer->Person
Person->friend-Person

etc. (one Company can have many relations to the same Person, like Companies can have multiple relations with other companies (like "supplier", "customer", "partner (like in a project)" etc. etc)

also Person1->Father->Person2 will be Person2->Son->Person1 when lookup the other way around. the Relationtype will be a ForeignKey to another table, the "Order" will be dependent on the "side" of the (current object) Person so there might be two relations in the Affiliation object . One working on the "Left" side of the reference table and the other one from the "Right"

This is just a small piece, in total there are many Classes based on the Affiliation object so doing it all there would be nice....

Thanks for ANY thoughts, doing this right from the beginning helps me a lot.
The definition of Affiliation, Person and Company can be found below.

Martijn

[Michael Bayer]

I don't see why the relationship between two Affiliation subclasses can't be self-referencing. You'd add another column to "affiliations" with a name like "related_to", use relationship() with primaryjoin. If you model the relationship as simply Affiliation->Affiliation then that single relationship handles the linkage for all subclass combinations.

If you'd rather have the linkages on the "persons" and "companies" tables, that is also fine, you just need to use two separate relationships to manage each foreign key, one local to "persons" the other local to "companies".

Even if you use an association table, as long as the linkages are from Affiliation->Affiliation, no weird gymnastics with "relationtypes" should be needed for persistence/querying.

This may or may not be related but this seems to have some elements which resemble a "polymorphic association", which is described at: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ , perhaps that can shed some light as well.

[Martijn Moeling]

Michael,

Thank you for the answer, I have been "wasting" my time all day getting this to work, reading documents etc. Since I'm a low level programmer I'm not really into database programming, The article you pointed out is not really understandable by me because of this.

I did the following (I left out the "other" definitions:

class Relation(Base):
__tablename__ = 'relations'
RId = Column(Integer, ForeignKey('affiliations.Id'), primary_key=True) # primary_key is just to make SQLA happy about the PK needed
LId = Column(Integer, ForeignKey('affiliations.Id'))

class Affiliation(Base):
__tablename__ = "affiliations"
Id = Column(Integer, primary_key=True)

discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}

ParentRelation = relation('Affiliation',primaryjoin='Relation.LId == Affiliation.Id', secondary=Relation.__table__)
ChildRelation = relation('Affiliation',primaryjoin='Relation.RId == Affiliation.Id', secondary=Relation.__table__)

This trows the error: sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relation Affiliation.ChildRelation. Specify a 'primaryjoin' expression. If this is a many-to-many relation, 'secondaryjoin' is needed as well.

I tried it with the table definition not declerative:

rel = Table('relations', Base.metadata,
Column('RId', Integer, ForeignKey('affiliations.Id')),
Column('LId', Integer, ForeignKey('affiliations.Id')))

class Affiliation(Base):
__tablename__ = "affiliations"
Id = Column(Integer, primary_key=True)

discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}

ParentRelation = relation('Affiliation',primaryjoin='relations.LId == Affiliation.Id', secondary=rel)
ChildRelation = relation('Affiliation',primaryjoin='relations.RId == Affiliation.Id', secondary=rel)

And this throws: AttributeError: 'Table' object has no attribute 'LId'
(Once I create an Instance of Company like z=Company(), see the definition below)

I also did put the relation definition in the __init__ of the Affiliation class (with :

def __init__(self):
self.ParentRelation = relation('Affiliation',primaryjoin='Relation.LId == Affiliation.Id', secondary=Relation.__table__)
self.ChildRelation = relation('Affiliation',primaryjoin='Relation.RId == Affiliation.Id', secondary=Relation.__table__)

This seems to work, but:

p1 = Person() (see below)
z = Company() (see below)

Traceback (most recent call last):
File "/Users/martijn/pywebos/Affiliation/AffiliationMain.py", line 126, in <module>
z.ParentRelation.append(p1)
AttributeError: 'RelationProperty' object has no attribute 'append'

I'm puzzled, sorry....

Any suggestions?

Martijn

[Michael Bayer]

Here is a demonstration program. If I understand correctly, "ChildRelation" is just the inverse of "ParentRelation" so "backref" can save you lots of configuration here:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

relation_table = Table('relation', Base.metadata,
Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True),
Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True),
)

class Affiliation(Base):
__tablename__ = "affiliations"

id = Column(Integer, primary_key=True)

discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}

ParentRelation = relation(
'Affiliation',

primaryjoin=relation_table.c.rid==id,
secondaryjoin=relation_table.c.lid==id,
secondary=relation_table,
backref="ChildRelation")

class Person(Affiliation):
__tablename__ = 'persons'

id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'person'}

class Company(Affiliation):
__tablename__ = 'companies'

id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'company'}

e = create_engine('sqlite://', echo =True)
Base.metadata.create_all(e)
sess = Session(e)

p1, p2, c1, c2 = Person(), Person(), Company(), Company()

p1.ParentRelation.append(c1)
p1.ChildRelation.append(c2)
c1.ParentRelation.append(p2)
c2.ChildRelation.append(p1)

sess.add_all([p1, p2, c1, c2])
sess.commit()

[Martijn Moeling]

Michael,

Thank you,

reading trough your example (and running it)
made me understand a bit more how SQLA works.
I managed to get it all working,

Since I was on my way to something a bit slightly more complex ...one last Question

What if I would like to:

Class RelationType(Base):
__tablename__ = 'relationtypes'
Id = Column(Integer, primary_key=True)
ChildRelationType = Column(Unicode(20))
ParentRelationType = Column(Unicode(20))

Some data for information:

Id ChildRelationType ParentRelationType
1 "Parent" "Child"
2 "Supplier" "Customer"

I will put additional logic to print Mother - Son, in Fact this is not really what I am doing but I feel the need to make it a bit more understandable for the MailingList
At the end it will be something with molecules, not really interesting and easy to understand.

>
> relation_table = Table('relation', Base.metadata,
> Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True),

Column('RelationType', integer, ForeignKey('relationtypes.Id')),

> Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True),
> )

Adding this column to the relation_table does not make a difference but....

Somehow I need to get a reference to the "type of relation"
Setting the relation_type MUST be done whilst "Appending" it. i.e. every relation between "Affiliates" must have a reason

Is this possible or is there another way to do such a thing, I feel logically it should be part of the relation_table.

Thank you Again for your great help. I'm Not really good in Database stuff, but did/do a lot on the mod_python/mod_wsgi lists so I know about the huge amounts of time spent....

Martijn

[Michael Bayer]

You then turn the relation_table into an association object. A declarative example is in http://www.sqlalchemy.org/docs/orm/relationships.html#association-object .

The "Relation" class will have a relationship() to "child", or similar (and probably "parent" as a backref). Intercept the "set" event on this attribute(s) and use that to establish the desired "RelationType" value. This interception can be done with the @validates decorator: http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators .