Professional Expense Tracker in Python

Summary

In this tutorial video, the host guides viewers through building a command-line expense tracking tool using Python and SQLite3. The project is designed for beginners, focusing on creating a database, establishing a connection, and implementing a user interface with a menu for entering new expenses and viewing summaries. The script covers SQL statements for inserting data and querying expenses by month and category, concluding with a demonstration of the tool in action.

Takeaways

• 😀 The video is a tutorial on building an expense tracking tool in the command line using Python.
• 🛠️ The project utilizes SQLite3 for database management to store expense data persistently.
• 📝 It starts by creating a Python file named 'create_DB.py' to set up the database and table structure.
• 🔑 The table 'expenses' has columns for ID, date, description, category, and price, with ID as the primary key.
• 🔍 The script includes a main menu for the user to choose between entering a new expense or viewing expenses.
• 📅 When entering a new expense, users are prompted to input the date, description, and category, with the option to create new categories.
• 💰 The price input is handled with prepared statements to prevent SQL injection.
• 📊 There are options to view all expenses or view monthly expenses by category, providing summaries of spending.
• 🔄 The application includes a loop for continuous use, allowing users to add multiple expenses and view summaries repeatedly.
• 🔒 The connection to the database is properly closed after operations to ensure data integrity.
• 📚 The tutorial is aimed at beginners, providing a practical project to practice Python and database skills.

Q & A

What is the main purpose of the video?

The main purpose of the video is to guide viewers on how to build an expense tracking tool in the command line using Python and SQLite database, which is suitable for beginners to practice their coding skills.

What is the first step in creating the expense tracking tool?

The first step is to create a new Python file called 'create_DB' to establish a connection with the SQLite database and set up the initial database structure using SQL commands.

What does the 'expenses' table in the database consist of?

The 'expenses' table consists of columns for 'ID' as an integer and primary key, 'date', 'description' as text, 'category' as text, and 'price' as a real (decimal) data type.

How does the video script handle the user input for the date of the expense?

The script prompts the user to enter the date of the expense in the format YYYY-MM-DD, which is a four-digit year, two-digit month, and two-digit day.

What is the method used to display existing categories to the user?

The script uses a SQL query with 'SELECT DISTINCT category FROM expenses' to fetch unique categories and then enumerates them to create a menu for the user to select from or create a new category.

How does the script handle the insertion of a new expense into the database?

The script collects the date, description, category, and price from the user, uses prepared statements to avoid SQL injections, and then executes an 'INSERT INTO' statement to add the new expense to the 'expenses' table.

What are the two main options provided in the user menu for the expense tracking tool?

The two main options are to 'enter a new expense' and to 'view expenses summary', which includes viewing all expenses or monthly expenses by category.

How does the script display the summary of expenses?

The script provides two options to display the summary: viewing all expenses or viewing monthly expenses grouped by category. It uses SQL queries to fetch and display the data accordingly.

What is the purpose of using prepared statements in the script?

The purpose of using prepared statements is to securely pass user inputs into SQL queries, which helps to prevent SQL injection attacks by ensuring inputs are safely typecast and handled.

How does the script handle the user's choice to exit the tool?

The script includes an 'if' condition to check if the user's choice is invalid or if they choose to exit by entering an option that is not recognized or by answering 'no' to the prompt 'would you like to do something else?'.

What is the final step in the script after all operations are completed?

The final step is to close the database connection using 'connection.close()' to ensure all data is saved and the connection is properly terminated.

Outlines

00:00

📝 Building a Command-Line Expense Tracker in Python

The video introduces a project to create an expense tracking tool in the command line using Python and SQLite for database management. The tutorial is aimed at beginners and begins with setting up a database using a 'create_DB.py' file. The script includes creating a table with columns for ID, date, description, category, and price. The video demonstrates how to establish a connection, create a cursor, and execute a SQL statement to build the database structure. It then proceeds to show how to commit changes and close the connection, setting the foundation for persistent storage of expenses.

05:02

🔍 Implementing User Interaction for Expense Entry

