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