Building a Developer Productivity Tracker in Python with SQLite and Matplotlib#

1
import sqlite3
2
import datetime
3

4
def create_database(db_name="productivity_tracker.db"):
5
    """Creates the SQLite database and the tasks table if they don't exist."""
6
    conn = sqlite3.connect(db_name)
7
    cursor = conn.cursor()
8
    cursor.execute('''
9
        CREATE TABLE IF NOT EXISTS tasks (
10
            id INTEGER PRIMARY KEY AUTOINCREMENT,
11
            task_description TEXT NOT NULL,
12
            category TEXT,
13
            start_time TEXT NOT NULL,
14
            end_time TEXT
15
        )
16
    ''')
17
    conn.commit()
18
    conn.close()
19
    print(f"Database '{db_name}' and table 'tasks' ensured.")
20

21
# Example usage:
22
# create_database()

1
import sqlite3
2
import datetime
3

4
DB_NAME = "productivity_tracker.db"
5

6
def start_task(description, category="General"):
7
    """Records the start time of a new task."""
8
    conn = sqlite3.connect(DB_NAME)
9
    cursor = conn.cursor()
10
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
11

12
    # End any potentially ongoing task first (simple approach for personal use)
13
    cursor.execute("UPDATE tasks SET end_time = ? WHERE end_time IS NULL", (now,))
14

15
    cursor.execute("INSERT INTO tasks (task_description, category, start_time, end_time) VALUES (?, ?, ?, NULL)",
16
                   (description, category, now))
17
    conn.commit()
18
    conn.close()
19
    print(f"Started task: '{description}' ({category}) at {now}")
20

21
def end_task():
22
    """Records the end time of the currently active task."""
23
    conn = sqlite3.connect(DB_NAME)
24
    cursor = conn.cursor()
25
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
26

27
    # Find the latest task that is still ongoing (end_time IS NULL)
28
    cursor.execute("UPDATE tasks SET end_time = ? WHERE end_time IS NULL", (now,))
29

30
    if cursor.rowcount == 0:
31
        print("No active task found to end.")
32
    else:
33
        conn.commit()
34
        print(f"Ended active task at {now}")
35

36
    conn.close()
37

38
def list_tasks(limit=10):
39
    """Lists the most recent tasks."""
40
    conn = sqlite3.connect(DB_NAME)
41
    cursor = conn.cursor()
42
    cursor.execute("SELECT id, task_description, category, start_time, end_time FROM tasks ORDER BY start_time DESC LIMIT ?", (limit,))
43
    tasks = cursor.fetchall()
44
    conn.close()
45

46
    if not tasks:
47
        print("No tasks recorded yet.")
48
        return
49

50
    print("\n--- Recent Tasks ---")
51
    for task in tasks:
52
        task_id, desc, cat, start, end = task
53
        duration = "Ongoing"
54
        if end:
55
            start_dt = datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
56
            end_dt = datetime.datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
57
            duration_td = end_dt - start_dt
58
            duration = str(duration_td).split('.')[0] # Remove microseconds
59

60
        print(f"[{task_id}] {desc} ({cat}) | Started: {start} | Ended: {end if end else 'Ongoing'} | Duration: {duration}")
61
    print("--------------------")
62

63
# Example Usage (could be wrapped in a command-line interface)
64
# create_database() # Ensure DB exists first
65
# start_task("Write Python code for tracker", "Development")
66
# # ... do work ...
67
# end_task()
68
# start_task("Attend stand-up meeting", "Meeting")
69
# # ... meeting ends ...
70
# end_task()
71
# list_tasks()

1
import sqlite3
2
import datetime
3
import matplotlib.pyplot as plt
4
from collections import defaultdict
5

6
DB_NAME = "productivity_tracker.db"
7

8
def analyze_productivity_by_category():
9
    """Analyzes and visualizes time spent per task category."""
10
    conn = sqlite3.connect(DB_NAME)
11
    cursor = conn.cursor()
12

13
    # Select tasks with both start and end times
14
    cursor.execute("SELECT category, start_time, end_time FROM tasks WHERE end_time IS NOT NULL")
15
    completed_tasks = cursor.fetchall()
16
    conn.close()
17

18
    if not completed_tasks:
19
        print("No completed tasks available for analysis.")
20
        return
21

22
    time_by_category = defaultdict(datetime.timedelta)
23

24
    for category, start_str, end_str in completed_tasks:
25
        try:
26
            start_dt = datetime.datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
27
            end_dt = datetime.datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
28
            duration = end_dt - start_dt
29
            if duration > datetime.timedelta(0): # Only count positive durations
30
                 time_by_category[category] += duration
31
        except ValueError:
32
            print(f"Skipping record with invalid timestamp format: Start={start_str}, End={end_str}")
33

34

35
    if not time_by_category:
36
         print("No valid completed tasks found for analysis after processing durations.")
37
         return
38

39
    # Convert timedeltas to hours for easier visualization
40
    hours_by_category = {cat: td.total_seconds() / 3600 for cat, td in time_by_category.items()}
41

42
    # Prepare data for plotting
43
    categories = list(hours_by_category.keys())
44
    hours = list(hours_by_category.values())
45

46
    # Sort categories by time spent
47
    sorted_categories, sorted_hours = zip(*sorted(zip(categories, hours), key=lambda x: x[1], reverse=True))
48

49

50
    # Create visualization using Matplotlib
51
    plt.figure(figsize=(10, 6))
52
    plt.bar(sorted_categories, sorted_hours, color='skyblue')
53
    plt.xlabel("Task Category")
54
    plt.ylabel("Time Spent (Hours)")
55
    plt.title("Developer Time Allocation by Category")
56
    plt.xticks(rotation=45, ha='right')
57
    plt.tight_layout() # Adjust layout to prevent labels overlapping
58
    plt.show()
59

60
# Example usage:
61
# create_database() # Ensure DB exists
62
# # ... use start_task() and end_task() to record data ...
63
# analyze_productivity_by_category()