Deploying a web app using MySQL server via Streamlit
Streamlit is becoming popular among many data science and AI community for its ease of web application deployment. But having a free presentable app hosted on GitHub will be limited by the amount of data you can leverage from GitHub repo. What if you have a lot of data which cannot be uploaded to GitHub? Or your data is sensitive to being made public? How can you remotely access your enormous amount of data or private data without hassle?
To overcome these and to make the use of streamlit more meaningful we can now connect our database connection remotely to streamlit cloud and run our web app without exposing sensitive data or getting trouble with very large amount of data. In this article I will discuss how you can leverage this to build an awesome dashboard or portfolio project directly from your database to streamlit web app.
All of this started with when I was using MovieLens 25M dataset[1][3] to create a live recommendation system. The dataset contain 25 million ratings and I was not confident to upload it anywhere. I was looking for solutions and found streamlit as optimal option. Let’s begin, I started my transforming and injecting data to MySQL server using python and SQLAlchemy (I will share how to do this too in another tutorial). After having transformed data in database, I started preparing code by importing the necessary modules.
Create_engine() from SQLAlchemy will be used to create the remote connection from database. Nearest Neighbors for movie recommendation. PyODBC is used to access ODBC databases this is the key here to have a stable connection of your database to MsSQL server as we use the ODBC driver in our hosted VM on streamlit. PyODBC allows you connecting to and using an ODBC database using the standard DB API 2.0 whereas SQL Alchemy is a toolkit that provides features like Object-relational mapping (ORM). PyMySQL is an interface for connecting to a MySQL database server from Python. I will the combination of PyMySQL and PyODBC with create_engine() from SQL Alchemy to create the connection as shown below:
As discussed above, we will have a combination of PyMySQL and PyODBC. The rest part of create engine are username and password of your database, DB_server is the IP or address of your server and database is the name of the database you want to access. After running this our connection with the MsSQL server is established and now we can query the database as this:
Here we used pandas to read SQL query with engine as our established connection to the database. The output from SQL server will return a dataframe which can be used in variety of ways for analysis. Another way of doing this is to use allow_output_mutation with @st.cache decorator.
Streamlit provides a caching mechanism that allows your app to stay performant even when loading data from the web, manipulating large datasets, or performing expensive computations. This is done with the @st.cache decorator. [2]
The reason for selecting this decorator with database connection is because we have to open a database connection that can be reused across several runs of a Streamlit app and we can make use of the fact that cached objects are stored by reference to automatically initialize and reuse the database connection. So whenever get_connection() function is called the already-created connection object is reused automatically. allow_output_mutation=True is used suppress the immutability check. This prevents Streamlit from trying to hash the output connection, and also turns off Streamlit’s mutation warning in the process.[2]
Now we will configure our web app page as:
Set_page_config() help us to customize the webpage as we want our UI/UX to be. Page_title is the main title of your web app. Layout can be wide or centered. I use wide to use the whole screen. initial_sidebar_state shows or hide the side bar. page_icon is set to none for my app but you can use any image with st.image or icon for your app.
Above, we have define a function based on our get_connection() function to ready any query from our database and return a dataframe. In this way we can have a dataframe in streamlit cache and we don’t have to query again and again our database for same data. Thus, speeding up our app response time. This is very crucial in cases where we have tons of data, and we don’t want our app to take infinity to provide interactive outputs.
After configuring the page and establishing the database connection, we move towards defining the tasks our app will do. St.columns allow use to divide our screen in different parts i.e. we can do different work in each column area. You can divide it with the ratio you want for your app in my case I just wanted to leave 1/8 spaces free from each side and centered column are used to write headers and sub headers which will look like this at output:
Below are some snippets of the work I was doing to query and transform data.
After writing the data transformation & querying code for frontend, we move toward deploying our web app. For deploying web app with streamlit cloud we first need to create a repository on GitHub for the app. As public apps are deployed directly from GitHub repo. First, we will upload our code file as .py (in my case I wrote st_app.py) then we will create two .txt files namely packages.txt & requirements.txt as shown below.
requirements.txt will contain all the Python package available on PyPi that we will be using in our web app and packages.txt will hold what is not the part of base Streamlit share image. Here ‘msodbcsql17’ (will be in packages.txt) is key to have connection to your database. As this driver is needed to be installed beforehand. If you forgot to add ‘msodbcsql17’ driver in packages.txt the database connection will not work.
Next, since we are using DB connection we need to store our credentials separately to avoid exposing it to everyone. For this we need to create a /secrets.toml file in app’s root directory. Create this file and add the SQL Server name, database name, username, and password as shown below:
Note: add this file to .gitignore and don’t commit it to your GitHub repo.
After uploading all the necessary files to GitHub, you need to login to https://share.streamlit.io/. After login, you will see “New app” option as seen in image below:
After selecting the option “from existing repo” fill in your repo, branch and file path. As a shortcut, you can also click “Paste GitHub URL”.
Now click “Advanced settings…” and you will see a new window as this:
As the secrets.toml file above is not committed to GitHub, you need to pass its content to your deployed app (on Streamlit Cloud) separately. Enter all the data we entered in secret.toml file here.
Now click deploy and wait for launch, it will take few minutes to create a virtual machine image for your dependencies and code. After successful integration you can now see the app from URL shown on streamlit and share your work with everyone you want. These are some snippets from my work you can further improve the UI and showcase task you want to achieve.
That’s all for this part, to compliment this implementation on how to use Colab to prepare and inject data in database you can read about it here: Cleaning & Injecting data into Microsoft SQL server via Python on Google Colab
References:
[1] https://grouplens.org/datasets/movielens/25m/
[2] https://docs.streamlit.io/library/advanced-features/caching
