Constructing an AI SQL Assistant with LangChain, GROQ, and Streamlit

Introduction

Have you ever ever wished you would merely chat together with your database, asking questions in plain language and getting immediate, related solutions? Think about the chances – no extra complicated SQL queries or digging by means of spreadsheets. Nicely, with the ability of LangChain and its new SQL toolkit, that’s precisely what you are able to do!

Diving into the brand new thrilling world of conversational database interactions, we are going to discover how with GROQ API, Streamlit, and LangChain SQL toolkit, you may create your chat software in your MySQL database. Be it as a knowledge fanatic, developer, or simply enthusiastic to see potential, everybody goes to realize worth insights and a few sensible data to unseal the true energy of your information.

Studying Outcomes

• Perceive combine LangChain’s SQL toolkit with a MySQL database to allow pure language queries.
• Discover ways to use the GROQ API for environment friendly and exact information retrieval.
• Acquire insights into constructing an interactive UI for database interplay utilizing Streamlit.
• Discover greatest practices for establishing database connections with SQLAlchemy.
• Uncover create a conversational AI-powered SQL assistant with seamless question dealing with.

This text was printed as part of the Information Science Blogathon.

Chat and Visualize MySQL Information with LangChain, GROQ, and Streamlit

Databases-the warehouses of extremely helpful, structured information-have been the spine of hundreds and hundreds of purposes. In robustness, ease of use, and broad utilization, nonetheless, one stands out: MySQL, the favored open-source relational database administration system. It gives a structured method of storing, organizing, and retrieving information in an environment friendly method, making it everybody’s go-to enterprise or developer’s alternative.

Nevertheless, to a nontechnical particular person, the direct manipulation of MySQL or every other database, for that matter, could be akin to making an attempt to crack a cryptic code. Highly effective as they’re, the previous method of utilizing SQL queries typically acts as a barrier towards entry into insights locked away in tables and columns.

From SQL Queries to Pure Language Conversations

However what if we might converse with our MySQL database in pure language, very like chatting with a colleague? What if we might visualize the info immediately in stunning charts and graphs, making tendencies and patterns leap off the display?

That brings us to the thrilling trio of LangChain, GROQ, and Streamlit. LangChain interprets our human-like questions into structured languages comprehensible to the database for which it makes use of its SQL toolkit. GROQ fetches the precise information that we’re in search of with pinpoint accuracy on account of its intuitive API. Lastly, Streamlit is a robust Python library for constructing internet purposes, offering the canvas that enables us to color the masterpiece pushed by our information. Streamlit helps simplicity and ease in speedy improvement, making it simpler to construct interactive interfaces with minimal effort.

Think about asking your MySQL database: “What have been our best-selling merchandise final quarter?” “Here’s a graph of buyer development over the previous yr.” Now you may, due to LangChain, GROQ, and Streamlit. On this tutorial, we are going to discover ways to construct a robust, user-friendly software that permits you to chat together with your MySQL database like an knowledgeable assistant whereas having fun with the real-time information presentation great thing about Streamlit.

Get able to unlock the complete potential of your MySQL information, making it accessible, comprehensible, and actionable like by no means earlier than.

Tech Stack Used

LangChain is due to this fact an adaptable platform for AI that allows the interplay between a human and a wide range of information sources to stream. Very similar to a bridge helps join our pure expressions in language with the structured world of databases, APIs, and other forms of information repositories.

The LangChain SQL toolkit acts as an clever translator, changing our human-like questions into SQL queries that the database understands. It leverages the ability of language fashions to know the intent behind our requests and generate the corresponding SQL code. This not solely simplifies the info retrieval course of but additionally opens up database entry to a wider viewers, together with these with out in depth SQL data.

Setting Up Required Packages

To get your software up and operating easily, you’ll want to put in a number of important packages. This step ensures that each one crucial libraries and instruments can be found for the seamless integration of LangChain, GROQ, and Streamlit together with your MySQL database.

pip set up streamlit
pip set up mysql-connector-python
pip set up langchain-community
pip set up python-dotenv
pip set up langchain
pip set up langchain-groq
pip set up sqlalchemy

Creating Your MySQL Chat Interface

