How to automatically create database tables and schema with Flask and Sqalchemy?

Question

I have a small Flask application. I add an extra security layer which is log in. I based my refactoring on the DO article.

In a nutshell,

__init__.py:

from flask import Flask
from flask_login import LoginManager
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash
from sqlalchemy import create_engine

# init SQLAlchemy so we can use it later in our models
db = SQLAlchemy()

def create_app():
    app = Flask(__name__)

    app.config['SECRET_KEY'] = 'e56432f4402c9a0c166fe6c6a0a2fbbd'
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite'

    db.init_app(app)

        # blueprint for auth routes in our app
    from .auth import auth as auth_blueprint
    app.register_blueprint(auth_blueprint)

    # blueprint for non-auth parts of app
    from .main import main as main_blueprint
    app.register_blueprint(main_blueprint)

    login_manager = LoginManager()
    login_manager.login_view = 'auth.login'
    login_manager.init_app(app)

    from .models import User

    @login_manager.user_loader
    def load_user(user_id):
        # since the user_id is just the primary key of our user table, use it in the query for the user
        return User.query.get(int(user_id))

    return app

In order to create DB, I need to run in REPL:

  from project import db, create_app
  db.create_all(app=create_app())

This is both inconvenient and makes Docker image creation harder. I run an application as flask run. I saw similar issues but don't understand how to apply to my scenario.

How can I overcome this complication?

UPDATE 1:

My project structure is the following:

project/
  project/
    __init__.py
    auth.py
    main.py
    models.py
    static/
    templates/
  Dockerfile
  requirements.txt

Do you have a repository about this project of yours? I'm facing the same problem already, you were the one that got close to it. I'm also following the Digital Ocean tutorial. — Guilherme Matheus, Apr 17 '21 at 18:28
Hi, @GuilhermeMatheus, I still have access to the project. Unfortunately, it is closed-source, so I cannot post a link to it. Do you have any particular questions? — Dmytro Chasovskyi, Apr 18 '21 at 07:21
Hi @Dmytro Chasovskyi, thank you anyway for asking. How can I run the `db.create_all()` from __init__.py file? Because the solution you propose (it worked for me) is in a function. I droped the tables that flask created and now I don't how to create again. — Guilherme Matheus, Apr 19 '21 at 13:10
@GuilhermeMatheus I added a comment to my answer. I guess your problem that you are running the app via python, in the meantime you need to run it via flask utility. — Dmytro Chasovskyi, Apr 19 '21 at 13:54
thank you for the support! It really helped me. I deploy my app to Heroku. Repository: https://github.com/guimatheus92/Game-Recommendation-System. Heroku: https://recommendation-game-system.herokuapp.com/. Medium: https://guimatheus92.medium.com/game-recommendation-system-using-machine-learning-and-flask-e0c2a3e0305b. — Guilherme Matheus, Apr 20 '21 at 16:42

Dmytro Chasovskyi · Accepted Answer · 2021-04-19T13:53:39.677

I found a more elegant solution

Instead of return app make the following block, so an application will be created in the runtime if and only if the context was initialized and database created.

with app.app_context():
        db.create_all()
        return app

Surprisingly the Flask-SqlAlchemy is pointing out to REPL db creation as a first solution, not the one above.

UPDATE:

Here is an example of my Dockerfile:

FROM python:3.7-slim

ENV FLASK_APP=.

WORKDIR /app

COPY requirements.txt /app/requirements.txt

COPY . /app

RUN pip install -r requirements.txt

CMD flask run -h 0.0.0.0 -p 5000

The catch is that you have to change how you run flask, not via python command.

kingofrubik · Answer 2 · 2020-03-27T15:58:46.623

2

If you want the database to be created when you run the flask app:

I would put this code

def createMyDatabase():
    from project import db, create_app
    db.create_all(app=create_app())

into the file makedatabase.py and save it into your program's root directory. Then add from <path to makedatabase.py>/makedatabase import createMyDatabase to the top of your __init__.py file and then just after your import statements in __init__.py write createMyDatabase(). I think that this would be the easiest way to do it in your situation.

If you don't want the database to be created every time that you run the program:

You could just take the function out of the makedatabase.py file and then run the file just as it is before you run the flask application.

edited Mar 27 '20 at 15:58

answered Mar 27 '20 at 02:23

kingofrubik

21
3

Hm, I added my project structure, so express what I tried to do more explicitly. I added makedatabase.py to the upmost `project` and run the application but I still get broken imports. Do you know how to debug it further? – Dmytro Chasovskyi Mar 27 '20 at 15:35
Sorry, my bad. The import should be `from import createMyDatabase`. That should solve your import error. Then you should be able to just call the function `createMyDatabase()` to create the db. – kingofrubik Mar 27 '20 at 15:53

How to automatically create database tables and schema with Flask and Sqalchemy?

2 Answers2