Just hit the issue tracker with this, and the two snags I encountered so far doing this. Hopefully it's not because I missed some glaring instructions about how to build the documentation?
On Tuesday, April 28, 2015 at 3:46:11 PM UTC-4, Michael Bayer wrote: > > > > On 4/28/15 3:02 PM, Sam Zhang wrote: > > Thanks Michael! it was the lack of a primary key. I see references to it > now that I know what to look for > - a very interesting explanation: > http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key > - > http://stackoverflow.com/questions/23765681/sqlalchemy-automap-does-not-create-class-for-tables-without-primary-key > > It looks like there's no mention of this requirement in the automap > documentation page though: > http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html. I'd be > happy to add a brief note about it and submit a pull request if you'd like. > > > sure thing! > > > > Sam > > On Monday, April 27, 2015 at 6:54:13 PM UTC-4, Michael Bayer wrote: >> >> >> >> On 4/27/15 4:29 PM, Sam Zhang wrote: >> >> Hello, >> >> I'm following the documentation for reflecting database tables using >> `automap`: >> http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#generating-mappings-from-an-existing-metadata >> . >> >> When I don't specific a schema, and Postgres uses the default `public` >> schema, this works as expected, and I find the names of my tables: >> >> >>> m = MetaData() >> >>> b = automap_base(bind=engine, metadata=m) >> >>> b.prepare(engine, reflect=True) >> >>> b.classes.keys() >> ['ads', 'spatial_ref_sys', 'income'] >> >> But when I specific an explicit schema, I don't have access to the >> tables in `Base.classes` anymore. >> >> >>> m = MetaData(schema='geography') >> >>> b = automap_base(bind=engine, metadata=m) >> >>> b.prepare(engine, reflect=True) >> >>> b.classes.keys() >> [] >> >> The MetaData reflected correctly though: >> >> >>> b.metadata.tables >> immutabledict({geography.usa_cbsa_centroids': >> Table('usa_cbsa_centroids', >> MetaData(bind=Engine(postgresql://asteroids:***@localhost:5432/asteroids)), >> Column('GEOID', VARCHAR(length=5), table=<u >> sa_cbsa_centroids>, nullable=False), ...}) >> >> Note that the tables and columns are only known at runtime. >> >> Here's a demo that works for me. Does it work for you? Do all your >> tables have primary keys defined? >> >> >> from sqlalchemy.ext.automap import automap_base >> from sqlalchemy.orm import Session >> from sqlalchemy import create_engine, MetaData >> >> >> engine = create_engine("postgresql://scott:tiger@localhost/test", >> echo=True) >> engine.execute(""" >> create table if not exists test_schema.user ( >> id serial primary key, name varchar(30) >> ) >> """) >> engine.execute(""" >> create table if not exists test_schema.address ( >> id serial primary key, >> email_address varchar(30), >> user_id integer references test_schema.user(id) >> ) >> """) >> >> m = MetaData(schema="test_schema") >> >> Base = automap_base(bind=engine, metadata=m) >> >> # reflect the tables >> Base.prepare(engine, reflect=True) >> >> assert Base.classes.keys() == ['user', 'address'] >> >> User = Base.classes.user >> Address = Base.classes.address >> >> >> session = Session(engine) >> >> session.add(Address(email_address="[email protected]", user=User(name="foo"))) >> session.commit() >> >> u1 = session.query(User).first() >> print(u1.address_collection) >> >> >> >> >> >> >> >> Any thoughts? >> >> This is duplicated from >> <http://stackoverflow.com/questions/29905160/automap-reflect-tables-within-a-postgres-schema-with-sqlalchemy> >> http://stackoverflow.com/questions/29905160/automap-reflect-tables-within-a-postgres-schema-with-sqlalchemy, >> >> feel free to answer there as well. >> >> Thanks, >> Sam >> -- >> 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/d/optout. >> >> >> -- > 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] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- 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/d/optout.