Let’s deliver our ideas to life! The next code combines the ability of LangChain’s SQL toolkit, GROQ’s information fetching capabilities, and Streamlit’s intuitive UI to create a conversational interface to your MySQL database. We’ll stroll by means of the important thing steps, from establishing the database connection to dealing with consumer queries and displaying outcomes.

SQL Code for Database Initialization

To get began, we have to arrange our MySQL database and outline its schema. Under is the SQL code required to initialize the database and create the mandatory tables for storing consumer and put up info.

create database instagramdb;
use instagramdb;
create desk if not exists customers(
userId int main key,
userName varchar(50),
e-mail varchar(100)
);
create desk if not exists posts(
postId int main key,
userId int,
caption varchar(100)
);

insert into customers(userId,userName,e-mail)
values
(1,"Gourav","[email protected]"),
(2,"Tushar","[email protected]"),
(3,"Adi","[email protected]");

insert into posts(postId,userId,caption)
values
(51,"587","rain"),
(2,"963","water"),
(3,"821","sunny");

Python Code for Interactive Utility

To deliver our idea to life, we’ll write the Python script that integrates LangChain, GROQ, and Streamlit. This code will arrange the interactive chat interface, deal with database connections, and allow pure language interactions together with your MySQL database.

Import Required Libraries

Allow us to begin by importing required libraries under:

import streamlit as st
from pathlib import Path
from langchain_community.agent_toolkits.sql.base import create_sql_agent
from langchain_community.utilities import SQLDatabase
from langchain.brokers.agent_types import AgentType
from langchain_community.callbacks.streamlit import StreamlitCallbackHandler
from langchain_community.agent_toolkits.sql.toolkit import SQLDatabaseToolkit
from sqlalchemy import create_engine
from langchain_groq import ChatGroq

These imports lay the inspiration for constructing a Streamlit software that leverages LangChain, GROQ, and SQLAlchemy to allow pure language interactions together with your MySQL database. The code units the stage for creating an SQL agent that understands consumer queries, interprets them into SQL, executes them on the database, and shows the ends in a user-friendly method inside your Streamlit app.

Streamlit Configuration

On this part, we’ll arrange the inspiration for our Streamlit software, together with its preliminary structure and configuration. This can set up the atmosphere the place customers will work together with the AI SQL Assistant.

st.set_page_config(page_title="AI SQL Assistant", page_icon="🤖", structure="broad")

st.title("🤖 AI SQL Assistant")

MYSQL = "USE_MYSQL"

Sidebar and Database Configuration

We’ll dive into the sidebar setup, permitting customers to enter their MySQL connection particulars and API keys. This configuration ensures seamless connectivity to the database and integration with the mandatory instruments.

