Conversational Analytics Tutorial: How to Implement SQL AI Agent that Speaks Database

AI agents are transforming how we engage with technology by efficiently analyzing data, making informed decisions, and automating complex processes with little human input. From virtual assistants like Siri and Alexa to self-driving cars and cutting-edge cybersecurity solutions, these intelligent systems enhance efficiency, precision, and adaptability across various industries. For businesses looking to explore AI development services, these technologies can be key in shaping the future of their operations.

In this blog, I’ll guide you through building an SQLite AI agent that allows you to interact with an SQLite database using simple, natural language queries instead of writing complex SQL statements. This AI-powered agent will intelligently translate everyday language into SQL queries, making database interaction seamless for both developers and non-technical users, without the need to learn SQL syntax. We’ll dive into the tools and technologies behind this project, focusing on how machine learning models can enhance the querying process and improve database management. If you want a strategic approach, consider exploring AI strategy services to align your AI initiatives with broader business goals.

To build this agent, we’ll use the LangChain and Mistral 7B model running on Hugging Face, combining their strengths to transform natural language inputs into SQL queries, execute them, and fetch the results—streamlining and speeding up your workflow. Let’s dive into the process!

Step 1: Create a New Project Directory

mkdir sqlite_agent_project
cd sqlite_agent_project

Note: Ensure that the following prerequisites are installed on your Machine.  

## Prerequisites

– Python 3.11 or higher

– pip (Python package installer)

– A HuggingFace account and API token

Step 2: Create and Activate a Virtual Environment

 python3 -m venv venv

• The above command creates a new virtual environment
• First venv is the Python module that creates virtual environments
• Second venv is the name of the directory where the environment will be created
• Creates an isolated Python environment with its own:
  • Python interpreter
  • pip package manager
  • Standard library
  • Site-packages directory

 source venv/bin/activate

• The above  command activates the virtual environment
• venv/bin/activate is the path to the activation script

Step 3: Install the Required Dependencies

pip install langchain langchain-community langchain-experimental langchain-huggingface python-dotenv sqlalchemy

Here’s a quick overview of the dependencies we’ll be using to build our SQLiteAgent:

1. LangChain Components:

• langchain: The main framework for building AI-driven applications, providing tools for natural language processing and interacting with various data sources, including SQL databases.
• langchain-community: A collection of community-contributed tools to extend the functionality of LangChain.
• langchain-experimental: A package that includes cutting-edge features and tools that are still in development.
• langchain-huggingface: A module that integrates LangChain with HuggingFace models, allowing us to use pre-trained models for language tasks.

2. Supporting Packages:

• python-dotenv: A package that helps manage environment variables, making it easier to handle configuration in different environments.
• sqlalchemy: A powerful library for SQL database operations, enabling interaction with SQLite and other SQL databases.

Step 4: In the Project Directory, Create a .env File and Add Your HuggingFace API Token

HUGGINGFACE_API_TOKEN=your_token_here

You can generate a token from Hugging Face Tokens. Ensure the token has the following permissions:

• Make calls to Inference Providers
  
• Make calls to your Inference Endpoints

Step 5: Building the SQLite Agent Script

Create a file named sqlite_agent.py and insert the following code.

import sqlite3

from typing import List, Dict, Any

from langchain_huggingface import HuggingFaceEndpoint

from langchain_community.utilities import SQLDatabase

from langchain_experimental.sql import SQLDatabaseChain

import os

from dotenv import load_dotenv

