I have the exact same issue. I create a new MetaData, use tometadata to copy the first to the second, and then if I use the second metadata it works fine. But if I pickle and unpickle it first then it doesn't work. So something about pickling/unpickling is breaking it.
On Monday, February 24, 2014 at 5:32:59 PM UTC-5, bkcsfi sfi wrote: > > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
