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!).
AsDoctor 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:

pipinstallstreamlit

(Or you can add it to yourrequirements.txt file.)

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

importstreamlitasstst.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)withcent_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'notinst.session_state:st.session_state.hostname='sqlzilla-iris-1'if'user'notinst.session_state:st.session_state.user='_system'if'pwd'notinst.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:

pipinstallsqlalchemy

Then, set up the connection in yourapp.py file:

fromsqlalchemyimportcreate_engineimportpandasaspd# Replace with your own connection detailsengine=create_engine(f"iris://{user}:{password}@{host}:{port}/{namespace}")defrun_query(query):withengine.connect()asconnection:result=pd.read_sql(query,connection)returnresult

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'inst.session_state:query=st.session_state.querydf=run_query(query)st.dataframe(df)

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

if'query'inst.session_stateandst.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 calledstreamlit-code-editor:

fromcode_editorimportcode_editoreditor_dict=code_editor(st.session_state.code_text,lang="sql",height=[10,100],shortcuts="vscode")iflen(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:

fromsqlzillaimportSQLZilladefassistant_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"inresponse.upper():st.session_state.query=responsereturnresponse

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