class SQLiteAgent:

    def __init__(self, db_path: str):

        self.db_path = db_path

        # Load the Environment variables from .env file

        load_dotenv()

        # Initialize the LLM with HuggingFace

        self.llm = HuggingFaceEndpoint(

            repo_id="mistralai/Mistral-7B-Instruct-v0.2",

            task="text-generation",

            temperature=0.1,

            max_new_tokens=250,

            do_sample=False,

            model_kwargs={

                "max_length": 512

            },

            huggingfacehub_api_token=os.getenv("HUGGINGFACE_API_TOKEN"),

            client_options={"timeout": 60.0}

        )

        # Initialize the database

        self.db = SQLDatabase.from_uri(f"sqlite:///{db_path}")

        # Create the chain

        self.chain = SQLDatabaseChain.from_llm(

            llm=self.llm,

            db=self.db,

            verbose=True

        )

    def query(self, question: str) -> str:

        """Execute a natural language query on the database."""

        try:

            result = self.chain.invoke({"query": question})

            return f"Result: {result['result']}"

        except Exception as e:

            return f"Error executing query: {str(e)}"

    def get_table_info(self) -> List[Dict[str, Any]]:

        """Retrieve information about all the tables in the database."""

        try:

            with sqlite3.connect(self.db_path) as conn:

                cursor = conn.cursor()

                cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

                tables = cursor.fetchall()

                table_info = []

                for table in tables:

                    table_name = table[0]

                    cursor.execute(f"PRAGMA table_info({table_name});")

                    columns = cursor.fetchall()

                    table_info.append({

                        "table_name": table_name,

                        "columns": [col[1] for col in columns]

                    })

                return table_info

        except Exception as e:

            print(f"Error getting table info: {str(e)}")

            return []

if __name__ == "__main__":

    # Initialize the agent with the existing database

    db_path = 'employee_database.db'

    if not os.path.exists(db_path):

        print(f"Error: Database file '{db_path}' not found!")

        print("Please make sure the employee_database.db file exists in the current directory.")

        exit(1)

    # Initialize the agent

    agent = SQLiteAgent(db_path)

    # Display table information

    table_info = agent.get_table_info()

    print("\nAvailable tables in the database:")

    for table in table_info:

        print(f"\nTable: {table['table_name']}")

        print("Columns:", ", ".join(table['columns']))

    print("\nSQLite Agent initialized! You can ask questions about the database.")

    print("\nExample questions:")

    print("- What is the average salary in the Engineering department?")

    print("- Who are all the employees in the Marketing department?")

    print("- What is the total number of employees?")

    print("\nType 'exit' to quit.\n")

    while True:

        question = input("Your question: ")

        if question.lower() == 'exit':

            break

        result = agent.query(question)

        print(result)

If you want to learn more about building AI agents, check out this AI Agent Framework Tutorial: Building AI Agents using CrewAI for an in-depth look.

Let’s understand the code and see how our agent functions.

The Imports and Their Purposes

• import sqlite3 – Handles SQLite database operations.
• from typing import List, Dict, Any – Provides type hints for better code readability.
• from langchain_huggingface import HuggingFaceEndpoint – Integrates Hugging Face models.
• from langchain_community.utilities import SQLDatabase – Offers database utility functions.
• from langchain_experimental.sql import SQLDatabaseChain – Manages SQL chain processing.
• import os – Supports file and path operations.
• from dotenv import load_dotenv – Manages environment variables.

Class Definition and HuggingFace Model Setup

class SQLiteAgent:

    def __init__(self, db_path: str):

        self.db_path = db_path

        # Load the Environment variables from .env file

        load_dotenv()

        # Initialize the LLM with HuggingFace

                self.llm = HuggingFaceEndpoint(

            repo_id="mistralai/Mistral-7B-Instruct-v0.2",

            task="text-generation",

            temperature=0.1,

            max_new_tokens=250,

            do_sample=False,

            model_kwargs={

                "max_length": 512

            },

            huggingfacehub_api_token=os.getenv("HUGGINGFACE_API_TOKEN"),

            client_options={"timeout": 60.0}

        )

...

In the above code block, we perform the following operations:

• Load environment variables from the .env file.
• Initialize the LLM using Hugging Face.
• Set the model to Mistral-7B-Instruct-v0.2.
• Define parameters to control the model’s behavior:
  • Perform a text generation task
  • Set a low temperature (0.1) for precision
  • Limit the generation to a maximum of 250 new tokens
  • Disable sampling for consistency
  • Set a total length limit of 512 tokens.
• Retrieve and set huggingfacehub_api_token value from the .env file.

Initialize the Database and Chain setup 

# Initialize the database

        self.db = SQLDatabase.from_uri(f"sqlite:///{db_path}")

        # Create the chain

        self.chain = SQLDatabaseChain.from_llm(

            llm=self.llm,

            db=self.db,

            verbose=True

        )       Code language: PHP (php)

In the above code block, we are initializing the database and creating the processing chain. 

