Are you tired of wondering where your money goes each month? Do you ever feel like youāre spending more than you realize? If so, youāre not alone. Many people struggle to keep track of their finances. But fear not! Here, weāll guide you through building your very own Expense Tracker App using Python and MySQL.
An expense tracker is a tool that helps individuals or businesses log, categorize, and monitor their spending habits systematically. In this tutorial, we will create an expense tracker project in Python using Tkinter and MySQL database that is both user-friendly and robust, which can help users to manage their expenses with ease.
We will combine Tkinterās GUI capabilities and MySQLās database management to create this application that can handle user authentication, add, edit, delete expenses, and display expenses report. So, grab your favorite cup of coffee and start building!
Requirements and Setup
To begin, ensure the following prerequisites are met:
Prerequisites
- Python installed on your system (version 3.6 or above).
- MySQL Database installed and configured.
- Necessary Python libraries:
- tkinter
- mysql-connector-python
Installation
Install the required MySQL connector library using pip:
pip install mysql-connector-python
MySQL Database Setup
Create a database named āexpense_trackerā:
CREATE DATABASE expense_tracker;
Create the necessary tables:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(50) NOT NULL
);
CREATE TABLE expenses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
expense_name VARCHAR(100),
amount FLOAT,
date DATE,
FOREIGN KEY (user_id) REFERENCES users(id)
);Setting Up the Database Connection
The first step involves establishing a connection between your Python program and the MySQL database. This will allow us to store and retrieve expense data efficiently. Hereās the code that accomplishes this:
import mysql.connector
class ExpenseTracker:
def __init__(self):
self.db = self.connect_to_db()
self.cursor = self.db.cursor()
self.current_user_id = None
self.login_window()
def connect_to_db(self):
try:
return mysql.connector.connect(
host="localhost",
user="root",
password="YourPasswordHere",
database="expense_tracker"
)
except mysql.connector.Error as e:
messagebox.showerror("Database Error", f"Failed to connect: {e}")
exit()This code snippet imports the mysql.connector library, which enables Python to interact with MySQL databases. The connect_to_db method establishes a connection using the provided credentials and database name. An error message will be displayed if the connection fails.
Building the Login Window
Next, weāll create a user-friendly login window where users can enter their credentials to access the Expense Tracker and login method. Hereās the code for this section:
from tkinter import *
from functools import partial
from tkinter import ttk, messagebox
def login_window(self):
self.login_screen = Tk()
self.login_screen.title("Login")
self.login_screen.geometry("460x220")
self.login_screen.config(bg="white")
self.login_screen.resizable(False, False)
Label(self.login_screen, text="Username: ", font=("consolas", 15, "bold"), bg="white").place(x=20, y=30)
Label(self.login_screen, text="Password: ", font=("consolas", 15, "bold"), bg="white").place(x=20, y=70)
self.username_entry = Entry(self.login_screen)
self.username_entry.place(x=150, y=33)
self.password_entry = Entry(self.login_screen, show="*")
self.password_entry.place(x=150, y=73)
Button(self.login_screen, text="Login", font=("consolas", 12), cursor="hand2", bg="green", fg="white",
command=self.login).place(x=70, y=120, width=100)
Button(self.login_screen, text="Register", font=("consolas", 12), cursor="hand2", bg="red", fg="white",
command=self.register_user).place(x=180, y=120, width=100)
self.login_screen.mainloop()
def login(self):
username = self.username_entry.get()
password = self.password_entry.get()
if not username or not password:
messagebox.showwarning("Input Error", "Username and password cannot be empty!")
return
query = "SELECT id FROM users WHERE username = %s AND password = %s"
self.cursor.execute(query, (username, password))
result = self.cursor.fetchone()
if result:
self.current_user_id = result[0]
messagebox.showinfo("Success", "Login successful!")
self.login_screen.destroy()
self.main_window()
else:
messagebox.showerror("Error", "Invalid username or password.")The above code creates a basic login window using the Tkinter library. It includes labels for username and password, along with entry fields for users to input their credentials. The āLoginā and āRegisterā buttons trigger corresponding methods for login verification and user registration, respectively.
The login method retrieves the āusernameā and āpasswordā from the entry fields. If both fields are filled, it attempts to insert username and password both. Upon successful insertion, a success message is displayed, login window destroys, and main window opens.
Implementing User Registration
Before users can access the expense tracking features, they need to register an account. Letās implement the functionality to create new user accounts:
def register_user(self):
username = self.username_entry.get()
password = self.password_entry.get()
if not username or not password:
messagebox.showwarning("Input Error", "All fields are required!")
return
try:
query = "INSERT INTO users (username, password) VALUES (%s, %s)"
self.cursor.execute(query, (username, password))
self.db.commit()
messagebox.showinfo("Success", "User registered successfully!")
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))This code retrieves the username and password from the entry fields. If both fields are filled, it attempts to insert the new user into the users table in the database. Upon successful insertion, a success message is displayed. If an error occurs, an error message is shown.
Creating the Main Expense Tracking Window
Once a user is logged in, the main window of the Expense Tracker will be displayed. This window will allow users to add, view, edit, and delete expenses. Hereās the code for creating the main window:
def main_window(self):
self.window = Tk()
self.window.title("Expense Tracker")
self.window.geometry("780x400")
self.window.config(bg="white")
self.window.resizable(False, False)
self.create_expense_form()
self.create_expense_table()
self.window.mainloop()This code creates a new Tkinter window and sets its title, dimensions, and background color. It then calls two methods: create_expense_form and create_expense_table.
Part 5: Creating the Expense Form
The create_expense_form method creates the input fields for adding new expenses:
def create_expense_form(self):
Label(self.window, text="Expense Name", font=("Consolas", 14), bg="white").place(x=20, y=20)
Label(self.window, text="Amount", font=("Consolas", 14), bg="white").place(x=240, y=20)
Label(self.window, text="Date", font=("Consolas", 14), bg="white").place(x=400, y=20)
self.expense_name = Entry(self.window)
self.expense_name.place(x=20, y=50)
self.amount = Entry(self.window)
self.amount.place(x=240, y=50, width=120)
self.date = Entry(self.window)
self.date.place(x=400, y=50, width=120)
Button(self.window, text="Add Expense", font=("Consolas", 10), bg="green", fg="white",
command=self.add_expense).place(x=550, y=40)
Button(self.window, text="Logout", font=("consolas", 10), bg="yellow",
command=self.logout).place(x=20, y=350)
self.show_total_expense()The above code creates labels and entry fields for the expense name, amount, and date. It also creates a button to add the expense to the database.
Part 6: Creating the Expense Table
The create_expense_table method creates a table to display the list of expenses:
def create_expense_table(self):
self.frame = Frame(self.window, bg="white")
self.frame.place(x=20, y=90, width=740, height=200)
self.columns = ('id', 'expense_name', 'amount', 'date')
scroll_x = ttk.Scrollbar(self.frame, orient=HORIZONTAL)
scroll_y = ttk.Scrollbar(self.frame, orient=VERTICAL)
self.tree = ttk.Treeview(self.frame, columns=self.columns, height=200, yscrollcommand=scroll_y.set,
xscrollcommand=scroll_x.set, selectmode="browse")
scroll_x.pack(side=BOTTOM, fill=X)
scroll_y.pack(side=RIGHT, fill=Y)
scroll_x.config(command=self.tree.xview)
scroll_y.config(command=self.tree.yview)
self.tree.heading('id', text='ID')
self.tree.heading('expense_name', text='Expense Name')
self.tree.heading('amount', text='Amount')
self.tree.heading('date', text='Date')
self.tree['show'] = 'headings'
self.tree.pack(fill=BOTH, expand=True)
self.tree.bind('<Double-1>', self.selected)
self.load_expenses()
This code creates a Frame to hold the table, defines the columns for the table, and then loads the existing expenses from the database using the load_expenses method.
Adding, Editing, and Deleting Expenses
Weāll now implement the functionality to add, edit, and delete expenses from the database.
Adding Expenses:
def add_expense(self):
expense_name = self.expense_name.get()
amount = self.amount.get()
date = self.date.get()
if not expense_name or not amount or not date:
messagebox.showerror("Input Error", "All fields are required!")
return
try:
query = "INSERT INTO expenses (user_id, expense_name, amount, date) VALUES (%s, %s, %s, %s)"
self.cursor.execute(query, (self.current_user_id, expense_name, amount, date))
self.db.commit()
messagebox.showinfo("Success", "Expense added successfully!")
self.load_expenses()
self.show_total_expense()
self.clear_expense_form()
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))This code retrieves the expense name, amount, and date from the entry fields. It then inserts a new record into the expenses table with the current userās ID, the entered expense details, and the current date. After a successful insertion, the table is reloaded, the total expense is recalculated, and the form is cleared.
Editing Expenses:
def selected(self, event):
Button(self.window, text="Edit", font=("consolas", 10), bg="green", fg="white", cursor="hand2", command=self.edit_expense).place(x=20, y=300)
Button(self.window, text="Delete", font=("consolas", 10), bg="red", fg="white", cursor="hand2", command=self.delete_expense).place(x=90, y=300)
def edit_expense(self):
x = self.tree.selection()
y = self.tree.item(x)['values']
query = "SELECT * FROM expenses WHERE id = %s"
self.cursor.execute(query, (y[0],))
row = self.cursor.fetchone()
self.clear_screen()
self.get_new_data(row)
def get_new_data(self, row):
Label(self.window, text="Expense Name", font=("Consolas", 14, "bold"), bg="white").place(x=20,y=20)
Label(self.window, text="Amount", font=("Consolas", 14, "bold"), bg="white").place(x=240,y=20)
Label(self.window, text="Date", font=("Consolas", 14, "bold"), bg="white").place(x=400,y=20)
self.new_expense_name = Entry(self.window)
self.new_expense_name.insert(END, f"{row[2]}")
self.new_expense_name.place(x=20, y=50)
self.new_amount = Entry(self.window)
self.new_amount.insert(END, f"{row[3]}")
self.new_amount.place(x=240, y=50, width=120)
self.new_date = Entry(self.window)
self.new_date.insert(END, f"{row[4]}")
self.new_date.place(x=400, y=50, width=120)
Button(self.window, text="Submit", font=("Consolas", 10), bg="green", fg="white", cursor="hand2", command=partial(self.update_expense, row)).place(x=550, y=40)
def update_expense(self, row):
if self.new_expense_name.get() == "" or self.new_amount.get() == "" or self.new_date.get() == "":
messagebox.showerror("Error!", "All fields are required", parent=self.window)
else:
query = "update expenses set expense_name=%s, amount=%s, date=%s where id=%s"
self.cursor.execute(query, (self.new_expense_name.get(), self.new_amount.get(), self.new_date.get(), row[0],))
self.db.commit()
messagebox.showinfo("Successful", "Data has been updated")
self.clear_screen()
self.create_expense_form()
self.create_expense_table()
self.clear_expense_form()The above code fetches the details of the selected expense, creates input fields to modify the details, and then updates the database with the new information.
Deleting Expenses:
def delete_expense(self):
x = self.tree.selection()
y = self.tree.item(x)['values']
response = messagebox.askokcancel("Confirm Deletion", "Are you sure you want to delete this expense?")
try:
if response:
query = "DELETE FROM expenses WHERE id = %s"
self.cursor.execute(query, (y[0],))
self.db.commit()
messagebox.showinfo("Deleted", "Expense has been deleted.")
self.clear_frame()
self.create_expense_table()
else:
messagebox.showinfo("Cancelled", "Deletion was cancelled.")
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))Deleting an expense involves confirming the deletion with the user and then executing a DELETE query to remove the selected expense from the database.
Calculating Total Expenses
def show_total_expense(self):
query = "SELECT SUM(amount) FROM expenses WHERE user_id = %s"
self.cursor.execute(query, (self.current_user_id,))
total_expense = self.cursor.fetchone()[0] or 0
Label(self.window, text=f"Total: {total_expense}", font=("consolas", 15), bg="blue",
fg="white").place(x=550, y=300)The above code calculates the total expenses by querying the database for the sum of all amounts for the current user. The result is then displayed on the main window.
Declaring Miscellaneous Methods
In this section, we will develop four different methods: clear_screen, clear_frame, clear_expense_form, logout. These methods are involved clearing main expense tracker window, the tkinter frame (where the expense table is shown), expense form, and logout the user. After logout, the main expense tracker window closes and the login window reopens.
def clear_screen(self):
for widget in self.window.winfo_children():
widget.destroy()
def clear_frame(self):
for widget in self.frame.winfo_children():
widget.destroy()
def clear_expense_form(self):
self.expense_name.delete(0, END)
self.amount.delete(0, END)
self.date.delete(0, END)
def logout(self):
self.window.destroy()
self.current_user_id = None
self.login_window()Initializing the Application
In this part of the code, we will initialize the ExpenseTracker class.
if __name__ == "__main__":
ExpenseTracker()The Complete Program
Below is the complete code for the expense tracker application.
from tkinter import *
import mysql.connector
from functools import partial
from tkinter import ttk, messagebox
class ExpenseTracker:
def __init__(self):
self.db = self.connect_to_db()
self.cursor = self.db.cursor()
self.current_user_id = None
self.login_window()
def connect_to_db(self):
try:
return mysql.connector.connect(
host="localhost",
user="root",
password="YourPasswordHere",
database="expense_tracker"
)
except mysql.connector.Error as e:
messagebox.showerror("Database Error", f"Failed to connect: {e}")
exit()
def login_window(self):
self.login_screen = Tk()
self.login_screen.title("Login")
self.login_screen.geometry("460x220")
self.login_screen.config(bg="white")
self.login_screen.resizable(False, False)
Label(self.login_screen, text="Username: ", font=("consolas", 15, "bold"), bg="white").place(x=20, y=30)
Label(self.login_screen, text="Password: ", font=("consolas", 15, "bold"), bg="white").place(x=20, y=70)
self.username_entry = Entry(self.login_screen)
self.username_entry.place(x=150, y=33)
self.password_entry = Entry(self.login_screen, show="*")
self.password_entry.place(x=150, y=73)
Button(self.login_screen, text="Login", font=("consolas", 12), cursor="hand2", bg="green", fg="white",
command=self.login).place(x=70, y=120, width=100)
Button(self.login_screen, text="Register", font=("consolas", 12), cursor="hand2", bg="red", fg="white",
command=self.register_user).place(x=180, y=120, width=100)
self.login_screen.mainloop()
def login(self):
username = self.username_entry.get()
password = self.password_entry.get()
if not username or not password:
messagebox.showwarning("Input Error", "Username and password cannot be empty!")
return
query = "SELECT id FROM users WHERE username = %s AND password = %s"
self.cursor.execute(query, (username, password))
result = self.cursor.fetchone()
if result:
self.current_user_id = result[0]
messagebox.showinfo("Success", "Login successful!")
self.login_screen.destroy()
self.main_window()
else:
messagebox.showerror("Error", "Invalid username or password.")
def register_user(self):
username = self.username_entry.get()
password = self.password_entry.get()
if not username or not password:
messagebox.showwarning("Input Error", "All fields are required!")
return
try:
query = "INSERT INTO users (username, password) VALUES (%s, %s)"
self.cursor.execute(query, (username, password))
self.db.commit()
messagebox.showinfo("Success", "User registered successfully!")
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))
def main_window(self):
self.window = Tk()
self.window.title("Expense Tracker")
self.window.geometry("780x400")
self.window.config(bg="white")
self.window.resizable(False, False)
self.create_expense_form()
self.create_expense_table()
self.window.mainloop()
def create_expense_form(self):
Label(self.window, text="Expense Name", font=("Consolas", 14), bg="white").place(x=20, y=20)
Label(self.window, text="Amount", font=("Consolas", 14), bg="white").place(x=240, y=20)
Label(self.window, text="Date", font=("Consolas", 14), bg="white").place(x=400, y=20)
self.expense_name = Entry(self.window)
self.expense_name.place(x=20, y=50)
self.amount = Entry(self.window)
self.amount.place(x=240, y=50, width=120)
self.date = Entry(self.window)
self.date.place(x=400, y=50, width=120)
Button(self.window, text="Add Expense", font=("Consolas", 10), bg="green", fg="white",
command=self.add_expense).place(x=550, y=40)
Button(self.window, text="Logout", font=("consolas", 10), bg="yellow",
command=self.logout).place(x=20, y=350)
self.show_total_expense()
def create_expense_table(self):
self.frame = Frame(self.window, bg="white")
self.frame.place(x=20, y=90, width=740, height=200)
self.columns = ('id', 'expense_name', 'amount', 'date')
scroll_x = ttk.Scrollbar(self.frame, orient=HORIZONTAL)
scroll_y = ttk.Scrollbar(self.frame, orient=VERTICAL)
self.tree = ttk.Treeview(self.frame, columns=self.columns, height=200, yscrollcommand=scroll_y.set,
xscrollcommand=scroll_x.set, selectmode="browse")
scroll_x.pack(side=BOTTOM, fill=X)
scroll_y.pack(side=RIGHT, fill=Y)
scroll_x.config(command=self.tree.xview)
scroll_y.config(command=self.tree.yview)
self.tree.heading('id', text='ID')
self.tree.heading('expense_name', text='Expense Name')
self.tree.heading('amount', text='Amount')
self.tree.heading('date', text='Date')
self.tree['show'] = 'headings'
self.tree.pack(fill=BOTH, expand=True)
self.tree.bind('<Double-1>', self.selected)
self.load_expenses()
def selected(self, a):
Button(self.window, text="Edit", font=("consolas", 10), bg="green", fg="white", cursor="hand2", command=self.edit_expense).place(x=20, y=300)
Button(self.window, text="Delete", font=("consolas", 10), bg="red", fg="white", cursor="hand2", command=self.delete_expense).place(x=90, y=300)
def edit_expense(self):
x = self.tree.selection()
y = self.tree.item(x)['values']
query = "SELECT * FROM expenses WHERE id = %s"
self.cursor.execute(query, (y[0],))
row = self.cursor.fetchone()
self.clear_screen()
self.get_new_data(row)
def get_new_data(self, row):
Label(self.window, text="Expense Name", font=("Consolas", 14, "bold"), bg="white").place(x=20,y=20)
Label(self.window, text="Amount", font=("Consolas", 14, "bold"), bg="white").place(x=240,y=20)
Label(self.window, text="Date", font=("Consolas", 14, "bold"), bg="white").place(x=400,y=20)
self.new_expense_name = Entry(self.window)
self.new_expense_name.insert(END, f"{row[2]}")
self.new_expense_name.place(x=20, y=50)
self.new_amount = Entry(self.window)
self.new_amount.insert(END, f"{row[3]}")
self.new_amount.place(x=240, y=50, width=120)
self.new_date = Entry(self.window)
self.new_date.insert(END, f"{row[4]}")
self.new_date.place(x=400, y=50, width=120)
Button(self.window, text="Submit", font=("Consolas", 10), bg="green", fg="white", cursor="hand2", command=partial(self.update_expense, row)).place(x=550, y=40)
def update_expense(self, row):
if self.new_expense_name.get() == "" or self.new_amount.get() == "" or self.new_date.get() == "":
messagebox.showerror("Error!", "All fields are required", parent=self.window)
else:
query = "update expenses set expense_name=%s, amount=%s, date=%s where id=%s"
self.cursor.execute(query, (self.new_expense_name.get(), self.new_amount.get(), self.new_date.get(), row[0],))
self.db.commit()
messagebox.showinfo("Successful", "Data has been updated")
self.clear_screen()
self.create_expense_form()
self.create_expense_table()
self.clear_expense_form()
def delete_expense(self):
x = self.tree.selection()
y = self.tree.item(x)['values']
response = messagebox.askokcancel("Confirm Deletion", "Are you sure you want to delete this expense?")
try:
if response:
query = "DELETE FROM expenses WHERE id = %s"
self.cursor.execute(query, (y[0],))
self.db.commit()
messagebox.showinfo("Deleted", "Expense has been deleted.")
self.clear_frame()
self.create_expense_table()
else:
messagebox.showinfo("Cancelled", "Deletion was cancelled.")
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))
def load_expenses(self):
for item in self.tree.get_children():
self.tree.delete(item)
query = "SELECT id, expense_name, amount, date FROM expenses WHERE user_id = %s"
self.cursor.execute(query, (self.current_user_id,))
results = self.cursor.fetchall()
for expense in results:
self.tree.insert('', 'end', values=expense)
def add_expense(self):
expense_name = self.expense_name.get()
amount = self.amount.get()
date = self.date.get()
if not expense_name or not amount or not date:
messagebox.showerror("Input Error", "All fields are required!")
return
try:
query = "INSERT INTO expenses (user_id, expense_name, amount, date) VALUES (%s, %s, %s, %s)"
self.cursor.execute(query, (self.current_user_id, expense_name, amount, date))
self.db.commit()
messagebox.showinfo("Success", "Expense added successfully!")
self.load_expenses()
self.show_total_expense()
self.clear_expense_form()
except mysql.connector.Error as e:
messagebox.showerror("Database Error", str(e))
def show_total_expense(self):
query = "SELECT SUM(amount) FROM expenses WHERE user_id = %s"
self.cursor.execute(query, (self.current_user_id,))
total_expense = self.cursor.fetchone()[0] or 0
Label(self.window, text=f"Total: {total_expense}", font=("consolas", 15), bg="blue",
fg="white").place(x=550, y=300)
def clear_screen(self):
for widget in self.window.winfo_children():
widget.destroy()
def clear_frame(self):
for widget in self.frame.winfo_children():
widget.destroy()
def clear_expense_form(self):
self.expense_name.delete(0, END)
self.amount.delete(0, END)
self.date.delete(0, END)
def logout(self):
self.window.destroy()
self.current_user_id = None
self.login_window()
if __name__ == "__main__":
ExpenseTracker()How to run the project?
- Run the Python script:
python expense_tracker.py - Register a new user.
- Log in using the registered credentials.
- Add, edit, or delete expenses as needed.
Output
Summary
In this tutorial, we developed our own Expense Tracker Application using Python. We used Tkinter library to manage GUIs of the application and MySQL for handling database related tasks. We added features like user authentication, adding, modifying, and deleting expenses, and displaying the expenses report.
Itās a great tool for managing finances while improving your Python skills. In the future, you can further enhance features like data visualization, exporting expense reports.
If you have any queries related to this project, contact me at contact@pyseek.com.
Recommended Articles:
- Create a Contact Management System Project in Python
- Create a Student Management System Project in Python
- Create a Library Management System Project in Python
- Create a Login and Registration form using Python and MySQL
Happy Coding!
