Database Setup

Now you will build the foundation of your application: the database.

All user data, tasks, and expenses will be stored here.

You will use SQLite, which is built into Python.

Goal of This Lesson

By the end of this lesson, you will:

• create a database file
• create required tables
• connect Python to the database
• structure database code properly

Step 1: Create Database Service File

Create this file:

app/services/database.py

Step 2: Import SQLite

Add:

import sqlite3

Step 3: Create Connection Function

def connect():
    return sqlite3.connect("app.db")

This function connects your app to the database file.

If the file does not exist, it will be created.

Step 4: Create Tables Function

def create_tables():
    conn = connect()
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT UNIQUE,
        password TEXT
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        title TEXT,
        completed INTEGER
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        amount REAL,
        category TEXT
    )
    """)

    conn.commit()
    conn.close()

Table Design Overview

users

• id → unique user id
• username → must be unique
• password → stored password

tasks

• id → task id
• user_id → link to user
• title → task text
• completed → 0 or 1

expenses

• id → expense id
• user_id → link to user
• amount → money value
• category → type of expense

Step 5: Initialize Database in main.py

Open main.py and update:

from app.services.database import create_tables

def main():
    create_tables()
    print("Database ready")

if __name__ == "__main__":
    main()

Step 6: Run the Program

python main.py

Expected output:

Database ready

Result

After running:

• a file named app.db is created
• tables are created inside it

Why This Step Matters

This is the foundation of your entire application.

Everything you build next will depend on:

• these tables
• this connection
• this structure

Important Notes

• Always call commit() after changes
• Always close the connection
• Use IF NOT EXISTS to avoid errors
• Keep database logic separate from UI

Summary

You created a working SQLite database, defined tables, and connected it to your application.

Your project now has persistent storage and is ready for backend logic.