The script continues with the development of the 'main.py' file, which handles user interactions. It starts by connecting to the database and setting up a menu-driven interface with options to enter a new expense or view an expense summary. The process of entering a new expense involves prompting the user for the date, description, and category of the expense. It also includes a feature to select from existing categories or create a new one by querying the database for distinct categories. The user is then asked to input the price of the expense, and the details are inserted into the database using a prepared statement to prevent SQL injection.

10:02

📊 Displaying Expense Summaries and Reports

The video script explains how to display expense summaries with two options: viewing all expenses or viewing monthly expenses by category. The code snippet shows how to execute SQL queries to fetch and display the required data. For viewing all expenses, a simple SELECT statement retrieves all records from the expenses table. For monthly expenses by category, the script uses an aggregate function to sum up the prices and groups the results by category, filtered by the specified month and year. The script assumes valid user input and demonstrates how to format and display the results to the user.

15:04

🔄 Iterative User Experience and Closing the Database Connection

The final part of the script focuses on creating an iterative user experience by asking if the user would like to perform another action after each operation. The loop continues until the user decides to exit by responding with anything other than 'yes'. The script ensures that the database connection is closed at the end of the session to maintain the integrity and performance of the application. The video concludes with a demonstration of entering multiple expenses, viewing a summary of all expenses, and generating a monthly expense report by category, showcasing the functionality of the expense tracker.

Keywords

💡Expense Tracking Tool

An expense tracking tool is a software application designed to monitor and record expenditures, helping individuals or businesses manage their finances effectively. In the video's context, the tool is being developed in the command line using Python, which involves creating a database to store expense records. The script describes the process of building this tool from setting up the database to implementing user interaction for entering and viewing expenses.

💡Command Line

The command line, also known as the terminal or shell, is a text-based interface for interacting with a computer's operating system. In the script, the command line is used as the platform for building the expense tracking tool, where Python commands are executed to create and interact with the database.

💡Python

Python is a high-level, interpreted programming language known for its readability and efficiency. It is the language of choice in the video for creating the expense tracking tool due to its simplicity and the powerful libraries it offers for database interaction and command-line user interfaces.

💡Database

A database is an organized collection of data, typically stored and accessed electronically. In the video, a SQLite database is created to persistently store the expense records. The script explains how to establish a connection with the database and execute SQL statements to create a table and manage expense data.

💡SQLite

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. It is used in the video to demonstrate how to create a simple database for the expense tracker without the need for a separate database server.

💡SQL

SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system. The script includes SQL statements such as 'CREATE TABLE' for setting up the database schema and 'SELECT' queries for retrieving data.

💡Primary Key

In a database table, a primary key is a unique identifier for each record. The script mentions that the 'ID' field in the expenses table is an integer and serves as the primary key, ensuring that each expense entry can be uniquely identified.

💡Cursor

In the context of database programming, a cursor is a control structure that enables traversal over the rows of a result set. The script uses a cursor to execute SQL commands and interact with the database, such as creating the expenses table and inserting new expense records.

💡Data Type

A data type defines the type of value a variable can hold, such as integer, text, or real (decimal). The script specifies different data types for the columns in the expenses table, like 'INTEGER' for the ID, 'TEXT' for the description and category, and 'REAL' for the price.

💡User Input

User input refers to the data provided by a user through various means, such as command-line prompts. The script describes how the expense tracking tool collects input from the user for the date, description, category, and price of an expense, which are then stored in the database.

💡Menu

A menu in a command-line application is a list of options presented to the user to select a particular action. The script outlines a simple menu system for the expense tracker, offering choices like entering a new expense or viewing a summary of expenses.

💡Loop

In programming, a loop is a sequence of instructions that is continually repeated until a certain condition is reached. The script uses a 'while true' loop to create an endless loop for the main menu of the expense tracker, allowing the user to continuously interact with the tool until they choose to exit.

💡Exception Handling

Exception handling is a programming technique for responding to unwanted or exceptional events that require special processing. Although the script mentions it, it does not delve into implementing exception handling for the expense tracker, suggesting that the focus is on the core functionality rather than robust error checking.

💡SQL Injection

