From Zero to App: Building a Database-Driven Streamlit App with Python
From no-code platforms to complex coding solutions, we have numerous options to build an app. Creating a functional app from scratch can be very overwhelming. If you are an analyst and you know Python and SQL, then you are in luck. Python provides powerful libraries which makes it perfect for anyone who wants to create interactive apps quickly – without needing deep knowledge of HTML, CSS, or JavaScript.
Streamlit is one such open-source Python library that allows you to build custom web applications with minimal efforts. It takes care of the front-end components allowing you to focus purely on writing your Python code. With Streamlit sharing, one can also deploy their Streamlit apps for free easily. SQLite, on the other hand, is a lightweight, server-less database engine included with Python. It requires no additional setup and allows you to save your app's data in a local file, making it incredibly portable and simple to use.
Combine Streamlit with Sqlite, and you get an interactive, user-friendly application backed by a powerful back-end database system. Follow along with me in this article where I explore how to create a simple expense management app to record personal expenses and export all the data for further analysis.
Designing the Database
For the very first version, I wanted the application to do three things –
- Record an expense with date and an expense category
- Add custom expense categories
- Export all the data
As a basic rule, I used the normalization principle to design the tables. I created separate tables for categories and expenses, and with SQL joins, I reunited the two to create a complete expense record. Below is the code I used to create the two tables:
import Streamlit as st
import sqlite3
import pandas as pd
import datetime as dt DATABASE_NAME = "expenses.db"DEFAULT_CATEGORIES = ["Rent", "Utilities", "Groceries"]cat_list = ""
for i in DEFAULT_CATEGORIES:
cat_list = cat_list + "("" + i + ""),"def create_tables():
connection = sqlite3.connect(DATABASE_NAME)
cursor = connection.cursor() # Add the categories table
cursor.execute('''
CREATE TABLE IF NOT EXISTS category (
id INTEGER PRIMARY KEY,
category TEXT
)
''')
# Insert some default category values
cat_query = 'SELECT category FROM category'
category_list = pd.read_sql_query(cat_query, connection).category.to_list()
if len(category_list) == 0:
cursor.execute('INSERT INTO category (category) VALUES ' + cat_list[:-1]) # Add the expenses table
cursor.execute('''
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY,
date INTEGER,
category_id INTEGER,
amount REAL,
FOREIGN KEY (category_id) REFERENCES category(id)
)
''') connection.commit()
connection.close()I added some default categories so that the user will have some options to start with. I ensured the default values are inserted only for the first time when the category table is created. If we skip this step, then there is chance of inserting the same values multiple times when page is reloaded.
Designing the App
Next we need to create fields for user to input their expense. For this, I used the form widget from Streamlit to get all the user inputs. Then I created a function to save the record once the submit button is clicked.
def save_expense(date, category, amount):
connection = sqlite3.connect(DATABASE_NAME)
cursor = connection.cursor() cursor.execute('SELECT id FROM category WHERE category = ?', (category,))
row = cursor.fetchone()
cat_id = row[0] try:
cursor.execute('INSERT INTO expenses (date, category_id, amount) VALUES (?, ?, ?)', (date, cat_id, amount))
result='Expense record saved successfully!'
except:
result='Oops something is not right. Please check your inputs and try again!' connection.commit()
connection.close()
return resultst.header("Record Expense")with st.form("record_form", clear_on_submit=True):
st.write("Fill in your expense details here")
f_date = st.date_input("Enter the date", value=None)
f_category = st.selectbox('Pick a category', u.get_category_list())
f_amount = st.number_input("Enter amount in $")
f_submitted = st.form_submit_button('Submit Expense')if f_submitted:
expense_result = save_expense(f_date,f_category,f_amount)
st.success(expense_result)To add a custom category, I added the logic to check if a particular category already exists in the database in order to avoid duplicates. I also stored the categories in "title" case to keep the list of categories clean (Ex: "flight tickets", "Flight tickets", "FLIGHT TICKETS", etc are all saved as "Flight Tickets). I displayed the list of available categories for the user so that they could visually check before creating a new category. Once a new category is added, I have to also update the list shown to the user. To achieve this without having to rerun the whole script, I stored the category list in a _sessionstate which is also updated when the "Add new category" button is clicked.
def save_category(new_choice):
new_choice = new_choice.title()
connection = sqlite3.connect(DATABASE_NAME)
cursor = connection.cursor() cat_query = 'SELECT category FROM category'
category_list = pd.read_sql_query(cat_query, connection).category.to_list() if new_choice in category_list:
result = new_choice + " already exists in the list of categories"
else:
cursor.execute('INSERT INTO category (category) VALUES (?)', (new_choice,))
result = new_choice + " added successfully to the list of categories" connection.commit()
connection.close() return resultdef update_value():
connection = sqlite3.connect(DATABASE_NAME)
query = '''
SELECT c.id, c.category
FROM category c
'''
df = pd.read_sql_query(query, connection)
connection.close()
cat_list = df.category.tolist()
st.session_state.category = ', '.join(cat_list)st.header("Categories")
new_choice = st.text_input('Add your own new category:')
submitted = st.button("Add new category") if submitted:
category_result = save_category(new_choice)
st.success(category_result)
update_value()# ---------- Show Categories --------------------
st.write("Here are the categories.")
st.write(st.session_state.category)Exporting the data is also easy. You need a function which will gather the data from the required tables and export to an excel file. In this case I join the expense and category tables to create a table with all the data user previously keyed in. This function can be called on click of a button.
def get_expenses():
connection = sqlite3.connect(DATABASE_NAME)
query = '''
SELECT e.date, c.category, e.amount
FROM expenses e
LEFT JOIN category c ON c.id = e.category_id
'''
df = pd.read_sql_query(query, connection)
connection.close()
return df# -------------- Download Button------------------
df = get_expenses()
st.download_button(
label="Download data as CSV",
data=df.to_csv().encode("utf-8"),
file_name="expenses.csv",
mime="text/csv",
)To run the file (say the file name is main.py), simply execute the following command line:
streamlit run main.pyOnce I had all the blocks of code ready, I separated all the database functions into a different file. I also arranged the page layout using "columns" and added a sidebar to write in additional info. Check out the final version of code in my Github repository here.
Final Thoughts
Congratulations on building an interactive, database-powered apps with Python, Streamlit, and SQLite! I hope this project served as an enjoyable learning experience to explore the world of some of the Streamlit's UI capabilities. There's lot of possibilities to improve the app, such as adding data visualizations, user authentication, enhanced data management options and deploying the app to cloud, but all this is for another day. I hoped you enjoyed working on this! Keep experimenting, and who knows?
Before you go…
_Follow me on Medium so you don't miss any new posts I write in future; you will find more of my articles on my profile page. You can also connect with me on LinkedIn or Twitter!_
