Automap not reflecting tables in Postgres schemas

[Sam Zhang]

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.

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

[Mike Bayer]

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

[Sam Zhang]

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

[Mike Bayer]

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 Zhang]

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?

[Mike Bayer]

On 4/28/15 10:08 PM, Sam Zhang wrote:

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?

there's no instructions right now.  it's a sphinx build, plus the things that are in requirements.txt.   the themes and plugins are all extremely custom so you can't modify the themes or anything like that.