I am using a command line argument of -clear to empty the database of my application before the program starts up. My problem is that calling db.drop_all() from the run.py file throws a "no such table" error. I am wondering what the best approach to clearing my database before running the application would be. Here is my relevant code.
run.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()
init.py
# 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')
models.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]
Error message:
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)