I have made a simple CRUD database program for harvest. It is able to read from database, update the database, delete things from database and etc. by doing queries to the SQLite3 database. Now, I would like to make it executable so that I could share it to users for them to try out the program.
I have tried pyinstaller with this command: pyinstaller -w --onefile --add-data "harvest.db:." program.py
It compiles and everything but when i try to run the app, i get a console error that there is no such table in db. sqlite3.OperationalError: no such table: storages and it close the program.
Is there a way how to fix it ? Many thanks.
Code:
#Imports
from tkinter import *
from tkinter import messagebox
from tkinter import ttk
import tkinter as tk
import customtkinter
import pandas as pd
import os
import os.path
import sqlite3
#System Settings
customtkinter.set_appearance_mode("System")
customtkinter.set_default_color_theme("blue")
#App frame
app = customtkinter.CTk() #inicializovanie
app.geometry("1024x768")
app.title("Žatva")
app.resizable(False, False)
#Db connection
conn = sqlite3.connect("harvest.db")
cursor = conn.cursor()
#Switch for routing between pages
def switch(page):
#For loop, checking if in main frame is generated any page from our defined functions, and destroying it before rendering new frame, also updating app
for frame in main_frame.winfo_children():
frame.destroy()
app.update()
page()
#Navbar
navbar_frame = tk.Frame(app, bg="#8D99AE")
navbar_frame.pack(padx= 0)
navbar_frame.pack_propagate(False)
navbar_frame.configure(width=1024, height=75)
#NavbarBtns
#command, function for button, lambda is keyword for anonymous function ,syntax lamba arguments: expression
records_btn = customtkinter.CTkButton(navbar_frame, text="ZÁZNAMY", corner_radius=25, font=("Arial", 24), hover_color="#405BFF", width=200, height=55, command=lambda: switch(page=records_page))
records_btn.place(x=40, y=10)
fields_btn = customtkinter.CTkButton(navbar_frame, text="POLIA", corner_radius=25, font=("Arial", 24), hover_color="#405BFF", width=200, height=55, command=lambda: switch(page=fields_page))
fields_btn.place(x=280, y=10)
crops_btn = customtkinter.CTkButton(navbar_frame, text="PLODINY", corner_radius=25, font=("Arial", 24), hover_color="#405BFF", width=200, height=55, command=lambda: switch(page=crops_page))
crops_btn.place(x=520, y=10)
storages_btn = customtkinter.CTkButton(navbar_frame, text="SKLADY", corner_radius=25, font=("Arial", 24), hover_color="#405BFF", width=200, height=55, command=lambda: switch(page=storages_page))
storages_btn.place(x=760, y=10)
#RecordsFrame
def records_page():
records_page_frame = tk.Frame(main_frame)
#Creating table
cursor.execute('''CREATE TABLE IF NOT EXISTS records (id INTEGER PRIMARY KEY, fieldName TEXT, fieldArea TEXT, cropName TEXT, cropType TEXT, storageName TEXT, load FLOAT)''')
conn.commit()
def load_storages():
cursor.execute("SELECT storage FROM storages")
storages = cursor.fetchall()
storage_options = ["{}".format(storage[0]) for storage in storages]
return storage_options
storages = load_storages()
selected_storage_var = tk.StringVar()
if storages:
selected_storage_var.set(storages[0])
else:
storages = ["Nebol nahratý sklad."]
selected_storage_var.set(storages[0])
def load_field_props():
cursor.execute("SELECT DISTINCT id, fieldName, fieldArea, sowedCrop, sowedCropType FROM fields")
field_props = cursor.fetchall()
field_props_options = ["{} - {} - {} - {} - {}".format(fprop[0], fprop[1], fprop[2], fprop[3], fprop[4]) for fprop in field_props]
return field_props_options
fprops = load_field_props()
selected_fprops_var = tk.StringVar()
if fprops:
selected_fprops_var.set(fprops[0])
else:
fprops = ["Nebolo nahraté pole."]
selected_fprops_var.set(fprops[0])
def add_record():
selected_fprop = selected_fprops_var.get()
id, fieldName, fieldArea, cropName, cropType = selected_fprop.split(" - ")
storageName = selected_storage_var.get()
load = float(entryLoad.get())
if fieldName and fieldArea and cropName and cropType and storageName and load:
cursor.execute("INSERT INTO records (fieldName, fieldArea, cropName, cropType, storageName, load) VALUES (?,?,?,?,?,?)",
(fieldName, fieldArea, cropName, cropType, storageName, load))
conn.commit()
entryLoad.delete(0, tk.END)
load_records()
else:
messagebox.showwarning("Warning", "Vyplň všetky polia.")
def load_records():
records_listbox.delete(0, tk.END)
cursor.execute("SELECT * FROM records")
records = cursor.fetchall()
for row in records:
records_listbox.insert(tk.END, " ".join(str(item) for item in row[:8]))
def del_record():
selected_record = records_listbox.curselection()
if selected_record:
index = selected_record[0]
selected_item = records_listbox.get(index)
ind = selected_item.split()[0]
cursor.execute("DELETE FROM records WHERE id=?", (ind,))
conn.commit()
cursor.execute("SELECT id FROM records ORDER BY id")
ids = cursor.fetchall()
for i, row in enumerate(ids, start=1):
cursor.execute("UPDATE records SET id=? WHERE id=?", (i, row[0]))
conn.commit()
load_records()
else:
messagebox.showwarning("Warning", "Vyber záznam na vymazanie")
def del_all_records():
confirmation = messagebox.askyesno("Potvrdenie", "Naozaj chcete vymazať všetky polia?")
if confirmation:
cursor.execute("DELETE FROM records")
conn.commit()
load_records()
else:
messagebox.showinfo("Info", "Operácia zrušená.")
def excel_fields():
try:
query = 'SELECT FIELDNAME AS "Názov poľa", FIELDAREA AS "Výmera poľa", CROPNAME AS "Vysiata plodina", CROPTYPE AS "Odroda vysiatej plodiny", SUM(LOAD) AS "Celkové dovezené množstvo [kg]",ROUND(SUM(LOAD) / CAST(FIELDAREA AS FLOAT), 3) AS "Priemerný výnos [kg/ha]"FROM RECORDS GROUP BY FIELDNAME, FIELDAREA;'
df = pd.read_sql_query(query, conn)
desktop_path = os.path.expanduser("~")
excel_file_path = os.path.join(desktop_path, "ZostavaPolia.xlsx")
df.to_excel(excel_file_path, index=False)
messagebox.showinfo("Success", "Zostava podľa polí bola úspešne vyexportovaná.")
except Exception as e:
messagebox.showerror("Error", f"Error {str(e)}")
def excel_crops():
try:
query = 'SELECT cropName AS "Názov plodiny", SUM(fieldArea) AS "Celková výmera [ha]", SUM(load) AS "Dovezené množstvo [kg]", ROUND(SUM(ROUND(SUM(load) / MAX(fieldArea), 3)) OVER (PARTITION BY cropName) / COUNT(*) OVER (PARTITION BY cropName), 3) AS "Priemerný výnos [kg/ha]" FROM (SELECT cropName, fieldName, MAX(fieldArea) AS fieldArea, SUM(load) AS load FROM records GROUP BY cropName, fieldName) AS subquery GROUP BY cropName;'
df = pd.read_sql_query(query, conn)
desktop_path = os.path.expanduser("~")
excel_file_path = os.path.join(desktop_path, "ZostavaPlodiny.xlsx")
df.to_excel(excel_file_path, index=False)
messagebox.showinfo("Success", "Zostava podľa plodín bola úspešne vyexportovaná.")
except Exception as e:
messagebox.showerror("Error", f"Error {str(e)}")
def excel_ctypes():
try:
query = 'SELECT cropName AS "Názov plodiny", cropType AS "Odroda vysiatej plodiny", SUM(fieldArea) AS "Celková výmera [ha]", SUM(load) AS "Dovezené množstvo [kg]", ROUND(SUM(ROUND(SUM(load) / MAX(fieldArea), 3)) OVER (PARTITION BY cropType) / COUNT(*) OVER (PARTITION BY cropType), 3) AS "Priemerný výnos [kg/ha]" FROM (SELECT cropName, cropType, fieldName, MAX(fieldArea) AS fieldArea, SUM(load) AS load FROM records GROUP BY cropName, cropType, fieldName) AS subquery GROUP BY cropType;'
df = pd.read_sql_query(query, conn)
desktop_path = os.path.expanduser("~")
excel_file_path = os.path.join(desktop_path, "ZostavaOdrody.xlsx")
df.to_excel(excel_file_path, index=False)
messagebox.showinfo("Success", "Zostava podľa odrôd bola úspešne vyexportovaná.")
except Exception as e:
messagebox.showerror("Error", f"Error {str(e)}")
def excel_storages():
try:
query = 'SELECT storageName AS "Názov skladu", fieldName AS "Názov poľa", cropName AS "Názov plodiny", cropType AS "Názov odrody", SUM(load) AS "Celkové množstvo v sklade [kg]" FROM records GROUP BY storageName, fieldName, cropName, cropType;'
df = pd.read_sql_query(query, conn)
desktop_path = os.path.expanduser("~")
excel_file_path = os.path.join(desktop_path, "ZostavaSklady.xlsx")
df.to_excel(excel_file_path, index=False)
messagebox.showinfo("Success", "Zostava podľa skladov bola úspešne vyexportovaná.")
except Exception as e:
messagebox.showerror("Error", f"Error {str(e)}")
#? User inputs
fieldNameLabel = Label(records_page_frame, text="Názov poľa: ", font="bold 20")
fieldNameLabel.place(x=40, y=20)
#OptionMenu from fields
fPropsDropDown = OptionMenu(records_page_frame, selected_fprops_var, *fprops)
fPropsDropDown.config(width=40, font=(None, 20), pady=10)
fPropsDropDown.place(x=40, y=60)
loadLabel = Label(records_page_frame, text="Dovezené množstvo v kg: ", font="bold 20")
loadLabel.place(x=40, y=115)
entryLoad = customtkinter.CTkEntry(records_page_frame, width=250, height=35, font=(None, 20))
entryLoad.place(x=40, y=170)
storageLabel = Label(records_page_frame, text="Názov skladu: ", font="bold 20")
storageLabel.place(x=40, y=225)
storageDropdown = OptionMenu(records_page_frame, selected_storage_var, *storages)
storageDropdown.config(width=16, font=(None, 20), pady=10)
storageDropdown.place(x=40, y=260)
records_listbox = tk.Listbox(records_page_frame, font=(None, 16), width=62, height=30, highlightcolor="#405BFF")
records_listbox.place(x=335, y=100)
submit = customtkinter.CTkButton(records_page_frame, text="Pridaj záznam", width=250, height=35, font=(None, 20), command=add_record)
submit.place(x=40, y=315)
delete = customtkinter.CTkButton(records_page_frame, text="Vymaž záznam", width=250, height=35, font=(None, 20), command=del_record)
delete.place(x=40, y=360)
del_all_button = customtkinter.CTkButton(records_page_frame, text="Vymaž všetky záznamy", width=250, height=35, font=(None, 20), command=del_all_records)
del_all_button.place(x=40, y=405)
excel_fields_button = customtkinter.CTkButton(records_page_frame, text="Zostava na základe polí", width=250, height=35, font=(None, 20), command=excel_fields)
excel_fields_button.place(x=40, y=450)
excel_crops_button = customtkinter.CTkButton(records_page_frame, text="Zostava na základe plodín", width=250, height=35, font=(None, 20), command=excel_crops)
excel_crops_button.place(x=40, y=495)
excel_ctypes_button = customtkinter.CTkButton(records_page_frame, text="Zostava na základe odrôd", width=250, height=35, font=(None, 20), command=excel_ctypes)
excel_ctypes_button.place(x=40, y=540)
excel_storages_button = customtkinter.CTkButton(records_page_frame, text="Zostava na základe skladov", width=250, height=35, font=(None, 20), command=excel_storages)
excel_storages_button.place(x=40, y=585)
load_records()
records_page_frame.pack(fill=tk.BOTH, expand=True)
#*FieldsFrame
def fields_page():
fields_page_frame = tk.Frame(main_frame)
#Creating table
cursor.execute('''CREATE TABLE IF NOT EXISTS fields (id INTEGER PRIMARY KEY, fieldName TEXT, fieldArea FLOAT, sowedCrop TEXT, sowedCropType TEXT)''')
conn.commit()
def load_crops():
cursor.execute("SELECT DISTINCT cropName, cropType FROM crops")
crops = cursor.fetchall()
crop_options = ["{} - {}".format(crop[0], crop[1]) for crop in crops]
return crop_options
crops = load_crops()
selected_crop_var = tk.StringVar()
if crops:
selected_crop_var.set(crops[0])
else:
crops = ["Nebola nahratá plodina."]
selected_crop_var.set(crops[0])
def add_field():
selected_crop = selected_crop_var.get()
cropName, cropType = selected_crop.split(" - ")
fieldName = entryFieldName.get()
fieldArea = float(entryfieldArea.get())
if cropName and cropType and fieldName and fieldArea:
cursor.execute("INSERT INTO fields (fieldName, fieldArea, sowedCrop, sowedCropType) VALUES(?,?,?,?)", (fieldName, fieldArea, cropName, cropType))
conn.commit()
load_fields()
else:
messagebox.showwarning("Warning", "Vyplň všetky polia.")
def load_fields():
field_listbox.delete(0, tk.END)
cursor.execute("SELECT * FROM fields")
fields = cursor.fetchall()
for row in fields:
field_listbox.insert(tk.END, " ".join(str(item) for item in row[0:5]))
def del_field():
selected_field = field_listbox.curselection()
if selected_field:
index = selected_field[0]
selected_item = field_listbox.get(index)
ind = selected_item.split()[0]
cursor.execute("DELETE FROM fields WHERE id=?", (ind,))
conn.commit()
cursor.execute("SELECT id FROM fields ORDER BY id")
ids = cursor.fetchall()
for i, row in enumerate(ids, start=1):
cursor.execute("UPDATE fields SET id=? WHERE id=?", (i, row[0]))
conn.commit()
load_fields()
else:
messagebox.showwarning("Warning", "Vyber pole na vymazanie")
def del_all_fields():
confirmation = messagebox.askyesno("Potvrdenie", "Naozaj chcete vymazať všetky polia?")
if confirmation:
cursor.execute("DELETE FROM fields")
conn.commit()
load_fields()
else:
messagebox.showinfo("Info", "Operácia zrušená.")
def excel_export():
try:
query = "SELECT id, fieldName AS 'Názov poľa', fieldArea AS 'Výmera poľa', sowedCrop AS 'Vysiata plodina', sowedCropType AS 'Odroda vysiatej plodiny' FROM fields"
df = pd.read_sql_query(query, conn)
desktop_path = os.path.expanduser("~") # Cesta k domovskej zložke používateľa
excel_file_path = os.path.join(desktop_path, "Polia.xlsx")
df.to_excel(excel_file_path, index=False)
messagebox.showinfo("Success", "Zoznam polí bol úspešne vyexportovaný.")
except Exception as e:
messagebox.showerror("Error", f"Error {str(e)}")
#? User inputs
fieldNameLabel = Label(fields_page_frame, text="Názov poľa: ", font="bold 20")
fieldNameLabel.place(x=40, y=20)
entryFieldName = customtkinter.CTkEntry(fields_page_frame, width=250, height=35, font=(None, 20))
entryFieldName.place(x=40, y=60)
fieldAreaLabel = Label(fields_page_frame, text="Výmera poľa: ", font="bold 20")
fieldAreaLabel.place(x=40, y=115)
entryfieldArea = customtkinter.CTkEntry(fields_page_frame, width=250, height=35, font=(None, 20))
entryfieldArea.place(x=40, y=170)
sowedCropLabel = Label(fields_page_frame, text="Vysiata plodina: ", font="bold 20")
sowedCropLabel.place(x=40, y=225)
#Option menu from crops
dropdown = OptionMenu(fields_page_frame, selected_crop_var, *crops)
dropdown.config(width=16, font=(None, 20), pady=10)
dropdown.place(x=40, y=270)
field_listbox = tk.Listbox(fields_page_frame, font=(None, 16), width=62, height=30, highlightcolor="#405BFF")
field_listbox.place(x=335, y=20)
submit = customtkinter.CTkButton(fields_page_frame, text="Pridaj pole", width=250, height=35, font=(None, 20), command=add_field)
submit.place(x=40, y=325)
delete = customtkinter.CTkButton(fields_page_frame, text="Vymaž pole", width=250, height=35, font=(None, 20), command=del_field)
delete.place(x=40, y=370)
export_button = customtkinter.CTkButton(fields_page_frame, text="Export zoznamu polí", width=250, height=35, font=(None, 20), command=excel_export)
export_button.place(x=40, y=460)
del_all_button = customtkinter.CTkButton(fields_page_frame, text="Vymaž všetky polia", width=250, height=35, font=(None, 20), command=del_all_fields)
del_all_button.place(x=40, y=415)
load_fields()
fields_page_frame.pack(fill=tk.BOTH, expand=True)
#*CropsFrame
def crops_page():
crops_page_frame = tk.Frame(main_frame)
#Creating table
cursor.execute('''CREATE TABLE IF NOT EXISTS crops (id INTEGER, cropName TEXT, cropType TEXT UNIQUE)''')
conn.commit()
def add_crop():
cropKod = int(entryCropKod.get())
cropName = entryCropName.get()
cropType = entryCropType.get()
if cropKod and cropName and cropType:
try:
cursor.execute("INSERT INTO crops(id, cropName, cropType) VALUES(?,?,?)", (cropKod, cropName, cropType))
conn.commit()
load_crops()
entryCropKod.delete(0, tk.END)
entryCropName.delete(0, tk.END)
entryCropType.delete(0, tk.END)
except sqlite3.IntegrityError:
messagebox.showwarning("Warning", "Typ plodiny musí by unikátny.")
else:
messagebox.showwarning("Warning", "Vyplň všetky polia.")
def load_crops():
crop_listbox.delete(0, tk.END)
cursor.execute("SELECT * FROM crops")
crops = cursor.fetchall()
for row in crops:
crop_listbox.insert(tk.END, " ".join(str(item) for item in row[0:3]))
def del_crop():
selected_crop = crop_listbox.curselection()
if selected_crop:
index = selected_crop[0]
selected_item = crop_listbox.get(index)
cropType = selected_item.split()[2]
cursor.execute("DELETE FROM crops WHERE cropType=?", (cropType,))
conn.commit()
load_crops()
else:
messagebox.showwarning("Warning", "Vyber plodinu na vymazanie")
#?User inputs
cropKodLabel = Label(crops_page_frame, text="Kód plodiny: ", font="bold 20")
cropKodLabel.place(x=40, y=20)
entryCropKod = customtkinter.CTkEntry(crops_page_frame, width=250, height=35, font=(None, 20))
entryCropKod.place(x=40, y=60)
cropNameLabel = Label(crops_page_frame, text="Názov plodiny: ", font="bold 20")
cropNameLabel.place(x=40, y=115)
entryCropName = customtkinter.CTkEntry(crops_page_frame, width=250, height=35, font=(None, 20))
entryCropName.place(x=40, y=170)
cropTypeLabel = Label(crops_page_frame, text="Odroda plodiny: ", font="bold 20")
cropTypeLabel.place(x=40, y=225)
entryCropType = customtkinter.CTkEntry(crops_page_frame, width=250, height=35, font=(None, 20))
entryCropType.place(x=40, y=270)
crop_listbox = tk.Listbox(crops_page_frame, font=(None, 16), width=62, height=30, highlightcolor="#405BFF")
crop_listbox.place(x=335, y=20)
submit = customtkinter.CTkButton(crops_page_frame, text="Pridaj plodinu", width=250, height=35, font=(None, 20), command=add_crop)
submit.place(x=40, y=325)
delete = customtkinter.CTkButton(crops_page_frame, text="Vymaž plodinu", width=250, height=35, font=(None, 20), command=del_crop)
delete.place(x=40, y=370)
load_crops()
crops_page_frame.pack(fill=tk.BOTH, expand=True)
#*StoragesFrame
def storages_page():
storages_page_frame = tk.Frame(main_frame)
#Creating table
cursor.execute('''CREATE TABLE IF NOT EXISTS storages(id INTEGER PRIMARY KEY, storage TEXT)''')
conn.commit()
def add_storage():
storageName = entryStorName.get()
if storageName:
cursor.execute("INSERT INTO storages(storage) VALUES(?)", (storageName,))
conn.commit()
load_storages()
entryStorName.delete(0, tk.END)
else:
messagebox.showwarning("Warning", "Zadaj názov skladu.")
def load_storages():
storage_listbox.delete(0, tk.END)
cursor.execute("SELECT * FROM storages")
storages = cursor.fetchall()
for row in storages:
storage_listbox.insert(tk.END, row[1])
def del_storage():
selected_storage = storage_listbox.get(tk.ACTIVE)
if selected_storage:
cursor.execute("DELETE FROM storages WHERE storage=?", (selected_storage,))
conn.commit()
load_storages()
else:
messagebox.showwarning("Warning", "Vyber sklad na vymazanie")
#?User inputs
nameLabel = Label(storages_page_frame, text="Názov skladu: ", font="bold 20")
nameLabel.place(x=40, y=20)
entryStorName = customtkinter.CTkEntry(storages_page_frame, width=250, height=35, font=(None, 20))
entryStorName.place(x=40, y=60)
storage_listbox = tk.Listbox(storages_page_frame, font=(None, 16), width=62, height=30, highlightcolor="#405BFF")
storage_listbox.place(x=335, y=20)
submit = customtkinter.CTkButton(storages_page_frame, text="Pridaj sklad", width=250, height=35, font=(None, 20), command=add_storage)
submit.place(x=40, y=115)
delete = customtkinter.CTkButton(storages_page_frame, text="Vymaž sklad", width=250, height=35, font=(None, 20), command=del_storage)
delete.place(x=40, y=170)
load_storages()
storages_page_frame.pack(fill=tk.BOTH, expand=True)
#MainFrame
main_frame = tk.Frame(app)
main_frame.pack(fill=tk.BOTH, expand=True)
records_page()
app.mainloop()
There is terminal error im getting, but in code everything looks just well.
Traceback (most recent call last):
File "hello.py", line 506, in <module>
File "hello.py", line 71, in records_page
File "hello.py", line 66, in load_storages
sqlite3.OperationalError: no such table: storages
[8807] Failed to execute script 'hello' due to unhandled exception: no such table: storages
[8807] Traceback:
Traceback (most recent call last):
File "hello.py", line 506, in <module>
File "hello.py", line 71, in records_page
File "hello.py", line 66, in load_storages
sqlite3.OperationalError: no such table: storages
Saving session...
...copying shared history...
...saving history...truncating history files...
...completed.
[Proces dokončený]
I tried to compile it but im getting lost i guess and dont know what to do and how to make it work.