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

Introduction

Have you ever ever wished you could possibly merely chat along with your database, asking questions in plain language and getting immediate, related solutions? Think about the probabilities – no extra advanced SQL queries or digging by spreadsheets. Properly, 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’ll discover how with GROQ API, Streamlit, and LangChain SQL toolkit, you’ll be able to create your chat utility in your MySQL database. Be it as an information fanatic, developer, or simply enthusiastic to see potential, everybody goes to achieve worth insights and a few sensible information to unseal the true energy of your knowledge.

Studying Outcomes

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

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

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

Databases-the warehouses of extremely helpful, structured information-have been the spine of 1000’s and 1000’s of purposes. In robustness, ease of use, and vast utilization, nonetheless, one stands out: MySQL, the favored open-source relational database administration system. It affords a structured approach of storing, organizing, and retrieving knowledge 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 attempting to crack a cryptic code. Highly effective as they’re, the previous approach of utilizing SQL queries usually 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 similar to chatting with a colleague? What if we might visualize the information immediately in stunning charts and graphs, making developments and patterns leap off the display screen?

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 knowledge that we’re in search of with pinpoint accuracy as a consequence of its intuitive API. Lastly, Streamlit is a strong Python library for constructing net purposes, offering the canvas that permits us to color the masterpiece pushed by our knowledge. Streamlit helps simplicity and ease in fast 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’ll be able to, because of LangChain, GROQ, and Streamlit. On this tutorial, we’ll learn to construct a strong, user-friendly utility that permits you to chat along with your MySQL database like an knowledgeable assistant whereas having fun with the real-time knowledge presentation great thing about Streamlit.

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

Tech Stack Used

LangChain is subsequently an adaptable platform for AI that allows the interplay between a human and a wide range of knowledge sources to circulation. Very like a bridge helps join our pure expressions in language with the structured world of databases, APIs, and different kinds 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 understand the intent behind our requests and generate the corresponding SQL code. This not solely simplifies the information retrieval course of but in addition opens up database entry to a wider viewers, together with these with out intensive SQL information.

Setting Up Required Packages

To get your utility up and operating easily, you’ll want to put in a number of important packages. This step ensures that every one vital libraries and instruments can be found for the seamless integration of LangChain, GROQ, and Streamlit along 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

Growing Your MySQL Chat Interface

Let’s carry our ideas to life! The next code combines the ability of LangChain’s SQL toolkit, GROQ’s knowledge fetching capabilities, and Streamlit’s intuitive UI to create a conversational interface to your MySQL database. We’ll stroll by 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 submit info.

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

insert into customers(userId,userName,electronic 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 carry 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 along with your MySQL database.

Import Required Libraries

Allow us to begin by importing required libraries beneath:

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 utility that leverages LangChain, GROQ, and SQLAlchemy to allow pure language interactions along 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 leads to a user-friendly method inside your Streamlit app.

Streamlit Configuration

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

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

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 choice", ["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 Person")
        mysql_password = st.text_input("MySQL Password", kind="password")
        mysql_db = st.text_input("MySQL Database")
        api_key = st.text_input("Groq API Key", kind="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.data("This AI SQL Assistant makes use of LangChain and Groq to supply 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 learn 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 quick description of the app’s performance.

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

Dealing with Person 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 follow by together with enter validation to enhance the robustness of your utility.
• The usage of 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 forestall errors and improves the consumer expertise
• SQLAlchemy offers a sturdy approach to hook up with and work together along 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("The right way 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 along 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

We have now an excellent quantity of enjoyable on this article strolling by easy methods to 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 information we want. And Streamlit, with its intuitive interface, presents the leads to a visually participating and interactive method.

You could find Necessities.txt file right here: GitHub

Key Takeaway

• The code allows customers to question their MySQL database utilizing plain English, eliminating the necessity for advanced 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 knowledge retrieval and transformation, probably enhancing efficiency and enabling advanced knowledge operations.

Steadily Requested Questions

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