Issue with making python executable with local db, sqlite3, tkinter

Question

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.