SQL injection is a code injection technique that might destroy the integrity of a database. The script briefly mentions the use of prepared statements with placeholders (question marks) to avoid SQL injection vulnerabilities when inserting data into the database.

💡Aggregation

In SQL, aggregation is the process of combining a set of values into a single summary value, such as SUM, AVG, MIN, or MAX. The script uses the SUM function to aggregate the total price per category for expenses, providing a way to view the total expenses per category for a given month and year.

💡Group By

The GROUP BY clause in SQL groups rows that have the same values in specified columns into aggregated data. The script uses the GROUP BY clause to organize the expenses by category, allowing the user to view the total expenses per category for a specific time period.

Highlights

Introduction of building an expense tracking tool in the command line using Python and SQLite database.

Creating a new Python file 'create_DB.py' for setting up the database connection.

Using SQLite3 to establish a connection and create a cursor for database operations.

Executing a SQL statement to create an 'expenses' table with specified columns.

Committing changes to the database and closing the connection.

Creating the main Python file 'main.py' for the expense tracking application logic.

Designing a basic menu for user interaction with options to enter expenses and view summaries.

Implementing functionality to enter a new expense with prompts for date, description, category, and price.

Using SQL's 'distinct' to list unique categories for expense categorization.

Allowing users to select an existing category or create a new one for expenses.

Using prepared statements to insert new expense data into the database securely.

Providing options to view all expenses or monthly expenses by category.

Using SQL aggregation functions to sum up expenses per category for monthly reports.

Demonstrating the application flow with sample data entry and retrieval.

Closing the database connection at the end of the application run.

Encouraging viewers to like, comment, subscribe, and turn on notifications for future videos.

Transcripts

what is going on guys welcome back in

today's video we're going to build an

expense tracking tool in the command

line using python which will also make

use of a database making it a perfect

project for beginners to practice their

skills so let us get right into it

[Music]

all right so let us get started with a

database first and for that we're going

to create a new python file which we're

going to call create underscore DB and

we're going to keep it simple here we're

just going to import sqlite 3 and we're

going to establish a connection by

saying sqlite3 connect

expense dot DB or expenses dot b

and then we're going to say that we want

to have a cursor which is going to be a

connection.cursor and we just want to

execute a simple create statement so

cursor.execute and we're going to use a

multi-line string here in which we're

going to say create

up here create

table if not exists expenses

and as I said we're going to keep it

simple so we're going to say we have an

ID which is an integer and a primary key

we're going to have a date which is

going to be a date we're going to have a

description

so what was the money spent on which is

going to be a text then we're going to

also have a category which is going to

be a text as well and then we're going

to have the actual amount so the actual

price which is going to be a real which

is the decimal data type if you want in

um

in sqlite3 and we're going to of course

close

this bracket here so this is our

structure and in order to create this

database we now say connection dot

commit to commit the changes and then

connection.close

to wrap everything up and once we run

this you can see we have this expenses

database here I can double click it here

in pycharm I can open it I can connect

to it and then I can say select

everything from expenses

and when I run this you can see that we

have the correct structure down here ID

date description category price

um and this is our basis here this is

where we're going to store the expenses

this is where we're going to get the

expenses from so that we have a

persistent storage across the individual

sessions and now we're going to start

with the main file so we're going to

create this main.py file in here we're

also going to import sqli3 because we

need to connect to the database and

we're also going to import date time

so first we're going to again connect to

the database so sqlite3 connect expenses

DB and we're going to create a cursor

connection.cursor this is going to be

our basis for the individual actions and

then we're going to have this basic menu

or main Loop if you want to call it that

we're going to say while true so

basically an endless loop we're going to

print the following select an option and

we're going to provide the user with

different options

we're going to say one enter a new

expense

and 2 is going to be view expenses

summary or something

and then we're going to say okay if the

choice or actually we're going to say

Choice equals

the integer of the input

and we're going to say if the choice is

equal to 1 we're going to do one thing

and otherwise alif the choice is equal

to 2 we're going to do another thing and

else of the choice is invalid we're just

going to exit

um and let's go ahead and Implement now

the first section here which is entering

a new expense now to do this we need

some information from the users so what

date was the expense on uh what was the

money spent on and so on

