I made a GUI in python, with Tkinter. Now I just want to create an executable (.exe), so anyone can use my program. This program must record datas (The aim is to follow management indicators), so the datas must be stored and accessible. I need to be able to do my CRUD (create, read, update, delete) with my exe.
To do this, I use sqlite3. When I run my program from VSCode: The data is well saved, and when I close the program and re-run it, I find all my datas. Everything is working.
The problem : Now when I launch my .exe of this same programm, (i build the exe with auto-py-to-exe : I just select my python script, as well as my files and it made a single exe). When I run my exe I can save my data. BUT when I close my exe, an I reopen it, all my data are lost !!
I simplified the code to understand the problem. So I created methods that trigger when the user chooses the 6th row in the treeview widget. This opens a new window that displays the data. When I click on the button new data is saved.
I cannot send all the code in full, so I only send you the code contained in the "if choseUser == 6" bloc. These are basic methods. The goal is to simplify the problem: • A method generates a unique identifier • A method returns the current date and time • A method checks if the database already exists. If it does not exist, it creates it. • A method retrieves the data • A method deletes data based on an identifier • A method saves the data in 2 tables: o a table is called “history”: it has 2 columns: scan_id and date_scan o the 2nd table is called: "indica": it has 3 columns: scan_idi, data_one, data_two Here is the code:
if choixUser ==6:
win_debug = Toplevel(newWindow, bg="black")
win_debug.geometry("480x350")
win_debug.minsize(width=480, height=350)
win_debug.maxsize(width=480, height=350)
win_debug.resizable(height = None, width = None)
win_debug.geometry(f" {root.winfo_x() 500} {root.winfo_y() 100}") #
def random_id():
b1 = random.choice(string.ascii_uppercase)
b2 = random.choice(string.ascii_uppercase)
b3 = random.randint(10000,99999)
b4 = random.choice(string.ascii_lowercase)
b5 = random.randint(10,99)
id = f"{b1}{b2}-{b3}{b4}-{b5}"
return id
def gimme_the_date_now():
date = datetime.now()
out_format = "%d/%m/%Y %H:%M:%S"
date_formatee = datetime.strftime(date, out_format)
return date_formatee
def verify_or_create_DB():
connexion= sqlite3.connect('astupiddb.db')
cursor = connexion.cursor()
command1 = """CREATE TABLE IF NOT EXISTS history(scan_id TEXT PRIMARY KEY, datescan TEXT)"""
command2 = """CREATE TABLE IF NOT EXISTS indic(scan_id TEXT, data_one TEXT, data_two DOUBLE)"""
cursor.execute(command1)
cursor.execute(command2)
def fetch_datas(table):
connexion= sqlite3.connect('astupiddb.db')
query = f"SELECT * FROM {table}"
result = pd.read_sql(query, connexion)
result = pd.DataFrame(result)
return result
def suppr_data_with_the_id(id):
connexion= sqlite3.connect('astupiddb.db')
command1 = f"""DELETE FROM history WHERE scan_id =?"""
cursor = connexion.cursor()
cursor.execute(command1, (id,))
connexion.commit()
command2 = f"""DELETE FROM indic WHERE scan_id =?"""
cursor = connexion.cursor()
cursor.execute(command2, (id,))
connexion.commit()
def send_Datas_to_DB(dataFrame1, dataFrame2):
connexion= sqlite3.connect('astupiddb.db')
dataFrame1.to_sql('history', connexion, if_exists="append", index=False)
dataFrame2.to_sql('indic', connexion, if_exists="append", index=False)
connexion.commit()
connexion.close()
def prepareData_and_store():
id_Scan = random_id()
date_Scan = gimme_the_date_now()
d1 = random.choice(string.ascii_uppercase)
d2 = random.randint(10000,99999)
DATAFRAME_ONE = pd.DataFrame(columns=["scan_id","datescan"])
DATAFRAME_ONE= DATAFRAME_ONE.append({"scan_id":id_Scan,"datescan":date_Scan},ignore_index=True)
DATAFRAME_TWO = pd.DataFrame(columns=["scan_id","data_one","data_two"])
DATAFRAME_TWO= DATAFRAME_TWO.append({"scan_id":id_Scan,"data_one":d1,"data_two":d2},ignore_index=True)
send_Datas_to_DB(DATAFRAME_ONE,DATAFRAME_TWO)
verify_or_create_DB()
columns_tree = ('scan_id','datescan','data_one','data_two')
tree = ttk.Treeview(win_debug, columns=columns_tree, show='headings', selectmode='browse')
tree.column('#1', minwidth = 80, width = 80, stretch = False)
tree.column('#2', minwidth = 80, width = 80, stretch = False)
tree.column('#3', minwidth = 80, width = 80, stretch = False)
tree.column('#4', minwidth = 80, width = 80, stretch = False)
tree.heading('scan_id', text='scan id')
tree.heading('datescan', text='datescan')
tree.heading('data_one', text='data_one')
tree.heading('data_two', text='data_two')
tree.place(x=10,y=10)
# Barre de scroll:
scrollbar = ttk.Scrollbar(win_debug, orient="vertical", command=tree.yview)
scrollbar.place(x=10 320 3, y=10, height=225 2)
tree.configure(yscroll=scrollbar.set)
result_datas = fetch_datas("history")
result_datas_2 = fetch_datas("indic")
all_datas = result_datas_2.merge(result_datas, how="left", on="scan_id")
all_datas["datescan"] = pd.to_datetime(all_datas["datescan"],format="%d/%m/%Y %H:%M:%S")
all_datas = all_datas.sort_values(by="datescan", ascending=False)
for index,row in all_datas.iterrows():
scan_id = row["scan_id"]
date_scan = row["datescan"]
data_one = row["data_one"]
data_two = row["data_two"]
# L'attribut text correspond à la colonne #0
tree.insert("", "end", values = (scan_id, date_scan, data_one,data_two))
one_button = tkinter.Button(win_debug,text="i store in the memory",command=prepareData_and_store)
one_button.place(x=330,y=250)
Here is an image which sum up my problem: enter image description here
Here is the function i use to redefine the path due to the temporary folder generated by Py to exe:
def path_pour_wrapExe(fichier):
# pour les versions >=1.6
if hasattr(sys, '_MEIPASS'):
chdir(sys._MEIPASS)
fichier = join(sys._MEIPASS, fichier)
# Pour les versions <1.6
elif '_MEIPASS2' in environ:
chdir(environ['_MEIPASS2'])
fichier = join(environ['_MEIPASS2'], fichier)
# Pour le développement
else:
chdir(dirname(sys.argv[0]))
fichier = join(dirname(sys.argv[0]), fichier)
return fichier
I have been stuck since yesterday on this executable problem which once closed loses the data. If I can't solve this problem my program will be unusable ☹ You are my last hope!
UPDATE: It work when i choose to build my exe in One directory mode. But i need to wrap all in an only exe file !! How do to it ?
UPDATE: I used Roland Smith's solution and managed to wrap everything in one single exe: it work, thank you Roland ! here is the function to call for solving the probleme of temporary file:
user_folder = os.environ["HOMEPATH"]
engine = create_engine(f'sqlite:///C:\{user_folder}\nameofadatabase.db',echo=True)
CodePudding user response：
What PyInstaller (for which auto-py-to-exe is a front-end) does when you use "one file" mode is package your scripts, data and library in an executable program.
When you run that program, it unpacks the executable in a temporary directory and runs your script from there.
So when you run the program, it will probably update the database just fine. However (and here we have the problem) when the program is closed the temporary directory is removed, and the executable in not updated with any modified contents of the temporary folder.
Edit:
I see two possible solutions:
Use the "one folder" packaging method instead. You make the folder available to users as a ZIP-file. The user unpacks the ZIP-file into a folter and starts the program from that folder. This solves the problem because the folder is not deleted after every use. So any modified database files will stay.
If you still want to use "one file" method, create and save the database in the user's home directory. (Which should be
os.environ["HOMEPATH"]on ms-windows.)