Prediction analysis and creating a database

Using Streamlit
1

Hi, I’m in a project where I have an algorithm already built the works as a calculator, so what I want to know is if there is any simple way that I can create the input variables on the left, save them in a database and use them to calculate with the algorithm the final result.

Thank you very much in advance!

2

Hi @Pedro_Freire, welcome to the forum

Here’s a quick snippet to make you started :slight_smile:

import pandas as pd
from pathlib import Path
import sqlite3
from sqlite3 import Connection
import streamlit as st

URI_SQLITE_DB = "test.db"


def main():
    st.title("My Super Calculator")
    st.markdown("Enter data in database from sidebar, then run the **mighty** calculator")
    
    conn = get_connection(URI_SQLITE_DB)
    init_db(conn)

    build_sidebar(conn)
    display_data(conn)
    run_calculator(conn)


def init_db(conn: Connection):
    conn.execute(
        """CREATE TABLE IF NOT EXISTS test
            (
                INPUT1 INT,
                INPUT2 INT
            );"""
    )
    conn.commit()


def build_sidebar(conn: Connection):
    st.sidebar.header("Configuration")
    input1 = st.sidebar.slider("Input 1", 0, 100)
    input2 = st.sidebar.slider("Input 2", 0, 100)
    if st.sidebar.button("Save to database"):
        conn.execute(f"INSERT INTO test (INPUT1, INPUT2) VALUES ({input1}, {input2})")
        conn.commit()


def display_data(conn: Connection):
    if st.checkbox("Display data in sqlite databse"):
        st.dataframe(get_data(conn))


def run_calculator(conn: Connection):
    if st.button("Run calculator"):
        st.info("Run your function")
        df = get_data(conn)
        st.write(df.sum())


def get_data(conn: Connection):
    df = pd.read_sql("SELECT * FROM test", con=conn)
    return df


@st.cache(hash_funcs={Connection: id})
def get_connection(path: str):
    """Put the connection in cache to reuse if path does not change between Streamlit reruns.
    NB : https://stackoverflow.com/questions/48218065/programmingerror-sqlite-objects-created-in-a-thread-can-only-be-used-in-that-sa
    """
    return sqlite3.connect(path, check_same_thread=False)


if __name__ == "__main__":
    main()

(could have put conn=get_connection(URI_SQLITE_DB) in each function where you need it since it’s in cache anyway, you can reorganize as you will)

image
image1035×491 18.8 KB

4 Likes
3

Woow, thank you very much for that @andfanilo

Talking about the database, my doubt is if can I store the data that every single user put on it, I mean, independently of who and where this user is using the app, is this already enough to store that data and build a consolidated database? Or I should build something to store that in another file, a csv for example?

Thank you very much for your attention, I really appreciate it!!

4

An actual database is always going to be a more scalable solution than CSV files. In the case of SQLite, you may not be able to use that as a multi-user reading and writing environment (you’d have to look in their docs). But for a database like Postgres or similar, that will scale to hundreds, thousands or even more concurrent users.

5

Hi andfanilo :wave::wave::wave:
Is it work when we run this code on streamlit sharing??
Or you run it on localhost???
If it’s possible, could you help me??
I’ve create a CRUD with streamlit further sqlserver database …i run it on localhost without any problem but in streamlit sharing gets error.

6

Hi andfanilo!
I noticed you imported pathlib’s Path but could not find a usage later in your code. Why is this? Something I’m missing? Thanks!

7

Hi, welcome to the forum :slight_smile:

I think I just forgot to remove it from my tests, you can remove it safely

Have a nice day,
Fanilo

8

thanks a lot andfanilo!