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. 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="f...@bar.com" <javascript:>, > 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, > > 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 sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
