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
– Guilherme Matheus, Commented Apr 17, 2021 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
– Dmytro Chasovskyi, Commented Apr 18, 2021 at 7: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
– Guilherme Matheus, Commented Apr 19, 2021 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
– Dmytro Chasovskyi, Commented Apr 19, 2021 at 13:54
thank you for the support! It really helped me. I deploy my app to Heroku. Repository: github.com/guimatheus92/Game-Recommendation-System. Heroku: recommendation-game-system.herokuapp.com. Medium: guimatheus92.medium.com/…. — Guilherme Matheus
– Guilherme Matheus, Commented Apr 20, 2021 at 16:42

Dmytro Chasovskyi · Accepted Answer · 2021-04-19 13:53:39Z

3

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.

edited Apr 19, 2021 at 13:53

answered Mar 30, 2020 at 18:39

Dmytro Chasovskyi

3,7018 gold badges54 silver badges101 bronze badges

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

Comments

kingofrubik · Accepted Answer · 2020-03-27 15:58:46Z

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, 2020 at 15:58

answered Mar 27, 2020 at 2:23

kingofrubik

213 bronze badges

2 Comments

Dmytro Chasovskyi Over a year ago

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?

kingofrubik Over a year ago

Sorry, my bad. The import should be from <path to the file relative to __init__.py including file eg. myapp/makedatabase> import createMyDatabase. That should solve your import error. Then you should be able to just call the function createMyDatabase() to create the db.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related