SQLAlchemy not creating tables with db.create

This question has been asked a few times I never been able to fix my problem since a few days. I tried to create a new object in my sqllite db related to a Model that I created but I always have this issue:

Arguments: (OperationalError('(sqlite3.OperationalError) no such table: sku_model'),)

app.py

import requests
from flask import Flask
from flask_crontab import Crontab
from app.routes import routes_blueprint
from app.config import BaseConfig, BasicConfig, TestConfig
import os
from statistics import median
import click
from app.models import SKUModel, db


def create_app():

    app = Flask(__name__)
    app.config["SECRET_KEY"] = "any secret key"
    app.config["SQLALCHEMY_DATABASE_URI"] = BaseConfig.SQLALCHEMY_DATABASE_URI
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)
    app.register_blueprint(routes_blueprint)
    crontab.init_app(app)
    return app


def setup_database(app):
    with app.app_context():
        db.create_all()
        app.logger.info("DB init!")

crontab = Crontab()

if __name__ == "__main__":
    app = create_app()
    # not os.path.isfile(BaseConfig.SQLALCHEMY_DATABASE_URI):
    setup_database(app)
    app.run()

database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

models.py

import flask_sqlalchemy

db = flask_sqlalchemy.SQLAlchemy()


class SKUModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    sku = db.Column(db.String)
    product_title = db.Column(db.String)
    quantity = db.Column(db.Integer)
    price = db.Column(db.Float)

    # JSON serializer
    def to_json(self):
        return { 
            "id": self.id,
            "sku": self.sku,
            "product_title": self.product_title,
            "quantity": self.quantity,
            "price": self.price,
        }

config.py

import os


# default config
class BaseConfig(object):
    DEBUG = False
    # shortened for readability
    SECRET_KEY = '\xbf\xb0\x11\xb1\xcd\xf9\xba\x8bp\x0c...'
    SQLALCHEMY_PATH = "/tmp/database.db"
    #SQLALCHEMY_DATABASE_URI = "sqlite:////tmp/sku.db"
    SQLALCHEMY_DATABASE_URI = "sqlite:///"   SQLALCHEMY_PATH


class TestConfig(BaseConfig):
    DEBUG = True
    TESTING = True
    WTF_CSRF_ENABLED = False
    SQLALCHEMY_DATABASE_URI = 'sqlite:///:memory:'

class BasicConfig(BaseConfig):
    DATASET_PATH = "app/data/dataset.json"

routes.py

from itertools import product
import json
from statistics import median

import requests
from flask import Blueprint, request

from app.models import SKUModel, db
from app.core import get, get_all, update_dataset, get_5_highest, update_21, create, delete, get_lowest
routes_blueprint = Blueprint("route_blueprint", __name__)


@routes_blueprint.route("/")
def hello():
    return "Hello World!"


# Retrieve the current timezone using the WorldTimeAPI (http://worldtimeapi.org) for any country available in the service
@routes_blueprint.route("/timezone/<string:area>/<string:region>")
def timezone(area, region):
    url = f"http://worldtimeapi.org/api/timezone/{area}/{region}"
    response = requests.get(url)
    return (
        {"UTF:": response.json()["utc_offset"]}
        if response.ok
        else {"response error": response.text, "http code": response.status_code}
    )


# Get one SKU
@routes_blueprint.route("/sku/<int:id>")
def get_sku(id):
    sku = get(id)
    return sku.to_json() if sku else ("Not found", 404)


# Get all SKUs
@routes_blueprint.route("/sku")
def get_all_sku():
    return json.dumps([sku.to_json() for sku in get_all()])


@routes_blueprint.route("/sku/update", methods=["GET"])
def update_from_dataset():
    return update_dataset()


# Get the 5 best prices for a SKU
@routes_blueprint.route("/sku/best", methods=["GET"])
def get_best_sku():
    return json.dumps([sku.to_json() for sku in get_5_highest()])


# Update a SKU from an ID by increasing it's price by 21%
@routes_blueprint.route("/sku/<int:id>", methods=["PUT"])
def update_sku(id):
    return update_21(id).to_json()

# Create SKU from form data
@routes_blueprint.route("/sku", methods=["POST"])
def create_sku():
    sku = SKUModel(
        sku=request.form["sku"],
        product_title=request.form["product_title"],
        quantity=request.form["quantity"],
        price=request.form["price"],
    )
    return create(sku).to_json()
         


# Delete SKU from ID
@routes_blueprint.route("/sku/<int:id>", methods=["DELETE"])
def delete_sku(id):
    return delete(id)


# Return the lowest price for a SKU
@routes_blueprint.route("/sku/lowest")
def get_lowest_sku():
    return get_lowest().to_json()


# return median price of all SKU.
@routes_blueprint.route("/sku/median")
def get_median_sku():
    return {"median": median([sku.price for sku in get_all()])}

core.py

from asyncio.log import logger
from app.models import SKUModel, db
from app.config import BasicConfig
import json


import logging

LOGGER = logging.getLogger(__name__)

def get(id):
  try:
    return db.session.query(SKUModel).get(id)
  except Exception as e:
    LOGGER.error("Error while getting SKU", e)
    return None
  return None

def get_all():
  try: 
     return db.session.query(SKUModel).all()
  except Exception as e:
    LOGGER.error("Error while getting all SKU", e)
    return None

I'm sure I missed something. I heard that db.create_all() should be use after importing my model and this is what I'm doing.

CodePudding user response：

When you called db.create_all() inside app.py was imported from app.database. but the db inside of app.models is defined locally. Therefore there are no models in the db inside app.py and that is why it didn't create any tables. the simplest fix would be to update app.py and change:

from app.database import db

from app.models import db

running this extracted code works for me. If it doesn't for you then perhaps you have an issue writing to the /tmp/database.db file.

import flask_sqlalchemy
from flask import Flask
db = flask_sqlalchemy.SQLAlchemy()

class SKUModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    sku = db.Column(db.String)
    product_title = db.Column(db.String)
    quantity = db.Column(db.Integer)
    price = db.Column(db.Float)

SQLALCHEMY_PATH = "/tmp/database.db"
SQLALCHEMY_DATABASE_URI = "sqlite:///"   SQLALCHEMY_PATH

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
db.init_app(app)
with app.app_context():
     print(db)
     db.create_all()

If this simple code works, then you have to look at your code to find out why it isn't running in the expected fashion.

CodePudding user response：

Use flask "before_first_request" to create database tables.

@app.before_first_request
def create_database_tables():
    with app.app_context():
         db.create_all()