Flask SQLAlchemy reflection ignoring most of table…

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

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.

Flask SQLAlchemy reflection ignoring most of tables on Redshift

Advertisement

Answer