0

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?

Tibor Völcker
  • 175
  • 12

1 Answers1

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)
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • 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. – Tibor Völcker Jul 25 '21 at 18:16
  • 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. – snakecharmerb Jul 25 '21 at 18:23
  • Question can be found [here](https://stackoverflow.com/questions/68521426/import-reflected-flask-sqlalchemy-module-before-creating-the-app). Could you maybe give a small example? I didn't fully grasp what you meant. – Tibor Völcker Jul 25 '21 at 18:25
  • In your test code, do `metadata.create_all(engine)` before you call `create_app` (perhaps even before `db = SQLAlchemy()`) – snakecharmerb Jul 25 '21 at 18:29
  • 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. – Tibor Völcker Jul 25 '21 at 18:46