How to get Flask-SQLAlchemy to work with the Application Factory Pattern

Question

I want to set up an sqlite database using Flask-SQLAlchemy. I am getting an Operational error (sqlite3.OperationalError) no such table.

This is for a Web app written with flask. I want to interact with the database using Flask-SQLAlchemy extension. I feel it may be something to do with the application context, but I am not sure.

As you can see the application has one 'module' (the auth sub-package). The module is registered to the application via a blueprint. I define the model for the Researcher class in the models.py file of the auth sub-package. app/__init__.py holds the Application Factory and the database initialization. Because I am just trying to get the basic functionality working, the only two views I have are defined in app/auth/routes.py.

---

.
|-- app
|   |-- __init__.py
|   |-- auth
|   |   |-- __init__.py
|   |   |-- __pycache__
|   |   |-- models.py
|   |   `-- routes.py
|   |-- static
|   `-- templates
|-- app.db
|-- config.py
|-- instance
|   `-- config.py
`-- run.py

app/init.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
def create_app():
    app = Flask(__name__)
    app.config.from_pyfile("../config.py")

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

    from app.auth import auth
    app.register_blueprint(auth, url_prefix="/auth/")

    return app 

app/auth/routes.py

from flask import Flask
from app import db
from models import User
from app.auth import auth

@auth.route("/insert")
def insert():
    #r = Reasearcher("Pat","Den", "[email protected]", "operator","Dr.","Mr", "08611111", "+353", "0001")
    u = User(1,"Elija")
    db.session.add(u)
    db.session.commit()

@auth.route("/query")
def query():
    us = users.query.all()
    s = ""
    for u in us:
        s += u.name
    return s

app/auth/models.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from app import db

class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    def __init__(self,ids, name):
        self.id = ids
        self.name = name

app/auth/init.py

from flask import Flask, Blueprint

auth = Blueprint("auth", __name__)

import routes
import models

config.py

DEBUG = True

import os
BASE_DIR = os.path.abspath(os.path.dirname(__file__))

SQLALCHEMY_DATABASE_URI = "sqlite:////" + os.path.join(BASE_DIR, "app.db")
DATABASE_CONNECT_OPTIONS = {}
THREADS_PER_PAGE = 2

CSRF_ENABLED     = True

CSRF_SESSION_KEY = "secret"
SECRET_KEY = "secret"

I should be able to request /auth/query and get the contents of the database table, but instead I get the error - "OperationalError: (sqlite3.OperationalError) no such table: researchers [SQL: u'INSERT INTO researchers (prefix, suffix, phone, phone_ext, orcid) VALUES (?, ?, ?, ?, ?)'] [parameters: ('Dr.', 'Mr', '08611111', '+353', '0001')] (Background on this error at: http://sqlalche.me/e/e3q8)"

Answer 1

At the point that code is doing db.create_all(), models haven't been imported. Importing them has the side-effect of populating some data structures that SQLAlchemy uses behind the scenes. If the db.create_all() happens before SQLAlchemy knows about models, it thinks there's nothing to do.

Try this: In __init__.py, remove with app.appcontext(): db.create_all() and add import models to the bottom. Then add a management command to run.py. It'll look something like

app = create_app()

@app.cli.command()
def createdb():
    db.create_all()

Then

FLASK_APP=run.py flask createdb

will create tables.

Answer 2

I haven't been able to reproduce your issues, but this was working for me.

In your your app folder: python3 -m venv venv source venv/bin/activate pip install flask flask_sqlalchemy pip install -e . flask run

__init__.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
def create_app():
    app = Flask(__name__)
    app.config.from_pyfile("../config.py")

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

        import auth
        app.register_blueprint(auth.routes.auth, url_prefix="/auth/")

        return app 

setup.py

from setuptools import setup

setup(
    name='yourapplication',
    include_package_data=True,
    install_requires=[
        'flask',
        ],
    )

auth/__init__.py

from flask import Flask, Blueprint

import auth.routes
import auth.models

auth/models.py

from flask import Flask, Blueprint
from app import db
from auth.models import Researcher

auth = Blueprint("auth", __name__)

@auth.route("/insert")
def insert():
    #r = Reasearcher("Pat","Den", "[email protected]", "operator","Dr.","Mr", "08611111", "+353", "0001")
    r = Researcher("Dr.","Mr", "08611111", "+353", "0001")
    db.session.add(r)
    db.session.commit()

    @auth.route("/query")
    def query():
        rs = Researcher.query.all()
        s = ""
        for r in rs:
            s += r.prefix + " " + r.phone
            return s

auth/routes.py

from flask import Flask, Blueprint
from app import db
from auth.models import Researcher

auth = Blueprint("auth", __name__)

@auth.route("/insert")
def insert():
    #r = Reasearcher("Pat","Den", "[email protected]", "operator","Dr.","Mr", "08611111", "+353", "0001")
    r = Researcher("Dr.","Mr", "08611111", "+353", "0001")
    db.session.add(r)
    db.session.commit()

    @auth.route("/query")
    def query():
        rs = Researcher.query.all()
        s = ""
        for r in rs:
            s += r.prefix + " " + r.phone
            return s