How To Test Sqlalchemy With Reflected Database

As my flask app should not write anything in my database, I set up Flask-SQLAlchemy to reflect my database. This way I do not have to change my models, when I change my schema: # a

Solution 1:

There's no general rule for this situation: you database is decoupled from your application so you need to somehow get a copy of the database's schema to recreate locally.

Many database engines provide a way to dump a database schema to a file which in turn can be used to load a schema onto another server (or onto the same server with a different name).

If you want to stick to using Python and SQLAlchemy tools you could populate the database metadata via reflection on your production database, then use the metadata to create the tables on your local database.

Something like this: on the production server:

import pickle

import sqlalchemy as sa


engine = sa.create_engine(PRODUCTION_DATABASE_URI)

metadata = sa.MetaData()
metadata.reflect(engine)

# Save the metadata so that it can be transferred to another machine.withopen('metadata.pkl', 'wb') as f:
    pickle.dump(metadata, f)

Then locally

# Restore the metadata objectwithopen('metadata.pkl', 'rb') as f:
    metadata = pickle.load(f)

engine = sa.create_engine(TEST_DATABASE_URI)

# Create the tables
metadata.create_all(engine)