Columns that are created during SELECT do no exists

Question

I am trying to retrieve data from a sqlite data base. The goal is to get the sum of expenses per day. The code: (it is not correctly formatted)

db = SQL("sqlite:///financetracker.db")

test = db.execute("""
        SELECT SUM(amount) AS total_amount, strftime('%Y', timestamp) AS year, strftime('%m', timestamp) AS month, STRFTIME('%d', timestamp) AS day
        FROM transactions
        WHERE exp = 1 AND user_id = 1 AND year = strftime('%Y', 'now') AND month = strftime('%m', 'now')
        GROUP BY year, month, day
        ORDER by timestamp DESC
        """)

I am using the sql module from the cs50 library. exp is a column that contains booleans. This way I filter out the expenses.

As you can see, during the SELECT I add three additional columns: year, month and day and I calculate the sum per day. The dates are taken from another timestamp column in the same table. In this case test should be a list of dictionaries. The outcome looks like this:

total_amount	year	month	day
6.5	2021	01	29
20	2021	01	28

The SELECT works perfectly when I do it in phpliteadmin. Every column appears. But for some reasons the shown python script does not return the columns year, month and day. Only total_amount.

The following code:

day_1 = test[0]['day']

results in a key error:

KeyError: 'day'

@Barmer I just added the code and made some other improvements. — justforjan
Can you show how you set up the Python DB connection e.g. fetchall() — Mark
@Mark I added one line of code. That's all I did to connect the DB. This functionality comes within the SQL module of cs50. — justforjan
Something happened to test between the execute and day_1 = test[0]['day']. That sql will return dictionaries with all 4 keys whenever rows are selected. If no rows are selected this day_1 = test[0]['day'] would give a different error (index out of range). — DinoCoderSaurus

justforjan justforjan · Accepted Answer · 2021-01-30T15:56:32

Thanks you all for you input. I just solved it. For some reasons the columns day, month and year were all in capital letters. I don't know why, as I clearly wrote them in lowercase. What's even weirder is that total_amount stayed lowercase in the dict.

That means that by typing day_1 = test[0]['DAY'] I solved it.

Columns that are created during SELECT do no exists

1 Answers