Automap reflect tables within a postgres schema with sqlalchemy

Question

I'm following the sqlalchemy 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.

Answer 1

The answer is that database tables in SQLAlchemy require a primary key, and my table didn't have one. There is additional information on this page: http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key.

The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well. These columns do not need to be actually known to the database as primary key columns, though it’s a good idea that they are. It’s only necessary that the columns behave as a primary key does, e.g. as a unique and not nullable identifier for a row.

Thanks to Michael Bayer for answering this on the sqlalchemy mailing list: https://groups.google.com/forum/#!topic/sqlalchemy/8F2tPkpR4bE