# Prediction analysis and creating a database

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!

Hi @Pedro_Freire, welcome to the forum

Here’s a quick snippet to make you started

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):
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"):
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.
"""

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)

2 Likes

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!!

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.