eager loading breaks one-to-many relationship (after refresh)

Let's define database test (Postgres), master table linkhi and detail table linkhi_part, and insert data:

CREATE TABLE linkhi ( linkhi bigint NOT NULL, name text NOT NULL );

CREATE TABLE linkhi_part ( linkhi_part bigint NOT NULL, name text NOT NULL, linkhi bigint NOT NULL );

INSERT INTO linkhi (linkhi, name) VALUES (1, 'A'); INSERT INTO linkhi (linkhi, name) VALUES (2, 'B');

INSERT INTO linkhi_part (linkhi_part, name, linkhi) VALUES (101, 'LHP_A_1', 1); INSERT INTO linkhi_part (linkhi_part, name, linkhi) VALUES (102, 'LHP_A_2', 1); INSERT INTO linkhi_part (linkhi_part, name, linkhi) VALUES (103, 'LHP_B_1', 2); INSERT INTO linkhi_part (linkhi_part, name, linkhi) VALUES (104, 'LHP_B_2', 2);

ALTER TABLE ONLY linkhi_part ADD CONSTRAINT pk_lhp PRIMARY KEY (linkhi_part);

ALTER TABLE ONLY linkhi ADD CONSTRAINT pl_lh PRIMARY KEY (linkhi);

CREATE INDEX fki_lh ON linkhi_part USING btree (linkhi);

ALTER TABLE ONLY linkhi_part ADD CONSTRAINT fk_lh FOREIGN KEY (linkhi) REFERENCES linkhi(linkhi) ON DELETE CASCADE;

this is python part:

#coding: utf_8

from sqlalchemy import Table,BoundMetaData,create_engine,mapper,relation,create_session

class Linkhi(object): pass

class Linkhi_part(object): pass

def main(): sa_db = create_engine('postgres://postgres:@localhost:5432/test',echo=False)

metadata = BoundMetaData(sa_db)
linkhi_table = Table('linkhi',metadata,autoload = True)
linkhi_part_table = Table('linkhi_part',metadata,autoload = True)

linkhi_mapper = mapper(Linkhi,linkhi_table,
        properties={'linkhi_part_ref': relation(Linkhi_part,lazy=False)})
linkhi_part_mapper = mapper(Linkhi_part,linkhi_part_table)

session=create_session()

lh_object = session.query(Linkhi).selectone(Linkhi.c.linkhi == 1)
print len(lh_object.linkhi_part_ref)
session.refresh(lh_object)
print len(lh_object.linkhi_part_ref)
print 'End'

main()

before call session.refresh() len(lh_object.linkhi_part_ref) == 2 and after call session.refresh(): len(lh_object.linkhi_part_ref) == 1 - WRONG!

Comments (1)