How to test SQLAlchemy with reflected database

Question

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:

# app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    db.init_app(app)
    with app.app_context():
        db.Model.metadata.reflect(db.engine)

# app/models.py
from app import db

class Data(db.Model):
    __table__ = db.Model.metadata.tables['data']

This all works fine and dandy. But now, I wanted to implement tests using unittest. But I could not find anything how that is supposed to work? I am used to make a new sqlite database to test, but I don't have any Models to write there. What is the standard procedure here? Do you copy everything to sqlite? If so, how?

snakecharmerb · Accepted Answer · 2021-07-24 12:08:34Z

3

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.

with open('metadata.pkl', 'wb') as f:
    pickle.dump(metadata, f)

Then locally

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

engine = sa.create_engine(TEST_DATABASE_URI)

# Create the tables
metadata.create_all(engine)

answered Jul 24, 2021 at 12:08

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tibor Over a year ago

Thanks! This seems to work, I just run into a different problem regarding using the Models. When I need to import the Models before creating the app, it does not work as the metadata ist not filled yet. But I will open another question for that.

snakecharmerb Over a year ago

You can decouple the metadata load from the flask app in test mode. Create the tables with metadata.create_all(engine) initially, and then reflect them in the flask app later. But if it's more complicated than that, do open a new question to thrash it out.

Tibor Over a year ago

Question can be found here. Could you maybe give a small example? I didn't fully grasp what you meant.

snakecharmerb Over a year ago

In your test code, do metadata.create_all(engine) before you call create_app (perhaps even before db = SQLAlchemy())

Tibor Over a year ago

Thanks, now I understood. As the metadata is bound to the db object in my case, I did it a bit differently, but it now works. I will update my question.

Collectives™ on Stack Overflow

How to test SQLAlchemy with reflected database

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related