Flask-SQLAlchemy not creating tables due to import order?-CodePudding

I know there are a few threads out there with the same issue, but all of them have different ways they are initialising their flask applications, and the answers are sort of tailored to their setup. Some have put their SQLAlchemy init in their models files, some have just one massive application.py file with declarations at the top, some put it in __ init__.py.

But I've structured my flask applications the following way since I am comforable with this pattern:

init.py
|       \             \                \                      \
|        \             \                \                      \
app.py    models.py     init_db.py       some_db_helper.py      module_with_db_needs.py

Basically I have an init.py file that contains all the initialisation code for a Flask app:

init.py

from flask import Flask
from flask_wtf.csrf import CSRFProtect
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SECRET_KEY"] = "abcd"

db = SQLAlchemy()
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///testdata.sqlite3"
db.init_app(app)

csrf = CSRFProtect()
csrf.init_app(app)

From there, any other file that I need can just import the needed objects. So models.py can import db from init.py for defining models, app.py can import app for flask routes, and some_db_helper.py can also import db, etc:

models.py

from sqlalchemy import Table, ForeignKey, ...

from init import db, app     # Import app, db from init file


class Category(db.Model):
    id = Column(Integer, primary_key=True, autoincrement=True)
    # ... other properties

app.py

from init import app    # Import app from init file

@app.route("/index.html", methods=["GET"])
def index():
    return True

This file should create a new database file (I'm using sqlite), create the tables and data:

init_db.py

from models import *  # Get app, db from models file

def insert_data():
    # Insert some data into Category table


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

However, when I run python init_db.py, my database file does get created, but it has no tables created at all. When the program tries to insert data, I get this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: category

I am aware that the db models need to be defined first, so I have my from models import * line, then doing create_all() at the end after all is loaded. You can also see init_db.py is pulling db from the import *, so it's using the same db instance as the models.py file, which means it should be aware of the defined models before creating the tables.

How can I get my init_db.py to create the tables properly using my current folder structure / import strategy?

CodePudding user response：

I have read your comments, you have built some anti patterns, so rather than shoehorning this into working, let's go back to a healthier hierarchy.

. root of your app
 -- app.py (initialise app and routes)
 -- models.py (create models)
 -- init_db.py (db initial data, if you really don't want it in models.py)

`app.py`

All app initialisation, can be run with flask --app path/to/app.py run.

from flask import Flask

from init_db import insert_initial_data
from models import db

app = Flask(__name__)
app.config["SECRET_KEY"] = "abcd"

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///testdata.sqlite3"
db.init_app(app)

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

insert_initial_data()


@app.route("/index.html", methods=["GET"])
def index():
    return b"OK"

`models.py`

All DB schema.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, Integer, Table

db = SQLAlchemy()


class Category(db.Model):
    id = Column(Integer, primary_key=True, autoincrement=True)
    ...

`init_db.py`

I'd argue that belongs in models.py if you need them in the database on init.

def insert_initial_data():
    ...

PS1. If you want to split out your routes I really recommend a blueprint, it as easy to use as the Flask instance in your app.

PS2. This is functional, it does run and creates the DB file.