Connect Streamlit to SingleStore

Abstract

This article will show how SingleStore can be quickly used with Streamlit, an open-source application framework for Machine Learning and Data Science.

Introduction

The Streamlit documentation describes how to connect Streamlit to MySQL. It is trivial to adapt the code to work with SingleStore. Let's see how.

To begin with, we need to create a free Managed Service account on the SingleStore website. At the time of writing, the Managed Service account from SingleStore comes with $500 of Credits. This is more than adequate for the case study described in this article.

Create the Database Table

In our SingleStore Managed Service account, let's use the SQL Editor to create a new database and table. We'll use the example provided by Streamlit:

CREATE DATABASE pets;

USE pets;

CREATE TABLE mytable (
     name VARCHAR(80),
     pet VARCHAR(80)
);

INSERT INTO mytable VALUES
('Mary', 'dog'),
('John', 'cat'),
('Robert', 'bird');

Install Streamlit

We'll install Streamlit, as follows:

pip install streamlit

The Streamlit example uses MySQL Connector. However, for most Python use cases, SingleStore recommends using PyMySQL. All we need to do is install this, as follows:

pip install PyMySQL

Modify the Example Application

In the file streamlit_app.py we need to make a few minor modifications:

• Replace import mysql.connector with import pymysql
• Replace mysql.connector.connect with pymysql.connect
• Replace st.secrets["mysql"] with st.secrets["singlestore"]
• Remove references to st.cache

Here is the complete modified code listing:

# streamlit_app.py

import streamlit as st
import pymysql

# Initialize connection.

def init_connection():
    return pymysql.connect(**st.secrets["singlestore"])
  
conn = init_connection()

# Perform query.

def run_query(query):
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()
      
rows = run_query("SELECT * FROM mytable;")

# Print results.

for row in rows:
    st.write(f"{row[0]} has a :{row[1]}:")

Create a Secrets File

Our local Streamlit application will read secrets from a file .streamlit/secrets.toml in our application's root directory. We need to create this file, as follows:

# .streamlit/secrets.toml

[singlestore]
host = "<TO DO>"
port = 3306
database = "pets"
user = "admin"
password = "<TO DO>"

The <TO DO> for host and password should be replaced with the values obtained from the SingleStore Managed Service when creating a cluster.

Run the Code

We can run the Streamlit application as follows:

streamlit run streamlit_app.py

The output in a web browser should look like Figure 1.

Conclusion

This article showed rapid code modifications to the Streamlit MySQL example so that Streamlit could be used with SingleStore instead. We’ll explore Streamlit further in future articles and use its capabilities in greater depth. Stay tuned!