with st.sidebar:
    st.header("Database Configuration")
    selected_opt = st.radio("Select an possibility", ["How to Use", "Connect to MySQL Database"]

    if selected_opt == "Hook up with MySQL Database":
        db_uri = MYSQL
        mysql_host = st.text_input("MySQL Host")
        mysql_user = st.text_input("MySQL Consumer")
        mysql_password = st.text_input("MySQL Password", sort="password")
        mysql_db = st.text_input("MySQL Database")
        api_key = st.text_input("Groq API Key", sort="password")
    else:
        db_uri = None
        api_key = None

    if st.button("Clear Chat Historical past"):
        st.session_state["messages"] = []

    st.markdown("---")
    st.subheader("About")
    st.information("This AI SQL Assistant makes use of LangChain and Groq to offer a pure language interface to your MySQL database. Ask questions in plain English, and get SQL-powered solutions!")

• This code units up the preliminary structure and configuration choices to your AI SQL Assistant app.
• The sidebar permits customers to both discover ways to use the app or present their MySQL database connection particulars and GROQ API key.
• The “Clear Chat Historical past” button allows customers to reset the dialog.
• The “About” part offers a short description of the app’s performance.

Bear in mind, that is simply the preliminary setup. You’ll possible have further code to deal with the precise database connection, question processing utilizing LangChain and GROQ, and displaying the ends in the primary space of the Streamlit app.

Dealing with Consumer Enter and Question Execution

This half covers the core performance of processing consumer queries and executing them towards the MySQL database. We’ll element how the appliance interprets pure language inputs into SQL instructions and shows the outcomes interactively.

if db_uri == MYSQL and never api_key:
    st.sidebar.error("Please add the Groq API key")
    st.cease()

if api_key:
    llm = ChatGroq(groq_api_key=api_key, model_name="llama-3.1-70b-versatile", streaming=True)

• This code demonstrates good apply by together with enter validation to enhance the robustness of your software.
• Using st.cease() successfully prevents the app from operating with out the required API key.
• The initialization of ChatGroq units the stage for utilizing GROQ’s capabilities inside your LangChain-powered SQL agent.

@st.cache_resource(ttl="2h")
def configure_db(db_uri, mysql_host=None, mysql_user=None, mysql_password=None, mysql_db=None):
    if db_uri == MYSQL:
        if not (mysql_host and mysql_user and mysql_password and mysql_db):
            st.error("Please present all MySQL connection particulars.")
            st.cease()
        return SQLDatabase(create_engine(f"mysql+mysqlconnector://{mysql_user}:{mysql_password}@{mysql_host}/{mysql_db}"))

• The @st.cache_resource decorator optimizes efficiency by caching the database connection
• Enter validation helps stop errors and improves the consumer expertise
• SQLAlchemy offers a sturdy method to connect with and work together together with your MySQL database
• LangChain’s SQLDatabase class bridges the hole between SQLAlchemy and LangChain’s pure language processing capabilities

if db_uri == MYSQL:
    db = configure_db(db_uri, mysql_host, mysql_user, mysql_password, mysql_db)
else:
    st.header("Tips on how to Use")
    st.markdown("""
    1. Choose "Hook up with MySQL Database" within the sidebar.
    2. Fill in your MySQL connection particulars and Groq API key.
    3. As soon as linked, you can begin chatting together with your SQL database!
    4. Ask questions in pure language, and the AI will translate them into SQL queries.
    """)
    st.cease()

toolkit = SQLDatabaseToolkit(db=db, llm=llm)

streamlit_callback = StreamlitCallbackHandler(st.container())

agent = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    verbose=True,
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    callbacks=[streamlit_callback]
)

st.header("Chat Interface")

if "messages" not in st.session_state:
    st.session_state["messages"] = [{"role": "assistant", "content": "How can I help you?"}]

for msg in st.session_state.messages:
    with st.chat_message(msg["role"]):
        st.write(msg["content"])

user_query = st.chat_input(placeholder="Ask something from the database")

if user_query:
    st.session_state.messages.append({"function": "consumer", "content material": user_query})
    st.chat_message("consumer").write(user_query)

    with st.chat_message("assistant"):
        response_container = st.container()
        with response_container:
            response = agent.run(user_query)
            response_container.markdown(response)
        
        st.session_state.messages.append({"function": "assistant", "content material": response})

In essence, this code snippet brings the interactive chat performance to life. It allows customers to ask questions on their database in plain language and obtain informative responses, all inside a visually interesting and user-friendly Streamlit interface.

Conclusion

Now we have a very good quantity of enjoyable on this article strolling by means of join people and databases. By combining LangChain, GROQ, and Streamlit, we had this pushed AI SQL assistant offering pure language query answering again into your MySQL database.

We’ve witnessed how LangChain’s SQL toolkit acts because the translator, changing our plain-English questions into the structured language of SQL. GROQ, with its environment friendly API, seamlessly fetches and transforms the info we want. And Streamlit, with its intuitive interface, presents the ends in a visually partaking and interactive method.

You will discover Necessities.txt file right here: GitHub

Key Takeaway

• The code allows customers to question their MySQL database utilizing plain English, eliminating the necessity for complicated SQL syntax.
• LangChain SQL Toolkit leverages the LangChain framework to translate pure language queries into SQL, making database interactions extra accessible.
• GROQ is used for environment friendly information retrieval and transformation, doubtlessly enhancing efficiency and enabling complicated information operations.

Steadily Requested Questions

The media proven on this article isn’t owned by Analytics Vidhya and is used on the Creator’s discretion.

Hello I am Gourav, a Information Science Fanatic with a medium basis in statistical evaluation, machine studying, and information visualization. My journey into the world of knowledge started with a curiosity to unravel insights from datasets.
Outfitted with a strong instructional background in fields comparable to Machine Studying, arithmetic, and statistics, I continually try to remain on the forefront of technological developments within the information science area. I’m adept at using programming language Python to implement data-driven options and construct predictive fashions.