时间戳

首页 > 解决方案 > 当我尝试使用命令行参数清除数据库时，db.drop_all() 没有给出这样的表错误

python - 当我尝试使用命令行参数清除数据库时，db.drop_all() 没有给出这样的表错误

问题描述

我正在使用 -clear 的命令行参数在程序启动之前清空我的应用程序的数据库。我的问题是从 run.py 文件调用 db.drop_all() 会引发“没有这样的表”错误。我想知道在运行应用程序之前清除数据库的最佳方法是什么。这是我的相关代码。

运行.py

# Imports
from wms import desktopApp, db
from wms.functions import load_data
import argparse

# Parsing command line arguments
description = """IMPORTANT: When loading information to the database, please insure that all files are stored in the 
resources folder and that the file format matches that described in the application documentation."""

parser = argparse.ArgumentParser(description=description)

# Arguments
parser.add_argument("-warehouses",
                    help="Loads the supplied warehouses.csv file into the database when the program is run.",
                    action="store_true")
parser.add_argument("-itemTemplates",
                    help="Loads the supplied item_templates.csv file into the database when the program is run.",
                    action="store_true")
parser.add_argument("-items",
                    help="Loads the supplied items.csv file into the database when the program is run.",
                    action="store_true")
parser.add_argument("-clear",
                    help="Clears the existing database.",
                    action="store_true")

args = parser.parse_args()

successful = load_data(args.warehouses, args.itemTemplates, args.items)

if not successful:
    quit()

if __name__ == '__main__':

    # Clear database
    if args.clear:
        while True:
            confirmation = input("Are you sure you want to clear the database? (y/n): ")

            if confirmation.lower() in ["yes", "y"]:
                # Drop db logic
                db.drop_all()
                print("Database cleared.")
                break
            elif confirmation.lower() in ["no", "n"]:
                print("Good thing we double checked.")
                break
            else:
                pass

    desktopApp.run()
    desktopApp.keep_server_running()

初始化文件

# Imports
from flask import Flask
from flaskwebgui import FlaskUI
from flask_sqlalchemy import SQLAlchemy

# App config
app = Flask(__name__)
app.config["DEBUG"] = True
app.config["TESTING"] = True
app.config["TEMPLATES_AUTO_RELOAD"] = True
app.config["SECRET_KEY"] = '8e3416a9c67b328517b7b758875aaea0'
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///data.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False  # Removes warning message in console

# Database config
db = SQLAlchemy(app)
from wms.models import Warehouse, Item, ItemTemplate
db.create_all()

# Desktop UI config
desktopApp = FlaskUI(app,
                     width=1200,
                     height=675,
                     browser_path='C:\Program Files (x86)\Google\Chrome\Application\chrome.exe')

模型.py

# Imports
from wms import db
from sqlalchemy.ext.hybrid import hybrid_property

# Constants
MAXIMUM_NAME_LEN = 14
LARGE_SCALE_SIZE = 150


# Template for creating items
class ItemTemplate(db.Model):

    """This template will simply store the information related to an item type.
    Individual items that will be associated with the warehouse they're stored in
    will inherit this information from the item templates."""

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(MAXIMUM_NAME_LEN), unique=True, nullable=False)
    price = db.Column(db.Float, nullable=False)
    cost = db.Column(db.Float, nullable=False)
    size = db.Column(db.Integer, nullable=False)
    low_threshold = db.Column(db.Integer, nullable=False)

    instances = db.relationship('Item', backref='item_template', lazy=True)  # Instances using this item template

    @hybrid_property
    def total_stock(self):
        total_stock = 0
        for instance in self.instances:
            total_stock += instance.quantity
        return total_stock

    @hybrid_property
    def possible_revenue(self):
        return self.total_stock * (self.price - self.cost)

    @hybrid_property
    def warehouses(self):
        return [instance.warehouse for instance in self.instances]

    @hybrid_property
    def stock(self):
        stock = 0
        for instance in self.instances:
            stock += instance.quantity
        return stock


# Actual items
class Item(db.Model):

    """This template will be used to represent the actual items that are associated with a warehouse."""

    id = db.Column(db.Integer, primary_key=True)
    quantity = db.Column(db.Integer, nullable=False)
    warehouse = db.Column(db.String(MAXIMUM_NAME_LEN), db.ForeignKey('warehouse.name'), nullable=False)

    item_template_id = db.Column(db.Integer, db.ForeignKey('item_template.id'), nullable=False)


# Warehouse
class Warehouse(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(MAXIMUM_NAME_LEN), unique=True, nullable=False)
    capacity = db.Column(db.Integer, nullable=False)
    items = db.relationship("Item", backref="home_warehouse", lazy=True)

    @hybrid_property
    def remaining_capacity(self):
        capacityUsed = 0
        for item in self.items:
            capacityUsed += item.quantity * ItemTemplate.query.filter_by(id=item.item_template_id).first().size
        return self.capacity - capacityUsed

    @hybrid_property
    def possible_revenue(self):
        revenue = 0
        for item in self.items:
            item_temp = ItemTemplate.query.filter_by(id=item.item_template_id).first()
            revenue += item.quantity * (item_temp.price - item_temp.cost)
        return revenue

    @hybrid_property
    def total_production_cost(self):
        total_production_cost = 0
        for item in self.items:
            total_production_cost += item.quantity * ItemTemplate.query.filter_by(id=item.item_template_id).first().cost
        return total_production_cost

    @hybrid_property
    def low_stock_items(self):
        low_stock = []
        for item in self.items:
            if item.quantity <= ItemTemplate.query.filter_by(id=item.item_template_id).first().low_threshold:
                low_stock.append(item)
        return low_stock

    @hybrid_property
    def large_scale_items(self):
        large = []
        for item in self.items:
            if ItemTemplate.query.filter_by(id=item.item_template_id).first().size >= LARGE_SCALE_SIZE:
                large.append(item)
        return large

    @hybrid_property
    def item_names(self):
        return [ItemTemplate.query.filter_by(id=item.item_template_id).first().name for item in self.items]

错误信息：

  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: warehouse

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\waitress\channel.py", line 397, in service
    task.service()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\waitress\task.py", line 168, in service
    self.execute()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\waitress\task.py", line 434, in execute
    app_iter = self.channel.server.application(environ, start_response)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2091, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2076, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1518, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1516, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\flask\app.py", line 1502, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "C:\coding\python\projects\WMS\wms\routes.py", line 22, in home
    warehouseList = Warehouse.query.all()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\query.py", line 2711, in all
    return self._iter().all()
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\query.py", line 2846, in _iter
    result = self.session.execute(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\golin\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: warehouse
[SQL: SELECT warehouse.id AS warehouse_id, warehouse.name AS warehouse_name, warehouse.capacity AS warehouse_capacity
FROM warehouse]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

标签： pythondatabaseflasksqlalchemycommand-line-arguments

解决方案

推荐阅读