1

I'm trying to make an expense tracker with Python and SQLite3. I've figured out how to input data and the fields are as follows: Amount Category(Food, Leisure, Transport, Education) Description DateOfPurchase I'm trying to create a method so I can get the sum of all the expenses of a certain category in the month and put it into a python variable (because I want to further process and create a pie chart of the different categories)

I've tried working with the SELECT SQL function but I'm not sure how to go about solving the problem

import sqlite3
import datetime
import time

connectdb = sqlite3.connect("Expenses.db")
c = connectdb.cursor()


def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS Expenses (Amount REAL, Category 
TEXT, Description TEXT, DateOfPurchase TEXT)")


# used to input a record
def datain():
    amount = float(input("Please enter the amount of money paid for the 
             expense"))
    Today = str(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d'))
    category = str(input("Please pick a category from Food, Transport, 
               Leisure, Education"))
    temp = ("Food", "Transport", "Leisure", "Education")
    while not (category in temp):
        category = str(input("Please pick a category from Food, 
        Transport, Leisure, Education"))
    description = str(input("Please describe the expense in a few 
                  words"))
    c.execute("INSERT INTO Expenses (Amount,Category,Description,DateOfPurchase) VALUES (?,?,?,?)",
          (amount, category, description, Today))
    rerun = str(input("Would you like to add another item? (Yes/No)"))
    if (rerun == "Yes"):
        datain()
    else:
        connectdb.commit()
        c.close()
        connectdb.close()
adil.a
  • 9
  • 4

1 Answers1

0

In order to write a detailed solution, I think I'd need to see the schema for your database. However, I think the gist of what you're trying to achieve is something like:

query = "SELECT SUM(Amount) FROM Expenses WHERE Month = ?;"
c.execute(query, theMonth)
result = c.fetchone()

And then result will be the Python variable you wanted to create.

Tom Hosker
  • 526
  • 2
  • 17
  • Here is a screenshot of how the database looks. I don't think I can do it using Month as a variable since the DateOfPurchase field is in the format of YYYY-MM-DD https://gyazo.com/b30ac6e8e1c9e0aa66da56edd7882fd2 – adil.a Jul 29 '19 at 16:02
  • There are two ways you could do this, then. The _best practice_ approach would be to re-design the database so that year, month, day, etc are in separate fields. (If this is a large project, you'll thank me later.) – Tom Hosker Jul 29 '19 at 17:18
  • A quicker, hackier way of doing it would be to change the `WHERE` clause of your `query` variable to `WHERE DateOfPurchase LIKE '2019-07%'`. But this sort of thing can get very ugly, very quickly. Don't say I didn't warn you! – Tom Hosker Jul 29 '19 at 17:24
  • P. S. If what I've suggested works, you'd really help me out by clicking the tick next to my answer. – Tom Hosker Jul 29 '19 at 17:25
  • It worked! However, if I print the executed query, it prints me the result value in list form e.g I was given the retrieved result in the form [(74.5,)]. Is there a way to convert this into float form instead of list so I can perform operations on it? – adil.a Jul 29 '19 at 23:17
  • Ah, yes. I forgot about that mildly irritating feature of Python's SQLite package! `fetchone()` returns a _tuple_. Again, there are two ways around this, the quick way and the proper way. Quick: index the tuple, i.e. `extract = c.fetchone()` \\ `result = extract[0]`. Proper: get Python to return a _dictionary_ - generally a much more useful data structure. [This answer](https://stackoverflow.com/questions/3300464/how-can-i-get-dict-from-sqlite-query) provides a straightforward guide to doing this. – Tom Hosker Jul 30 '19 at 06:37
  • P. S. Thank you so much for accepting my answer. My first one! – Tom Hosker Jul 30 '19 at 06:40