I have a legacy database that I would like to use with automap unfortunately a number of tables each have multiple fks to the same table.
Using docs at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#handling-multiple-join-paths and http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#specifying-classes-explcitly I thought I could define just some of the relationships before calling base.prepare(), but that's not working for me. First, because reflection takes a long time on my database, I am pickling base.metadata and restoring it later for use e..g to store the meta-data def generate_metadata_from_engine(engine): base = automap_base() base.prepare(engine, reflect=True) return base.metadata def store_metadata_to_file(metadata): cPickle.dump(metadata, file(get_metadata_path(), 'wb'), cPickle.HIGHEST_PROTOCOL) Later I restore it like this def get_unprepared_sqla_base(): """load metadata from file and return auto-map base""" return automap_base(metadata=load_metadata_from_file()) def load_metadata_from_file(): return cPickle.load(file(get_metadata_path(), 'rb')) Given a table in part like this: CREATE TABLE ORG ( DEFAULT_MANIFEST INTEGER, RETURN_MANIFEST INTEGER <snip> ) ALTER TABLE ORG ADD CONSTRAINT C644ORG_DEFAULT_ FOREIGN KEY (DEFAULT_MANIFEST) REFERENCES MANIFEST (ID) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE ORG ADD CONSTRAINT C644ORG_RETURN FOREIGN KEY (RETURN_MANIFEST) REFERENCES MANIFEST (ID) ON UPDATE CASCADE ON DELETE SET NULL; I have 2 fks to the manifest table When running a query against the org table, I get this error sqlalchemy.exc.ArgumentError: Error creating backref 'org_collection' on relationship 'org.org_collection': property of that name exists on mapper 'Mapper|org|org' It looks like the above error is actually on the manifest table, however it's cleaner for me to define the relationship on the Org table and hope that automap figures out the backref .. (not sure that works) So I'm trying this def test(): engine = get_firebird_engine() base = get_unprepared_sqla_base() class Org(base): __tablename__ = 'org' default_manifest_collection = relationship('manifest', foreign_keys="org.default_manifest") current_manifest_collection = relationship('manifest', foreign_keys="org.current_manifest") base.prepare() session = get_session() session.query(base.classes.org).first() when I call test(), I now get this error:: Traceback (most recent call last): File "database_metadata/test.py", line 46, in <module> main(args=sys.argv[1:]) File "database_metadata/test.py", line 39, in main test() File "database_metadata/test.py", line 25, in test session.query(base.classes.org).first() File "/home/bkc/Python_Environments/mwd/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 174, in __getattr__ raise AttributeError(key) AttributeError: org If I comment out the definition of class Org in the test(), I go back to getting the sqlalchemy.exc.ArgumentError: (though it's random which table it fails on first) 1. am I correctly using pickled automap metadata? 2. does the existence of the Org class in base metadata break base.prepare() because I'm not also reflecting from the database at that time? 3. should I instead declare the Org class fragment before reflecting, and then pickl'ing the meta-data with my modified org class will work? something else instead? Thanks -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
