Building SQL Playground App with Streamlit and Python

SQL is an essential skills every data scientist and data engineer needs to know. In this article we will build a simple app where anybody can try out his know how on SQL. The app we will be building is like a clone of sqlzoo.net where by you can practice your SQL queries.

By the end of this tutorial you will learn

• How to build apps with streamlit
• How to connect to a database
• Streamlit forms and streamlit layouts
• etc

Streamlit is a powerful python framework for building data apps. Its simplicity makes it easier to build not just machine learning apps but different types of apps such as this one, although what we are building is still relevant for the domain of data science and data engineering as well as back end database management.

The requirements for this app is quite simple, we will only use streamlit, pandas and sqlite3 for easy of use .(you can use any database library of your choice such as postsgrel, mysql,etc.)

Installation

pip install streamlit pandas 

Structure and Workflow of App

The app will just have 3 sections, a part for receiving the query, another part of the app for executing the query entered and connecting to the database and finally the part for displaying the results of the submitted query.

For receiving the input of the query we will use streamlit forms and streamlit text_area input for this part. Streamlit makes it ease to implement this.

# Columns/Layout
		col1,col2 = st.beta_columns(2)

		with col1:
			with st.form(key='query_form'):
				raw_code = st.text_area("SQL Code Here")
				submit_code = st.form_submit_button("Execute")

			# Table of Info

			with st.beta_expander("Table Info"):
				table_info = {'city':city,'country':country,'countrylanguage':countrylanguage}
				st.json(table_info)
			

For searching the SQL Database and connecting to the database to retrieve info we utilized a simple function with sqlite3 as below

# DB Mgmt
import sqlite3 
conn = sqlite3.connect('data/world.sqlite')
c = conn.cursor()


# Fxn Make Execution
def sql_executor(raw_code):
	c.execute(raw_code)
	data = c.fetchall()
	return data 

The result of the query will be displayed as either a table using pandas or as a list or json like format. We can implement that using the code below

# Results Layouts
		with col2:
			if submit_code:
				st.info("Query Submitted")
				st.code(raw_code)

				# Results 
				query_results = sql_executor(raw_code)
				with st.beta_expander("Results"):
					st.write(query_results)

				with st.beta_expander("Pretty Table"):
					query_df = pd.DataFrame(query_results)
					st.dataframe(query_df)

Below is the code for each part of the app placed together in a file named app.py

# Core Pkgs
import streamlit as st 
import pandas as pd

# DB Mgmt
import sqlite3 
conn = sqlite3.connect('data/world.sqlite')
c = conn.cursor()


# Fxn Make Execution
def sql_executor(raw_code):
	c.execute(raw_code)
	data = c.fetchall()
	return data 


city = ['ID,', 'Name,', 'CountryCode,', 'District,', 'Population']
country = ['Code,', 'Name,', 'Continent,', 'Region,', 'SurfaceArea,', 'IndepYear,', 'Population,', 'LifeExpectancy,', 'GNP,', 'GNPOld,', 'LocalName,', 'GovernmentForm,', 'HeadOfState,', 'Capital,', 'Code2']
countrylanguage = ['CountryCode,', 'Language,', 'IsOfficial,', 'Percentage']




def main():
	st.title("SQLPlayground")

	menu = ["Home","About"]
	choice = st.sidebar.selectbox("Menu",menu)

	if choice == "Home":
		st.subheader("HomePage")

		# Columns/Layout
		col1,col2 = st.beta_columns(2)

		with col1:
			with st.form(key='query_form'):
				raw_code = st.text_area("SQL Code Here")
				submit_code = st.form_submit_button("Execute")

			# Table of Info

			with st.beta_expander("Table Info"):
				table_info = {'city':city,'country':country,'countrylanguage':countrylanguage}
				st.json(table_info)
			
		# Results Layouts
		with col2:
			if submit_code:
				st.info("Query Submitted")
				st.code(raw_code)

				# Results 
				query_results = sql_executor(raw_code)
				with st.beta_expander("Results"):
					st.write(query_results)

				with st.beta_expander("Pretty Table"):
					query_df = pd.DataFrame(query_results)
					st.dataframe(query_df)


	else:
		st.subheader("About")





if __name__ == '__main__':
	main()

To run the app you can use the code below inside your terminal

streamlit run app.py

You can see how simple it is to build a simple mini clone of sqlzoo.net with streamlit. Awesome!!

You can check out the video tutorial below. Don’t forget to share what you come out with this tutorial.

Support the channel

Become a Patron!

Thank you for your attention

Jesus Saves

By Jesse E.Agbe(JCharis)