Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)"

enter image description here

like image 694
Zephers Avatar asked Feb 01 '19 21:02

Zephers


People also ask

How does SQLAlchemy connect to Flask?

Step 1 - Install the Flask-SQLAlchemy extension. Step 2 - You need to import the SQLAlchemy class from this module. Step 3 - Now create a Flask application object and set the URI for the database to use. Step 4 - then use the application object as a parameter to create an object of class SQLAlchemy.

Is Flask-SQLAlchemy the same as SQLAlchemy?

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks. See the SQLAlchemy documentation to learn how to work with the ORM in depth.

What is application Factory in Flask?

A solution is to move the instantiation and configuration of the flask application into a function. The approach of using an application factory function and then calling it when you want to create a flask application instance is actually a well-known design pattern referred to as the Factory Method Pattern.


1 Answers

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.

like image 114
Dave W. Smith Avatar answered Oct 17 '22 02:10

Dave W. Smith