so we're going to say here first of all

that the date is going to be input and

we're going to prompt here enter the

date of the expense and we're going to

provide the format here year year year

so four digits for the year two for the

month and two for the day

and then we're going to do the same

thing with the description but without a

format obviously so description is going

to be input

enter the

description of the expense and we're

going to get rid of this format here

um and then we can basically uh what we

want to do is we want to be able to add

a category but we want to also be able

to choose an existing category so what

we want to do here is we don't want to

just ask the user for a string like give

me the category of this purchase but we

want to say okay here are the categories

you already have if you want to choose

one of those enter a number otherwise

you can create a new one so what we want

to do here is we want to use the cursor

to execute the following statement we

want to say select and then uh distinct

category

from expenses and what this does

essentially distinct means that we want

to have only the unique values so in the

beginning we won't have any categories

in the database so we're only going to

be able to create a new one

um but

um over time if we have a 100 entries

but we have only three different

categories across all these entries it

will show us only those three distinct

categories this is the the functionality

of the distinct statement here and we

want to get this we want to get the

results categories by saying cursor dot

fetch all

and then we're going to iterate over the

categories that we have and we're going

to also enumerate them so that we have

an automatic menu so we're going to say

here for or actually want to say first

select a category

by number or something

and then we're going to say four index

category in enumerate

categories we're going to print

an F string here with the index a DOT or

actually an index plus one because we

want to have options one two whatever

and not zero

um zero to whatever so we're going to

say here Dot and then the category

and we want to print

uh the category zero

so the actual category name

that is that and in the end we want to

also print here

the following we want to print an F

string we're going to have length of the

categories

plus one to add an additional object

here and this object is going to be

create a new category this is going to

allow us to also have the final option

of if you don't like all these

categories you can create a new one

and in order to do or in order to see

what the user wants to do we're going to

now say the category choice

is going to be equal to input

and then if

category choice

equals

length of the categories if that is the

case uh actually plus one this is what

we did up here so if we have this

particular choice what we're going to do

is we're going to create a new category

so the category is going to be equal to

enter the new category name

and otherwise if that is not the case

provided we have a valid um input we're

just going to assume now that the user

is not going to enter a number that does

not exist but provided that that is the

case provided that the input is valid

we're going to say category equals

categories category Choice minus one and

from that index 0 so the actual category

um and for this we actually need to make

this an integer

otherwise it won't work

there you go now you could also as I

said

um check if that is less than length of

categories because otherwise you would

say invalid solution but we're not going

to take care of all the edge cases of

all the exceptions that can arise

because that would just not focus too

much on the essence here but

theoretically if you want to do that

exception handling you would just have

to say elif category choice is less than

or less or equal to the length of

categories and then process it otherwise

and of course above zero it's also

important and otherwise say that it's

invalid in exit the script but you would

have to do that on a bunch of occasions

here so as I said we're not going to do

that

uh once we have that we're going to ask

for the price so the price is going to

be input

enter the price of the expense

and once all of this is done we're going

to say cursor

dot execute and we're going to insert

into

expenses

the dates the description

the category

the price

the following values

which are going to be we're going to use

a secure prepare a prepared statements

so question marks

to avoid any SQL injections

and here we're going to pass the dates

the description the category and the

price

[Applause]

and it's fine to pass the string because

it's going to automatically be Typecast

into a date or into a price that's not a

problem and finally we just say

connection dot commit to commit the

changes and that's it for the choice one

for the choice two to actually display

the summary to actually display the

expenses we're going to keep it simple

we're going to have two options so

select an option

and the two options are going to be one

view all expenses

and then two view monthly expenses by

category and of course you can add more

um customized options like weekly

expenses or yearly expenses or all

expenses by category I'm just going to

use those two and I think if you follow

along with the code you will be able to

change them or add additional options

here

so we're going to see here the view

choice is going to be equal to input

um

yeah actually just to input we don't

need to ask for anything specific here

and we're going to again turn this into

an integer

and we're going to say

if view Choice equals to one

is going to be one thing and LF view

Choice equals to 2 is going to be

another thing in this case I'm going to

once again add an else with an exit just

