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
--
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.
