I’m creating the engine and the Metadata as follows
engine = create_engine('redshift+psycopg2://USER:PASS.region.com:5439/DBNAME') metadata = MetaData(schema='SCHEMA') metadata.reflect(engine, only=['orders', 'packages']) Base = automap_base(metadata=metadata) Base.prepare() print(Base.classes.packages)
Yields AttributeError: packages
, and dir(Base.classes)
returns no attributes with that name, neither with the orders
name. Taking the only=['orders', 'packages']
off makes it reflect only a few random tables.
Now, when using the inspector that comes with native SQLAlchemy the table actually works (link to documentation):
insp = reflection.Inspector.from_engine(engine) print(insp.get_table_names(schema='SCHEMA')) #this prints all tables as expected packages_table = Table('packages', metadata) insp.reflecttable(packages_table, None) #this reflects the table as expected
Is this a bug, or am I overlooking something here?
Thanks!
Installed package versions:
alembic 1.4.2 Flask 1.1.2 Flask-Migrate 2.5.3 Flask-SQLAlchemy 2.4.4 psycopg2 2.8.5 SQLAlchemy 1.3.18 sqlalchemy-redshift 0.8.1
Advertisement
Answer
SQLAlchemy’s automap extension only reflects tables which have defined primary keys.
From the note in the docs:
[…] for a table to be mapped, it must specify a primary key.