Building a frontend using only Python

Frontend development can be a daunting, even nightmarish, task for backend-focused developers. Early in my career, the lines between frontend and backend were blurred, and everyone was expected to handle both. CSS, in particular, was a constant struggle; it felt like an impossible mission.

Although I enjoy frontend work, CSS remains a complex challenge for me, especially since I learned it through trial and error. The meme of Peter Griffin struggling to open blinds perfectly captures my experience of learning CSS.

But today, everything changes. Tools like Streamlit have revolutionized the game for developers like me, who prefer the comfort of a terminal's black screen. Gone are the days of wrestling with lines of code that look like cryptic messages from aliens (looking at you, CSS!).
As Doctor Károly Zsolnai-Fehér from Two Minute Papers always says, "What a time to be alive!"
With Streamlit, you can build an entire web application using just Python code.
Want to see it in action? Buckle up, because I'm about to share my attempt at creating the frontend for SQLZilla using this awesome tool.

To install it, simply open your terminal and cast this spell:

pip install streamlit

(Or you can add it to your requirements.txt file.)

Create a file, app.py and add this code snippet to display an "SQLZilla" title:

import streamlit as st

st.title("SQLZilla")

Run the Show!

Open your terminal again and type this command to activate your creation:

streamlit run app.py

Voila! Your Streamlit app should appear in your web browser, proudly displaying the title "SQLZilla."

Add an image using image method, to centralize it I just create 3 columns and add on center (shame on me)

   st.title("SQLZilla")

   left_co, cent_co, last_co = st.columns(3)
   with cent_co:
       st.image("small_logo.png", use_column_width=True)

To manage configurations and query results, you can use session state. Here's how you can save configuration values and store query results:

if 'hostname' not in st.session_state:
    st.session_state.hostname = 'sqlzilla-iris-1'
if 'user' not in st.session_state:
    st.session_state.user = '_system'
if 'pwd' not in st.session_state:
    st.session_state.pwd = 'SYS'
# Add other session states as needed

To connect SQLZilla to an InterSystems IRIS database, you can use SQLAlchemy. First, install SQLAlchemy with:

pip install sqlalchemy

Then, set up the connection in your app.py file:

from sqlalchemy import create_engine
import pandas as pd

# Replace with your own connection details
engine = create_engine(f"iris://{user}:{password}@{host}:{port}/{namespace}")

def run_query(query):
    with engine.connect() as connection:
        result = pd.read_sql(query, connection)
        return result

Once you've connected to the database, you can use Pandas and Streamlit to display the results of your queries. Here's an example of how to display a DataFrame in your Streamlit app:

if 'query' in st.session_state:
    query = st.session_state.query
    df = run_query(query)
    st.dataframe(df)

To make your app more interactive, you can use st.rerun() to refresh the app whenever the query changes:

if 'query' in st.session_state and st.button('Run Query'):
    df = run_query(st.session_state.query)
    st.dataframe(df)
    st.rerun()

You can find various Streamlit components to use. In SQLZilla, I added an ACE code editor version called streamlit-code-editor:

from code_editor import code_editor

editor_dict = code_editor(st.session_state.code_text, lang="sql", height=[10, 100], shortcuts="vscode")

if len(editor_dict['text']) != 0:
    st.session_state.code_text = editor_dict['text']

Since the SQLZilla assistant is written in Python, I just called the class:

from sqlzilla import SQLZilla

def assistant_interaction(sqlzilla, prompt):
    response = sqlzilla.prompt(prompt)
    st.session_state.chat_history.append({"role": "user", "content": prompt})
    st.session_state.chat_history.append({"role": "assistant", "content": response})

    if "SELECT" in response.upper():
        st.session_state.query = response

    return response

Congratulations! You’ve built your own SQLZilla. Continue exploring Streamlit and enhance your app with more features. And if you like SQLZilla, vote for this incredible assistant that converts text into queries!