In today’s data-driven world, databases play a crucial role in storing and managing vast amounts of information. However, interacting with databases often requires knowledge of complex SQL queries and database structures, which can be intimidating for beginners. In this tutorial, we’ll explore how to build a user-friendly database chat assistant using Python, Streamlit, and the Vanna library. This assistant will allow users to ask questions in natural language and receive meaningful insights from the database, all without writing a single line of SQL code.
Connect With Us
Understanding the Motivation:
Imagine you’re a business owner who wants to gain insights from your sales database. You have valuable information stored in tables such as customer data, product details, and sales transactions. However, you’re not an expert in SQL, and writing complex queries to extract insights seems daunting. This is where our database chat assistant comes into play. It acts as your personal data interpreter, allowing you to ask questions in plain English and receive actionable insights from your database.
Setting up the Environment:
To get started, we’ll need to set up our development environment. We’ll be using Python along with Streamlit, a popular framework for building interactive web applications. Additionally, we’ll leverage the Vanna library, which provides natural language processing capabilities for querying databases.
import pandas as pd
import streamlit as st
import sqlite3
from sqlalchemy import create_engine, inspect, text
from vanna.remote import VannaDefault
# Establish connection to the SQLite database
engine=create_engine("sqlite:///Chinook_Sqlite.sqlite?mode=ro",connect_args={"uri": True})
After importing necessary libraries, we create an engine object using SQLAlchemy to establish a connection to the SQLite database named “Chinook_Sqlite.sqlite“
Building the User Interface:
def setup_ui():
    st.set_page_config(layout="wide")
    inspector = inspect(engine)
    table_names = inspector.get_table_names()
    selected_table = st.sidebar.selectbox(label='Database Schema Viewer', placeholder="Select a Table", options=table_names, index=None)
Explanation:
- We define a function setup_ui() to create the user interface using Streamlit.
- We set the page configuration to a wide layout for better visualization.
- We use SQLAlchemy’s inspect function to get the list of table names from the database.
- We create a sidebar selectbox to allow users to choose a table from the database schema.
db_file = 'Chinook_Sqlite.sqlite'
conn = sqlite3.connect(db_file)
if selected_table:
    df = get_table_data(selected_table, conn)
    st.sidebar.text(f"Data for table '{selected_table}':")
    st.sidebar.dataframe(df)
conn.close()
Explanation:
- We establish a connection to the SQLite database using sqlite3.connect().
- If a table is selected from the sidebar, we retrieve its data using the get_table_data() function and display it in the sidebar as a Pandas DataFrame.
- Finally, we close the database connection.
Implementing Natural Language Processing:
This code is not implemented in the main file. We have created a separate file for this.
Â
vn = VannaDefault(model='chinook', api_key=YOUR API KEY)
vn.connect_to_sqlite('Chinook_Sqlite.sqlite')
Explanation:
- Here, we instantiate a VannaDefault object named vn with the specified model (‘chinook’) and API key (‘key’).
- We then connect to the SQLite database using Vanna’s connect_to_sqlite() method, specifying the database file ‘Chinook_Sqlite.sqlite’.
@st.cache_data(show_spinner="Generating SQL query ...")
def generate_sql_cached(question: str):
    try:
        return vn.generate_sql(question=question)
    except:
        return None
Explanation:
- This function generate_sql_cached() is a cached function that generates an SQL query based on a natural language question provided by the user.
- It uses Streamlit’s @st.cache_data decorator to cache the function’s output, improving performance by avoiding redundant computations.
- The function calls Vanna’s generate_sql() method to generate the SQL query based on the user’s question.
Connect With Us
Â
Running SQL Queries:
@st.cache_data(show_spinner="Running SQL query ...")
def run_sql_cached(sql: str):
    try:
        return vn.run_sql(sql=sql)
    except:
        return None
Explanation:
- This function run_sql_cached() is a cached function that runs an SQL query against the database.
- It uses Streamlit’s @st.cache_data decorator to cache the function’s output.
- The function calls Vanna’s run_sql() method to execute the SQL query against the database.
Generating Plotly Visualizations:
@st.cache_data(show_spinner="Generating Plotly code ...")
def generate_plotly_code_cached(question, sql, df):
    try:
        code = vn.generate_plotly_code(question=question, sql=sql, df=df)
        return code
    except:
        return None
Explanation:
- This function generate_plotly_code_cached() is a cached function that generates Plotly code for visualizing data based on a natural language question, SQL query, and DataFrame containing query results.
- It uses Streamlit’s @st.cache_data decorator to cache the function’s output.
- The function calls Vanna’s generate_plotly_code() method to generate the Plotly code based on the user’s question, SQL query, and DataFrame.
Generating Follow-up Questions:
@st.cache_data(show_spinner="Generating followup questions ...")
def generate_followup_cached(question, df):
    try:
        return vn.generate_followup_questions(question=question, df=df, sql='NA')
    except:
        return []
Explanation:
- This function generate_followup_cached() is a cached function that generates follow-up questions based on a natural language question and the resulting DataFrame from the query.
- It uses Streamlit’s @st.cache_data decorator to cache the function’s output.
- The function calls Vanna’s generate_followup_questions() method to generate follow-up questions based on the user’s question and DataFrame containing query results.
Conclusion:
In this beginner’s guide, we’ve embarked on an exciting journey to build a database chat assistant. It’s like having a casual chat with your database, no need for fancy SQL skills. With just a bit of Python, a sprinkle of Streamlit, and a dash of Vanna magic, we’ve whipped up a tool that lets you talk to your data. Whether you’re a business owner wanting to dig into sales numbers or just curious about your data, this project makes it easy-peasy to have a friendly chat with your database.
