Table of Contents
- Introduction
- Prerequisites
- Setup
- Creating a Database
- Creating Tables
- Inserting Data
- Querying Data
- Updating Data
- Deleting Data
- Conclusion
Introduction
In this tutorial, we will learn how to build a budget tracker app using Python and SQLite. SQLite is a lightweight, file-based database system that allows us to store and manipulate data without setting up a separate database server. By the end of this tutorial, you will have a functional budget tracker app that can create, read, update, and delete budget entries.
Prerequisites
Before starting this tutorial, you should have a basic understanding of Python programming and database concepts. Familiarity with SQL will be helpful but is not required.
Setup
To get started, make sure you have Python installed on your machine. You can download the latest version of Python from the official website (https://www.python.org/).
Additionally, we need to install the sqlite3 module, which is included in the Python standard library. Open a terminal or command prompt and run the following command:
pip install sqlite3
Now that we have Python and the necessary module installed, we can begin building our budget tracker app.
Creating a Database
The first step is to create a SQLite database to store our budget entries. We will use the sqlite3 module to create and connect to the database. Create a new Python file called budget_tracker.py and open it in your preferred text editor.
Inside the file, import the sqlite3 module:
python
import sqlite3
Next, we need to establish a connection to the database. We can use the connect() function provided by the sqlite3 module. Add the following code to create a new database connection:
python
conn = sqlite3.connect('budget.db')
This line of code will create a new SQLite database file called budget.db if it does not already exist. If the file already exists, it will establish a connection to it.
Creating Tables
Now that we have a database connection, we can create the necessary tables for our budget tracker app. In this tutorial, we’ll create two tables: categories and expenses.
First, let’s create the categories table. Add the following code below the previous line:
python
conn.execute('''
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
)
''')
This SQL statement creates a new table with two columns: id and name. The id column will be the primary key, and the name column will store the category name as text.
Next, let’s create the expenses table. Add the following code below the previous line:
python
conn.execute('''
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category_id INTEGER NOT NULL,
amount REAL NOT NULL,
description TEXT
)
''')
This SQL statement creates a new table with four columns: id, category_id, amount, and description. The id column will be the primary key, the category_id column will reference the id column of the categories table, the amount column will store the expense amount as a real number, and the description column will store the expense description as text.
Inserting Data
Now that we have our tables created, let’s add some data to them. We will start by inserting a few categories into the categories table.
Add the following code below the previous lines: ```python categories = [(‘Groceries’,), (‘Entertainment’,), (‘Transportation’,)]
conn.executemany('INSERT INTO categories (name) VALUES (?)', categories)
conn.commit()
``` This code creates a list of tuples containing the category names. We then use the `executemany()` function to insert multiple rows into the `categories` table. Finally, we call `commit()` to save the changes to the database.
To verify that the categories have been inserted, let’s query the categories table and print the results.
```python
cursor = conn.execute(‘SELECT * FROM categories’)
for row in cursor:
print(row)
``` This code retrieves all rows from the `categories` table and prints each row. Run the script, and you should see the categories printed in the console.
Querying Data
Now that we know how to insert data, let’s learn how to query it. We will create a function that retrieves all expenses from the expenses table and displays them in a formatted manner.
Add the following code below the previous lines: ```python def display_expenses(): cursor = conn.execute(‘’’ SELECT expenses.id, categories.name, expenses.amount, expenses.description FROM expenses INNER JOIN categories ON expenses.category_id = categories.id ‘’’)
for row in cursor:
print(f'ID: {row[0]}, Category: {row[1]}, Amount: {row[2]}, Description: {row[3]}')
``` This function uses an SQL query with an `INNER JOIN` to combine data from both the `expenses` and `categories` tables. It then prints the expense details for each row.
To test the function, add the following code at the end of the script:
python
display_expenses()
Run the script, and you should see the expenses printed in the console.
Updating Data
Next, let’s learn how to update existing expenses in the expenses table.
Add the following code below the previous lines: ```python def update_expense(description, amount): conn.execute(‘UPDATE expenses SET amount = ? WHERE description = ?’, (amount, description)) conn.commit()
update_expense('Groceries', 50.0)
``` This function uses an SQL `UPDATE` statement to modify the `amount` column for a specific expense. In this example, we are updating the amount for the expense with the description 'Groceries' to $50. We then call `commit()` to save the changes.
Deleting Data
Finally, let’s learn how to delete expenses from the expenses table.
Add the following code below the previous lines: ```python def delete_expense(description): conn.execute(‘DELETE FROM expenses WHERE description = ?’, (description,)) conn.commit()
delete_expense('Entertainment')
``` This function uses an SQL `DELETE` statement to remove the expense with the specified description. In this example, we are deleting the expense with the description 'Entertainment'. We call `commit()` to save the changes.
Conclusion
In this tutorial, we have learned how to build a budget tracker app using Python and SQLite. We covered the basics of SQLite, including creating a database, creating tables, inserting data, querying data, updating data, and deleting data.
By applying the concepts learned in this tutorial, you can further enhance the budget tracker app by adding features such as user authentication, additional data validation, and graphical user interfaces.
Remember to practice and experiment with the code provided to solidify your understanding. The more you work with databases and SQL, the more comfortable you will become.
Keep exploring and building amazing applications with Python and SQLite!
Note: This tutorial is for educational purposes only. For production-grade applications, consider using a more robust database system.