code for logic:
#logic.py
# Import required modules
import openpyxl
import pymysql
import datetime
import time
import os
from tkinter import filedialog, messagebox, Toplevel
from tkinter import *
from dotenv import load_dotenv
from pathlib import Path
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
import matplotlib.pyplot as plt
from matplotlib.figure import Figure
from auth.login import username
# Specify the path to the .env file
env_path = Path('.') / 'key' / '.env'
# Load the .env file
load_dotenv(dotenv_path=env_path)
# Load variables from .env
hosts = os.environ.get('DB_HOST')
user = os.environ.get('DB_USER')
passwords = os.environ.get('DB_PASS')
bases = os.environ.get('DB_NAME')
users = os.environ.get('DB_USERS')
data = os.environ.get('DB_DATA')
def db_connect(root,table):
global mycursor, con
try:
con = pymysql.connect(host=hosts, user=user, password=passwords, database=bases)
mycursor = con.cursor()
except Exception as e:
messagebox.showerror('Error', f'Error connecting to database: {str(e)}', parent=root)
return
# Check if the database exists
mycursor.execute("SHOW DATABASES")
databases = [db[0] for db in mycursor.fetchall()]
if bases not in [db.lower() for db in databases]:
mycursor.execute(f'CREATE DATABASE {bases}')
mycursor.execute(f'USE {bases}')
# Check if the table exists
mycursor.execute("SHOW TABLES")
tables = [table[0] for table in mycursor.fetchall()]
if data not in tables:
query = (f"CREATE TABLE {data} ("
"id INT AUTO_INCREMENT PRIMARY KEY, "
"username VARCHAR(255), "
"acct VARCHAR(255), "
"date DATE, "
"check_num VARCHAR(5), "
"description VARCHAR(255), "
"debit DECIMAL(10, 2), "
"credit DECIMAL(10, 2), "
"status VARCHAR(20), "
"balance DECIMAL(15, 2), "
"classification VARCHAR(50))")
mycursor.execute(query)
messagebox.showinfo('Success', 'Database Connection is successful', parent=root)
auto_refresh(2000,root,table)
return True
def execute_db_query(query, params=()):
if con and mycursor:
try:
mycursor.execute(query, params)
con.commit()
return mycursor.fetchall()
except Exception as e:
messagebox.showerror('Error', f'Error executing query: {str(e)}')
return None
def add_Purchase(dateEntry, descrEntry,amountEntry,classEntry,entryScreen,root):
if not (dateEntry.get() and descrEntry.get() and amountEntry.get() and classEntry.get()):
messagebox.showerror('Error', 'All Fields are required', parent=entryScreen)
return
query = (f'INSERT INTO {data} (username, date, description, debit, credit, classification, acct, check_num, status, balance) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)')
params = (username, dateEntry.get(), descrEntry.get(), amountEntry.get(), 0, classEntry.get(), 'n/a', 'n/a', 'n/a', 0)
execute_db_query(query, params)
#Another entry
result = messagebox.askyesno('Confirm', 'Data added successfully. Do you want to clean the form?', parent=entryScreen)
if result:
dateEntry.delete(0, END)
descrEntry.delete(0, END)
amountEntry.delete(0, END)
classEntry.delete(0, END)
update_table_from_database()
pass
def update_table_from_database(root,table):
try:
query = (f'SELECT date, description, IFNULL(credit, 0) - IFNULL(debit, 0) as amount, balance, classification FROM {data} WHERE username = %s')
rows = execute_db_query(query, (username,))
if rows is not None:
for record in table.get_children():
table.delete(record)
for row in rows:
table.insert("", "end", values=row)
except Exception as e:
messagebox.showerror('Error', f'Error fetching data from the database: {str(e)}', parent=root)
pass
def auto_refresh(interval, root,table):
update_table_from_database(root, table)
root.after(interval, lambda: auto_refresh(interval, root, table))
def search_Data(dateEntry, descrEntry,amountEntry,classEntry,entryScreen,table):
try:
#Seacrch based on
query = (f'SELECT date, description, IFNULL(debit, 0) - IFNULL(credit, 0) as amount, balance, classification FROM {data} WHERE username = %s')
params = [username]
#check which thing
if dateEntry.get():
query += " AND date = %s"
params.append(dateEntry.get())
if descrEntry.get():
query += " AND description LIKE %s"
params.append(f"%{descrEntry.get()}%")
if amountEntry.get():
query += " AND (credit = %s OR debit = %s)"
params.extend([amountEntry.get(), amountEntry.get()])
if classEntry.get():
query += " AND classification = %s"
params.append(classEntry.get())
# Execute the query
rows = execute_db_query(query, tuple(params))
# Clear current contents of the table
for record in table.get_children():
table.delete(record)
# Insert the new records into the table
if rows is not None:
for row in rows:
table.insert("", "end", values=row)
except Exception as e:
messagebox.showerror('Error', f'Error fetching data from the database: {str(e)}', parent=entryScreen)
pass
def remove_Purchase(table , root):
try:
selected_items = table.selection()
for selected_item in selected_items:
values = table.item(selected_item, 'values')
if len(values) < 2:
continue
# Extract values from the selected row
selected_date, selected_description, _, _, _ = values
# Check if records exist before deleting
query = (f'SELECT * FROM {data} WHERE username = %s AND date = %s AND description = %s')
params = (username, selected_date, selected_description)
rows = execute_db_query(query, params)
if not rows:
#messagebox.showinfo('Info', 'No records found to delete.', parent=root)
continue
# Delete the record
query = (f'DELETE FROM {data} WHERE username = %s AND date = %s AND description = %s')
params = (username, selected_date, selected_description)
execute_db_query(query, params)
# Update the table to reflect the changes
update_table_from_database()
messagebox.showinfo('Success', 'Record deleted successfully.', parent=root)
except Exception as e:
messagebox.showerror('Error', f'Error deleting record: {str(e)}', parent=root)
pass
def display_Data(root):
try:
# Fetch time-series data for line chart
query = (f'SELECT date, IFNULL(credit, 0) - IFNULL(debit, 0) as amount FROM {data} WHERE username = %s ORDER BY date')
mycursor.execute(query, (username,))
rows = mycursor.fetchall()
dates = [row[0] for row in rows]
amounts = [row[1] for row in rows]
# Fetch data for bar chart and pie chart
query = (f'SELECT classification, SUM(IFNULL(credit, 0) - IFNULL(debit, 0)) as amount FROM {data} WHERE username = %s GROUP BY classification')
mycursor.execute(query, (username,))
categories = mycursor.fetchall()
#Filter out categories with negative or zero amounts
filtered_categories = [(label, value) for label, value in categories if value > 0]
if not filtered_categories:
raise ValueError("No positive values to plot.")
labels = [category[0] for category in filtered_categories]
values = [category[1] for category in filtered_categories]
displayWindow = Toplevel(root)
displayWindow.title('Spending Display')
# Create matplotlib Figure and three subplots
fig = Figure(figsize=(15, 5), dpi=100)
ax1 = fig.add_subplot(131)
ax2 = fig.add_subplot(132)
ax3 = fig.add_subplot(133)
# Line Chart
ax1.plot(dates, amounts)
ax1.set_title('Amount Spent Over Time', fontsize=12)
ax1.set_xlabel('Date', fontsize=10)
ax1.set_ylabel('Amount', fontsize=10)
ax1.tick_params(axis='both', which='major', labelsize=8)
for tick in ax1.get_xticklabels():
tick.set_rotation(90) # Rotate x-axis text labels for Line Chart
# Bar Chart
ax2.bar(labels, values)
ax2.set_title('Spending Per Category', fontsize=12)
ax2.set_xlabel('Category', fontsize=10)
ax2.set_ylabel('Amount', fontsize=10)
ax2.tick_params(axis='both', which='major', labelsize=8)
for tick in ax2.get_xticklabels():
tick.set_rotation(90) # Rotate x-axis text labels for Bar Chart
# Pie Chart
if any(v <= 0 for v in values):
raise ValueError("All values must be positive for a pie chart.")
ax3.pie(values, labels=labels, autopct='%1.1f%%')
ax3.set_title('Spending by Category', fontsize=12)
# Embed the matplotlib Figure in the Tkinter window
canvas = FigureCanvasTkAgg(fig, master=displayWindow) # A tk.DrawingArea.
canvas.draw()
canvas.get_tk_widget().pack(side='top', fill='both', expand=1)
except Exception as e:
messagebox.showerror('Error', f'Error fetching data for display: {str(e)}', parent=root)
pass
def load_Data(root):
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx"), ("CSV files", "*.csv"), ("All files", "*.*")])
if file_path:
try:
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active
for row in sheet.iter_rows(min_row=2, values_only=True):
if all(cell is None for cell in row):
continue
acct_number = row[0] or "Unknown"
# Convert date format
if isinstance(row[1], datetime.datetime):
date = row[1]
elif row[1]:
date = datetime.datetime.strptime(row[1], '%m/%d/%Y').date()
else:
date = datetime.date.today()
check_value = row[2] or None
description = row[3] or "Unknown"
debit = row[4] or 0
credit = row[5] or 0
status = row[6] or "Unknown"
balance = row[7] or 0
classification = row[8] or "Uncategorized"
# Check duplicates
check_query = f"SELECT * FROM {data} WHERE username = %s AND date = %s AND description = %s"
check_values = (username, date, description)
mycursor.execute(check_query, check_values)
existing_record = mycursor.fetchone()
if existing_record:
continue # Skip this record if it already exists
query = (f'INSERT INTO {data} (username, acct, date, check_num, description, debit, credit, status, balance, classification) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)')
values = (username, acct_number, date, check_value, description, debit, credit, status, balance, classification)
mycursor.execute(query, values)
con.commit()
messagebox.showinfo('Success', 'Data loaded from Excel file and inserted into the database', parent=root)
except Exception as e:
messagebox.showerror('Error', f'Error loading data and inserting into the database: {str(e)}', parent=root)
pass
def export_Data(table, root):
saveas= filedialog.asksaveasfilename(defaultextension='.xlsx')
if not saveas:# if user cancelled
return
tab=table.get_children()
savelist=[]
#store the data from the table
for i in tab:
data=table.item(i)
datas=data['values']
savelist.append(datas)
#worksheet to add into
workbook = openpyxl.Workbook()
sheet = workbook.active
# Write first row
headers = ['Date', 'Description', 'Amount', 'Balance', 'Classification']
for col_num, header in enumerate(headers, 1):
sheet.cell(row=1, column=col_num, value=header)
# Write all the data from the table into the sheets
for row_num, row_data in enumerate(savelist, 2):
for col_num, cell_data in enumerate(row_data, 1):
sheet.cell(row=row_num, column=col_num, value=cell_data)
# Save to the specified file
workbook.save(saveas)
messagebox.showinfo('Success', 'Data exported successfully!', parent=root)
pass
def Exit(root):
result=messagebox.askyesno('Confirm','Do you want to exit?')
if result:
root.destroy()
mycursor.close()
con.close()
else:
pass
code for interface:
#interface.py
# Import required modules
from tkinter import *
import tkinter as tk
import time
from tkinter import ttk, filedialog, Toplevel
from tkcalendar import Calendar, DateEntry
from core.logic import (db_connect, execute_db_query, add_Purchase,
search_Data, remove_Purchase, update_table_from_database,
load_Data, export_Data, display_Data, Exit)
global dateEntry,descrEntry,amountEntry,classEntry,entryScreen,root, table
def entryAll(title, button, command):
entryScreen= Toplevel()
entryScreen.title(title)
entryScreen.grab_set()
entryScreen.resizable(False, False)
dateLabel= Label(entryScreen, text='Date', font=('times new roman', 20, 'bold'))
dateLabel.grid(row=0, column=0, padx=30, pady=15, sticky=W)
dateEntry = DateEntry(entryScreen, font=('roman', 15, 'bold'), width=24,date_pattern='y/m/d')
dateEntry.grid(row=0, column=1, pady=15, padx=10)
descrLabel = Label(entryScreen, text='Description', font=('times new roman', 20, 'bold'))
descrLabel.grid(row=1, column=0, padx=30, pady=15, sticky=W)
descrEntry = Entry(entryScreen, font=('roman', 15, 'bold'), width=24)
descrEntry.grid(row=1, column=1, pady=15, padx=10)
amountLabel = Label(entryScreen, text='Amount', font=('times new roman', 20, 'bold'))
amountLabel.grid(row=2, column=0, padx=30, pady=15, sticky=W)
amountEntry = Entry(entryScreen, font=('roman', 15, 'bold'), width=24)
amountEntry.grid(row=2, column=1, pady=15, padx=10)
classLabel = Label(entryScreen, text='classification', font=('times new roman', 20, 'bold'))
classLabel.grid(row=3, column=0, padx=30, pady=15, sticky=W)
classEntry = Entry(entryScreen, font=('roman', 15, 'bold'), width=24)
classEntry.grid(row=3, column=1, pady=15, padx=10)
student_button = ttk.Button(entryScreen, text=button, command=command)
student_button.grid(row=7, columnspan=2, pady=15)
pass
def clock():
date = time.strftime('%d/%m/%Y')
Time = time.strftime('%H:%M:%S')
datetimelabel.configure(text=f' Date: {date}\nTime: {Time}')
datetimelabel.after(1000, clock)
##lettering
counter = 0
text= ''
def lettering():
global text, counter
if counter == len(s):
counter =0
text = ''
text = text+s[counter]
letteringLabel.configure(text=text)
counter+= 1
letteringLabel.after(300, lettering)
root = Tk()
style = ttk.Style(root) #ttkk style
root.tk.call("source", "C:/financeapp/theme/forest-light.tcl")
root.tk.call("source", "C:/financeapp/theme/forest-dark.tcl")
style.theme_use("forest-light")
root.geometry('1200x680+0+0')
root.resizable(0,0)
root.title('Finance Tracker')
datetimelabel = Label(root, font=('times new roman', 18, 'bold'))
datetimelabel.place(x=5, y=5)
frame = Frame(root)
frame.pack()
clock() # Start the clock function to update the time
##Display the application name with a slider
s='Finance Tracker'
letteringLabel= Label(root,font=('arial', 28, 'italic bold'), width= 30)
letteringLabel.place(x=200, y= 0)
lettering()
def enable_buttons():
addPurchase['state'] = NORMAL
searchData['state'] = NORMAL
removePurchase['state'] = NORMAL
displayData['state'] = NORMAL
loadData['state'] = NORMAL
exportData['state'] = NORMAL
Connect['state'] = DISABLED
def new_db_connect():
conn = db_connect(root,table)
if conn:
enable_buttons()
Connect = Button(root, text="Connect Database", command=new_db_connect)
Connect.place(x=900,y=0)
options= Frame(root)
options.place(x=50,y=80, width=300, height=600)
logo3= PhotoImage(file='C:/financeapp/image/Flogo.png')
logo3_label= Label(options, image=logo3)
logo3_label.grid(row=0, column = 0)
addPurchase = Button(options, text='Add transaction', width=25, state=DISABLED, command=lambda :entryAll('Add Purchase','Add', lambda: add_Purchase(dateEntry.get(), descrEntry.get(), amountEntry.get(), classEntry.get(), entryScreen, root)))
addPurchase.grid(row=1, column =0, pady=20)
searchData= Button(options, text='Search transactions', width=25, state=DISABLED, command=lambda : entryAll('Search Data', 'Search',lambda: search_Data(dateEntry.get(), descrEntry.get(), amountEntry.get(), classEntry.get(), entryScreen,table)))
searchData.grid(row=2, column =0, pady=20)
removePurchase = Button(options, text='Remove transaction', width=25, state=DISABLED, command= lambda: remove_Purchase(table, root))
removePurchase.grid(row=3, column =0, pady=20)
displayData= Button(options, text='Display Spending', width=25, state=DISABLED, command=lambda: display_Data(root))
displayData.grid(row=4, column =0, pady=20)
loadData = Button(options, text='Load from external', width=25, state=DISABLED, command= lambda: load_Data(root))
loadData.grid(row=5, column =0, pady=20)
exportData = Button(options, text='Export data', width=25, state=DISABLED,command=lambda: export_Data(table,root))
exportData.grid(row=6, column =0, pady=20)
exitButton = Button(options, text='Exit', width=25, command=lambda: Exit(root))
exitButton.grid(row=7, column =0, pady=20)
rightFrame= Frame(root)
rightFrame.place(x=350,y=80, width=820, height=600)
scrollBarX= Scrollbar(rightFrame, orient=HORIZONTAL)
scrollBarY= Scrollbar(rightFrame, orient=VERTICAL)
table=ttk.Treeview(rightFrame, columns=('Date', 'Description',
'Amount','Balance', 'Classification'),
xscrollcommand=scrollBarX.set, yscrollcommand=scrollBarY.set)
scrollBarX.config(command=table.xview)
scrollBarY.config(command=table.yview)
scrollBarX.pack(side=BOTTOM, fill= X)
scrollBarY.pack(side=RIGHT, fill= Y)
table.pack(fill=BOTH, expand=1)
table.heading('Date', text='Date')
table.heading('Description', text='Description')
table.heading('Amount', text='Amount')
table.heading('Balance', text='Balance')
table.heading('Classification', text='Classification')
table.config(show='headings')
root.mainloop()
error:
File "C:\Financeapp\src\core\interface.py", line 117, in <lambda>
addPurchase = Button(options, text='Add transaction', width=25, state=DISABLED, command=lambda :entryAll('Add Purchase','Add', lambda: add_Purchase(dateEntry.get(), descrEntry.get(), amountEntry.get(), classEntry.get(), entryScreen, root)))
^^^^^^^^^
NameError: name 'dateEntry' is not defined
I was trying to a add record/purchase
>Solution :
For variables defined inside a function to be accessible globally, you must put the line with the global keyword inside the respective function.
You probably meant the following:
def entryAll(title, button, command):
global dateEntry,descrEntry,amountEntry,classEntry,entryScreen,root, table
entryScreen= Toplevel()
..instead of the following:
global dateEntry,descrEntry,amountEntry,classEntry,entryScreen,root, table
def entryAll(title, button, command):
entryScreen= Toplevel()
P.S.: These kind of questions don’t really fit Stack Overflow because it’s a type of a logical error which would be hard (if not impossible) for users doing the same mistake to find it. Some people will probably downvote your question