Query Method

def query(self, question: str) -> str:

        """Execute a natural language query on the database."""

        try:

            result = self.chain.invoke({"query": question})

            return f"Result: {result['result']}"

        except Exception as e:

            return f"Error executing query: {str(e)}"    Code language: PHP (php)

The query method takes a natural language question and processes it by sending it to the AI model, which converts the question into SQL. The SQL query is then executed on the database, and the result is returned.

The flow is as follows: Question → AI → SQL → Database → Answer.

Table Information Method

def get_table_info(self) -> List[Dict[str, Any]]:

        """Retrieve information about all the tables in the database."""

        try:

            with sqlite3.connect(self.db_path) as conn:

                cursor = conn.cursor()

                cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

                tables = cursor.fetchall()

                table_info = []

                for table in tables:

                    table_name = table[0]

                    cursor.execute(f"PRAGMA table_info({table_name});")

                    columns = cursor.fetchall()

                    table_info.append({

                        "table_name": table_name,

                        "columns": [col[1] for col in columns]

                    })

                return table_info

        except Exception as e:

            print(f"Error getting table info: {str(e)}")

            return []Code language: PHP (php)

The get_table_info method performs the following tasks

• Analyzes the database structure 
• Displays available tables and columns
• Guides users on what can be queried

Main Execution Block and User Interface Setup

if __name__ == "__main__":

    # Initialize the agent with the existing database

    db_path = 'employee_database.db'

    if not os.path.exists(db_path):

        print(f"Error: Database file '{db_path}' not found!")

        print("Please make sure the employee_database.db file exists in the current directory.")

        exit(1)

    # Initialize the agent

    agent = SQLiteAgent(db_path)

    # Get and display table information

    table_info = agent.get_table_info()

    print("\nAvailable tables in the database:")

    for table in table_info:

        print(f"\nTable: {table['table_name']}")

        print("Columns:", ", ".join(table['columns']))

    print("\nSQLite Agent initialized! You can ask questions about the database.")

    print("\nExample questions:")

    print("- What is the average salary in the Engineering department?")

    print("- Who are all the employees in the Marketing department?")

    print("- What is the total number of employees?")

    print("\nType 'exit' to quit.\n")

    while True:

        question = input("Your question: ")

        if question.lower() == 'exit':

            break

        result = agent.query(question)

        print(result)Code language: PHP (php)

The above code block is our main function, which performs the below-mentioned tasks:

• Initializes the employee_database, an SQLite database that is generated using the Faker Python package.
• Verifies if the database exists, ensuring that the required SQLite database is available for operations.
• Initializes an SQLite AI agent responsible for interacting with the database and processing queries.
• Displays the schema of the database, including available tables and columns, to guide users in constructing valid queries.
• It begins an interactive question-and-answer loop, allowing users to submit queries and receive responses based on the database content.

Step 6: Running and Executing the SQLite Agent

Once you’ve completed the previous steps, your project directory structure should appear as follows:

sqlite_agent_project/

├── sqlite_agent.py

├── .env

└── employee_database.db 

└── venv

• Activate the virtual environment by executing the below command in your project directory

 source venv/bin/activate

• Run the Agent

 python3 sqlite_agent.pyCode language: CSS (css)

After running the agent, it will show the available tables and their columns in the SQLite database, along with example questions you can ask. And just like that, our AI-powered agent is up and running!

Let’s give our agent a test run…

Note: The agent uses the Mistral 7B Model running on Hugging Face for natural language processing. While this implementation is free to use, it requires a HuggingFace API token. The model may have rate limits or usage restrictions based on your HuggingFace account type.

For those interested in expanding their knowledge of building AI agents, I highly recommend checking out this video tutorial on creating AI agents using the Crew AI framework. This video is perfect for beginners and anyone looking to improve their automation skills, providing a step-by-step guide on how to create intelligent AI agents.

Summing It Up

I hope you enjoyed the SQLite AI agent tutorial, which provides an intelligent interface that bridges natural language and databases. This allows users to query SQLite databases using plain English. Powered by the Mistral-7B AI model, it automatically converts natural language questions into SQL queries, executes them, and returns results in a human-readable format. You can download the script here.

Author's Bio