Python Personal Expenses Web App with Streamlit & SQLite

Originally a Glide App for personal Expenses

I had previously created a Glide, no code Personal Expense App and found it extremely useful. The Glide Apps are Web Apps and they originally wrote data to a Google Sheet in your account. See Glide Apps : https://www.glideapps.com/
It is a great app but I found it a bit slow to respond as data from the app went to the Google Sheet via Glide & then back through Glide to display update on the app on your mobile. It was a bit laggy. Since then they now have a data collector inside of Glide so no need to go to Google Sheets.

Replicate app processes in Python using Streamlit as framework

As I’ve been developing my skills as a programmer I wanted to do the same app in Python to see if it was more responsive than my original Glide App.
I searched the interwongle and YouTube for ideas ad I found Sven’s Streamlit app a good starting point for what I wanted. See You Tube ”python streamlit expense app” search: https://www.youtube.com/results?search_query=streamlit+expense+app
In Sven’s app for the backend he used a noSQL database with Deta platform which seems to be in beta phase. When I tried to replicate his app witj the code he gave I found that the Deta interface has changed since his video. So looked to use a different way to store the data.

SQLite as data storage & SQLite Studio for viewing tables

I’d played with using SQLite before but wanted to use it on a project, so decided that I’d use it on this project to store the data. The appeal is that your database is a single local file, so you can store it where you have your python script.
My one issue with SQLite DB was viewing the DB and I found that Izaias from Ahl the Automator used SQLite Studio to do this . SQLite Studio: https://sqlitestudio.pl/
So I now had all the elments that I needed to write and develop the script.
I personally struggle with coding a project I’ve done before and found it difficult, after the initial research on what type of tools I wanted to use to build the app to get sufficiently motivated to build something I’d built before.

Using Bing Crosby (aka Bing Chat) & Google Bard to get the initial code framework

At this time I saw that Google Bard was available for people to try outside of the US so I decided to compare it against Bing Chat to see how they both behaved. I wrote a simple prompt:
Can you write python streamlit app that you can select from a list a ‘category’ (category=[rent, food,rates]) and have an input field for sum in $ for amount and a date selector, default today, with a note field of say 150 chars and a submit button that will then put into a SQLite3 table
Can you add another field, ‘payee’ which can be a list (to=[paknsave, warehouse, warehouse mobile, WCC, countdown]) but you can add extra values to this list , and make the list a table in the SQLite DB so it reads the table but if new value added to a specific transaction it will also be added to the to table

And was impressed with the output from both Bard & Bing Chat, and was keen to test out the bare bones scripts that they wrote for the app. So tested them and added features in snippets to the initial prompt to get all the features that I needed to build the app.
I found their scripts great for connecting to the DB and doing CRUD (Create.Read.Update. Delete) operations required for the app really easy to use and incorporate into the script.
The script could be refined and rationalized as there are a lot of repetitive functions, but it works and I’m happy with that.

Exporting Data to CSV and how to do that from Remote Server

The one issue I had was getting data from the DB and making it accessible to the User. So I have an Export to CSV button but that requires the file to be accessible to the end user. To make that happen, when the App is deployed to a remote server required the file to be emailed to the user, and I did that via 2 external scripts, one for Expenses & one for In\come.

Summary Tab with graphics still to be developed

The basic elements of the app all work. I just need to decide on what UI want displayed on the summary tab, which I will look at after testing the app for a while.

Look to Deploy app onto Streamlit Cloud

I am considering a further video where I will deploy the app on the Streamlit cloud to see if that works. Currently the app will run locally & I have it also running on my personal VPS (Virtual Private Server).

Streamlit Sizing for Mobile- An issue

I spent this morning looking to see if I could reduce the vertical scale of the app or else make the components have less padding, I did succeed with some Specific CSS to reduce padding, but it just made the app ugly.

I was disappointment with scaling in the Streamlit app and I can’t seem to get all the bits I want on the page, so although Streamlit is responsive the components are rather large.

Overall it does not look as good as the Glide App doing the same function, the Glide app looks as if its made for mobile, whereas the Streamlit framework does not. But if it makes up for it in speed then that is good. Maybe a later version will overcome the limitations.

End comment

Overall I’m pleased with the process & the app after a slow start & have been impressed with the results from Bard & Bing Chat for creating an initial app from a simple prompt.
Script Link

Apart from Scaling of components in Streamlit to work in mobile format the rest looks good so far.