Help with SQLITE DB - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: Help with SQLITE DB (/thread-32575.html) |
Help with SQLITE DB - Tuxedo - Feb-18-2021 I have a two part question. I've created a DB via SQLITE containing personal financial transactions consisting of transaction date, amount, description, and an assigned category (e.g. food, clothing, taxes, etc.). I have 43 categories in total and the data spans multiple years. My goal is to be able to sum the amounts by category for each year and do a year by year comparision. Q1: right now I have all of the data in a single table. Considering my goal, is there a more appropriate design (e.g. each year in a different table)? Q2: is there a clever SQL query I can use to sum the amounts for each category/year combination? I envision perhaps a Pandas dataframe that I'd export to an Excel file. I suppose I could do a separate SQL query for each category and year via a nested loop but I'm guessing there is a more efficient/streamlined way to do it. I'd appreciate your comments. RE: Help with SQLITE DB - buran - Feb-18-2021 (Feb-18-2021, 07:28 PM)Tuxedo Wrote: Q1: right now I have all of the data in a single table. Considering my goal, is there a more appropriate design (e.g. each year in a different table)?No, your design looks good. Table per year would be plainly wrong (Feb-18-2021, 07:28 PM)Tuxedo Wrote: Q2: is there a clever SQL query I can use to sum the amounts for each category/year combination? I envision perhaps a Pandas dataframe that I'd export to an Excel file. I suppose I could do a separate SQL query for each category and year via a nested loop but I'm guessing there is a more efficient/streamlined way to do it.Not tested, but something like or GOUP BY category, strftime('%Y',date_column) - depending on which column you want to group by first.Of course, the same can be done in pandas, with pivot_table() or groupby() EDIT: see correct example here: https://python-forum.io/Thread-Help-with-SQLITE-DB?pid=137691#pid137691 RE: Help with SQLITE DB - Tuxedo - Feb-18-2021 Hmmm, I'll try both. Thanks! RE: Help with SQLITE DB - buran - Feb-18-2021 I edited the sql a bit to get just the year RE: Help with SQLITE DB - buran - Feb-18-2021 import sqlite3 conn = sqlite3.connect('test1.sqlite3') sql="""BEGIN TRANSACTION; CREATE TABLE `transactions` ( `tdate` TEXT, `category` TEXT, `amount` INTEGER ); INSERT INTO `transactions` (tdate,category,amount) VALUES ('2020-01-01','food',1), ('2020-02-01','food',3), ('2021-01-05','food',5), ('2021-01-10','fuel`',10); COMMIT;""" conn.executescript(sql) sql = 'SELECT * from transactions' result = conn.execute(sql) for row in result: print(row) sql = """SELECT strftime('%Y', tdate) as Year, category, SUM(amount) As Amount FROM transactions GROUP BY Year, category""" result = conn.execute(sql) for row in result: print(row)
RE: Help with SQLITE DB - Tuxedo - Feb-18-2021 Wow, I have so much to learn. Thanks! |