because it's simple and we don't need to

do too much to prevent

are wrong input being processed and for

the first option we just have to view

everything so we're going to say cursor

dot execute select everything from

expenses

that's quite simple and then we're going

to say expenses

is going to be cursor.fetch all and then

four expense

in expenses we can just print the full

expense

that's quite simple and if we want to

have it by month and by category we're

going to get the month and the category

so we're going to ask first of all

input

enter the month mm

[Applause]

then we also want to know which year of

course because I cannot just say January

I need to know January 2021 for example

so enter the year as well

with four years

and then we're going to also say

um

actually we don't need to enter the

category because we're going to list all

the categories but we're going to group

by category that's what we want to do

here so we're going to say here now

cursor.execute

and we're going to say select category

sum of price so we're going to aggregate

here in the SQL statement so we're going

to say category the sum of the price

column from expenses there's now a

little uh SQL magic here

if you're a beginner this might be magic

otherwise it's quite simple uh where

where the not actually the category

sorry with the string format of the time

strf time

we're going to get the month so percent

M actually we need to use single

quotations here

to make this happen

so month off the date

is going to be equal to a question mark

again a prepared statement we're going

to inject later on

um and strf time year of the date here

has to be

also equal to a question mark

maybe can I can I maybe do this on a new

line there you go this should work or

maybe let's do it do it as a multi-line

string

to have a better overview here

there you go

and actually not ants this was equal to

another question mark and then we have

the statement Group by category

so basically what we do is we select the

category and the price sum for this

category we grouped by the category so

we sum up the price column for each

category where the month is equal to

something and the year is equal to

something and this something will now be

passed here as a tuple month and year

that were entered here again we're not

gonna check if the input is correct

we're just going to assume that the user

enters something valid

um and then as a result we're going to

say expenses equals cursor dot fetch all

four expense

in expenses we're going to print an F

string

where the category

is going to be

expense zero because that's the first

thing that we ask for here

and then the total is going to be

expense one which is the second thing

the sum of the price per category

uh that's it and once we have that all

we need to do here is after this

um

after all of this here we're going to

say

repeat equals input

would you like to do something else

and we can say yes or no

[Applause]

maybe you want to add

backslash in

and then we can say if repeat

dot lower is equal to yes we're going oh

actually it's not equal to yes sorry

if it's not equal to yes we're going to

break out of the loop otherwise we're

just going to repeat so if we enter a no

or an N or anything else but a yes so

everything else but a y we're going to

break out of the loop otherwise we're

going to repeat the process and in the

end we want to close the connection

connection Dot close

so if we didn't do any mistakes here or

if I didn't do any mistakes here

this should work we should be able to

enter a new expense let's say we want to

enter it 2020

January 12th

into the description uh I don't know

bananas

the category we're going to create a new

one it's going to be food and the price

I bought quite a lot so 150 dollars

would you like to do something else yes

I want to enter a new expense 2020

January 5th

I want to enter uh I don't know a

skateboard that I bought is going to be

you can see I can choose now food or I

can create a new category I will name it

fun and then I will say 300 and then I

would like to add one more

enter a new expense or actually two more

because I want to show some stuff here

I'm going to enter one more in 2020

January let's say 27th

it's going to be uh groceries

it's going to be food so I'm going to

just pick one

and it's going to be 120 and then I

would like to do one more thing enter a

new expense let's say 20 21 February

10th

and then this is going to be I don't

know again bananas

food

twenty dollars

and I would like to do something else I

would like to view the expensive summary

now so view all expenses there you go we

can see a list of all the all the stuff

that we have here and I would like to do

something else I would like to also show

monthly expenses by category the year of

the month is going to be January so 0 1

and the year is going to be 2020 and you

can see now here we have category food a

total of 270 and category final total of

300. so this is how you build a simple

expense tracker in Python so that's it

for today's video I hope you enjoyed it

and hope you learned something if so let

me know by hitting a like button and

leaving a comment in the comment section

down below and of course don't forget to

subscribe to this Channel and hit the

notification Bell to not miss a single

future video for free other than that

thank you much for watching see you in

the next video and bye

[Music]

thank you