Professional Expense Tracker in Python
Summary
TLDRIn 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
📝 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.
🔍 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.
📊 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.
🔄 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.
Mindmap
Keywords
💡Expense Tracking Tool
💡Command Line
💡Python
💡Database
💡SQLite
💡SQL
💡Primary Key
💡Cursor
💡Data Type
💡User Input
💡Menu
💡Loop
💡Exception Handling
💡SQL Injection
💡Aggregation
💡Group By
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
تصفح المزيد من مقاطع الفيديو ذات الصلة
Introdução a bancos de dados SQLite com Python e módulo sqlite3
view data PHP
Learn Basic SQL in 15 Minutes | Business Intelligence For Beginners | SQL Tutorial For Beginners 1/3
How to install MongoDB 6 on Ubuntu 22.04 LTS Linux
EntityFramework Core Migrations | ASP.NET CORE | CLI Migrations | Package Manager Console Migratio
Dream Report: Acquiring Data using SQL Statements
5.0 / 5 (0 votes)