Becoming an SQL squirrel

The first chapter of my quest was the hunt for a suitable GUI element to display data in columns. I eventually settled on the Multilistbox, a custom widget I found that was created using the standard Tkinter Listbox widget. I then enthusiastically dove into learning all I could about Tkinter. This revealed the need to thoroughly test my Multilistbox with real-world data..of which I had none. Then I focused on methods of getting data into my DataQ app, which led to experimenting with code to parse data from text and CSV files. Before I knew it I was off on multiple, simultaneous rabbit trails, including trying to figure out how to parse data from Evernote notes. Before I knew it, I was wandering aimlessly and making little progress.

Now I’ve decided to focus back on the original goal: financial management. This would be, by far, the most useful function for me, something I would use on a daily basis. It will also be instructive for other possible uses for DataQ. I therefore have now imported all 3000+ of my financial transactions from my Microsoft Access database to Sqlite. I’m laying aside the GUI for now and focusing on the heart and soul of DataQ which is data! I need to learn how to get the data I want using queries, something I’ve had passing acquaintance with through MS Access, but don’t know thoroughly because Access lets me build queries graphically. That’s great for getting results quickly, not so great for learning.

I have two tables: ‘transactions’ and ‘accounts’, with the following structure:


TransactionID * Integer primary key
Category * Income or expense category. In correct bookkeeping terminology, also an ‘account.’
Income * Amount
Expense * Amount
Account * Asset or liability account, ex: checking, savings, credit card


AccountNum * Integer primary key
AccountType * Asset, liability, income, expense
Clear * 1 = transaction has cleared the bank, 0 = not cleared.

Both the ‘Category’ and ‘Account’ fields contain an ‘AccountNum’ from ‘accounts.’ Every transaction query includes a calculated field called ‘Net’ which subtracts the ‘Expense’ amount from the ‘Income’ amount, giving the net effect on the account balance. A typical SQL query will look like the following, which displays all transactions in descending order from most recent:

SELECT transactions.Date, transactions.PayeeOrName, transactions.TransactionDescription, accounts.AccountDescription AS Category, transactions.Income, transactions.Expense, (transactions.Income - transactions.Expense) AS Net, accounts_1.AccountDescription AS Account, transactions.Clear
FROM (transactions INNER JOIN accounts AS accounts_1 ON transactions.Account = accounts_1.AccountNum)
INNER JOIN accounts ON transactions.Category = accounts.AccountNum ORDER BY transactions.Date DESC, PayeeOrName ASC

The data source for the Multilistbox will be obtained by queries such as this, so I think it’s time I get to know Sqlite better.