The way to Construct a SQL Agent with CrewAI and Composio?

Introduction

SQL is well one of the necessary languages within the pc world. It serves as the first means for speaking with relational databases, the place most organizations retailer essential information. SQL performs a major position together with analyzing advanced information, creating information pipelines, and effectively managing information warehouses. Nevertheless, writing optimized SQL queries can usually be difficult and cumbersome. However because of the fast progress in AI prior to now few years, we now have AI brokers augmented with Giant Language Fashions able to writing queries on our behalf.

This text demonstrates how you can construct an AI agent utilizing CrewAI, Composio, and Gemini to entry databases and execute SQL queries to retrieve information.

Studying Goals

• Perceive what CrewAI is.
• Study Composio instruments and integrations.
• Perceive the workflow of the AI agent.
• A step-by-step information to constructing an SQL agent utilizing Composio and CrewAI.

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

What’s CrewAI?

CrewAI is an open-source collaborative multi-agent framework. It permits you to construct a crew of AI brokers with varied duties, instruments, roles, and motivations akin to a real-world crew. CrewAI manages the circulation of knowledge from one agent to a different, letting you construct autonomous environment friendly agentic workflows.

CrewAI primarily consists of 5 core options Brokers, Duties, Instruments, Processes, and Duties.

• Brokers: Brokers are the elemental unit of CrewAI and are liable for decision-making, performing duties, and speaking with different brokers.
• Duties: These are the objectives Brokers are motivated to perform. A process might be completed by one or many brokers.
• Instruments: Instruments allow the Brokers to work together with the exterior setting resembling utilizing an internet scrapper to retrieve the most recent information or a scheduler to schedule calendar occasions.
• Course of: The Course of is liable for managing duties in CrewAI. It allocates duties to brokers in an outlined order. The method might be sequential, hierarchical, or consensual. In a sequential course of, one process follows one other; in a hierarchical course of, a managerial hierarchy dictates the order of duties; and in a consensual course of, brokers carry out duties collaboratively.
• Crews: Crews inside CrewAI encompass collaborative brokers geared up with duties and instruments, all working collectively to perform advanced duties.

Here’s a mind-map CrewAI.

Additionally Learn: The way to Construct a Collaborative AI Brokers With CrewAI?

What’s Composio?

Composio is an open-source platform that gives tooling options for constructing dependable and helpful AI brokers. Composio supplies over 150 instruments and purposes with built-in consumer authentication and authorization to assist builders construct dependable, safe, and production-ready agentic workflows. The instruments have been designed from the bottom up protecting real-world readiness of brokers in thoughts.

Composio provides a number of benefits over different tooling options, together with managed consumer authentication and authorizations, a wide selection of instruments and integrations, a dashboard for monitoring stay integrations, and the pliability so as to add customized instruments. 

Composio has 4 key ideas.

• Entities: In Composio, an “entity” is a container for all consumer or group accounts and instruments, permitting centralized administration from a single dashboard.
• Integrations: Integrations hyperlink your account with exterior apps, involving the setup of authentication mechanisms like OAuth and defining entry permissions to regulate app actions. As soon as established, all customers can entry the identical integration by their accounts.
• Actions: are duties carried out by built-in instruments, like sending a Slack message or scheduling a calendar occasion.
• Triggers: Predefined circumstances set off webhooks to your brokers when met, sending occasion particulars resembling entities, message textual content, and extra.

Designing an AI-Powered SQL Agent Workflow

Right here, you’ll create an agentic system, which takes a consumer question concerning the info saved in a database, fetches the related information utilizing an SQL agent, and creates good plots to visualise the info. For this workflow, we will use CrewAI to orchestrate brokers and Composio for tooling assist.

The magnetic system can have an SQL question author agent and a coding agent to put in writing and execute the queries. The SQL agent can have entry to the SQL device from Composio and the coding agent will be capable of use the E2B’s Codeinterpreter by way of Composio. The Codeinterpreter supplies a sandboxed setting for executing Python packages. 

The SQL agent will hook up with an area database and question from an applicable desk. The information fetched from the SQL agent shall be utilized by the Coding agent to create plots. The crew will return the plot as the ultimate output.

Conditions for Constructing the SQL Agent

To run this challenge efficiently, you have to the Composio API key. First, create an account on Composio and we are going to clarify how you can get API keys later. Additionally, get a free Gemini API key from Google AI studio, however keep in mind the free account is rate-limited. The execution could take longer.

Moreover, you have to a dummy database for executing queries. For those who should not have a spare database, run the next code to create an worker desk with names, departments, and salaries.

import sqlite3

# Hook up with the SQLite database
connection = sqlite3.join('firm.db')

# Create a cursor object
cursor = connection.cursor()

# Create the 'worker' desk
create_table_query = '''
CREATE TABLE IF NOT EXISTS worker (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_name TEXT NOT NULL,
    division TEXT NOT NULL,
    wage INTEGER NOT NULL
);
'''
cursor.execute(create_table_query)

# Information to insert into the 'worker' desk
staff = [
    ("John Doe", "Engineering", 70000),
    ("Jane Smith", "Human Resources", 65000),
    ("Alice Johnson", "Marketing", 72000),
    ("Bob Brown", "Sales", 68000),
    ("Charlie Black", "Engineering", 71000),
    ("Daisy White", "Human Resources", 66000),
    ("Edward Green", "Marketing", 69000),
    ("Fiona Grey", "Sales", 64000),
    ("George Yellow", "Engineering", 73000),
    ("Hannah Blue", "Human Resources", 61000),
    ("Ivan Purple", "Marketing", 75000),
    ("Jessica Cyan", "Sales", 70000),
    ("Kyle Red", "Engineering", 68000),
    ("Lily Orange", "Human Resources", 67000),
    ("Martin Indigo", "Marketing", 72000),
    ("Nina Teal", "Sales", 65000),
    ("Oscar Lime", "Engineering", 73000),
    ("Penny Olive", "Human Resources", 62000),
    ("Quentin Silver", "Marketing", 74000),
    ("Rachel Maroon", "Sales", 69000),
    ("Steve Pink", "Engineering", 71000),
    ("Tina Violet", "Human Resources", 68000),
    ("Ursula Gold", "Marketing", 76000),
    ("Victor Bronze", "Sales", 64000),
    ("Wendy Mauve", "Engineering", 69000),
    ("Xavier Cream", "Human Resources", 65000),
    ("Yolanda Peach", "Marketing", 70000),
    ("Zack Sage", "Sales", 68000),
    ("Abby Coral", "Engineering", 72000),
    ("Bill Moss", "Human Resources", 63000)
]

# Insert information into the 'worker' desk
insert_query = 'INSERT INTO worker (employee_name, division, wage) VALUES (?, ?, ?);'
cursor.executemany(insert_query, staff)

# Commit the adjustments
connection.commit()

# Shut the connection
connection.shut()

print("Desk created and information inserted efficiently.")

Step-by-Step Information to Creating an SQL Agent

Now, that the fundamentals are coated, we are able to begin with the coding half. As with every Python challenge, we are going to first arrange a digital setting and setting variables, and set up libraries. The challenge will use Gemini 1.5 Professional because the language mannequin.

Step1: Putting in Libraries

Create a digital setting utilizing Python Venv.

python -m venv sqlagent
cd sqlagent
supply bin/energetic

Set up the next libraries utilizing pip set up .

composio-core
composio-crewai
langchain-google-genai
dotenv

Step2: Set Surroundings Variables

To make use of Composio toolsets, it is advisable to authenticate your Composio account. Run the beneath command to log in to Composio and comply with the login circulation.

composio login

Now, get your Composio API keys.

Composio whoami

Create a .env file and add COMPOSIO_API_KEY and GOOGLE_API_KEY variables to it.

COMPOSIO_API_KEY=your Composio API key
GOOGLE_API_KEY=your Gemini API key

Step3: Outline Instruments and LLM

Now, create a Python file and import the required libraries.

import os

import dotenv
from composio_langchain import App, ComposioToolSet
from crewai import Agent, Crew, Course of, Activity
from langchain_google_genai import ChatGoogleGenerativeAI

# Load setting variables from the .env file
dotenv.load_dotenv()

Outline Composio instruments.

# Initialize the ComposioToolSet
toolset = ComposioToolSet(api_key=os.environ["COMPOSIO_API_KEY"])

code_interpreter_tools = toolset.get_tools([App.CODEINTERPRETER])
sql_tools = toolset.get_tools([App.SQLTOOL])

Now, outline the LLM with Gemini 1.5 Professional.

llm = ChatGoogleGenerativeAI(mannequin="gemini-1.5-pro", 
                             api_key=os.environ['GOOGLE_API_KEY']
                          )

Step4: Outline Brokers and Duties

As mentioned earlier, we are going to right here outline two brokers and two duties. The SQL agent, Coding agent, and their respective duties.

code_interpreter_agent = Agent(
        position="Python Code Interpreter Agent",
        purpose="Run a code to get obtain a process given by the consumer",
        backstory="You're an agent that helps customers run Python code.",
        verbose=True,
        instruments=code_interpreter_tools,
        llm=llm,
    )
    
sql_agent = Agent(
        position="SQL Agent",
        purpose="Run SQL queries to get obtain a process given by the consumer",
        backstory=(
            "You're an agent that helps customers run SQL queries. "
            "Hook up with the native SQLite DB at connection string = firm.db"
            "Attempt to analyze the tables first by itemizing all of the tables and columns "
            "and doing distinct values for every column and as soon as positive, make a question to 
            get the info you want."
        ),
        verbose=True,
        instruments=sql_tools,
        llm=llm,
        allow_delegation=True,
    )

Within the above code snippet, we outlined the brokers, every with an outlined position, purpose, and backstory. The extra data provides extra context to LLMs earlier than producing responses to queries. A device equips every agent to carry out the actions.

Now, outline duties.

code_interpreter_task = Activity(
        description=f"Run Python code to attain the duty - {main_task}. 
        Exit as soon as the picture has been created.",
        expected_output="Python code executed efficiently. Return the picture path.",
        agent=code_interpreter_agent,
    )
    
   sql_task = Activity(
        description=f"Run SQL queries to attain a process - {main_task}",
        expected_output=f"SQL queries executed efficiently. The results of the duty 
        is returned - {main_task}",
        agent=sql_agent,
    )

We outlined the duties that the brokers will carry out. Every process has an outline, anticipated output, and the agent liable for performing it.

Now, outline the Crew with the brokers and the duties.

crew = Crew(
        brokers=[sql_agent, code_interpreter_agent],
        duties=[sql_task, code_interpreter_task],
    )
end result = crew.kickoff()
print(end result)

You possibly can put this circulation shortly loop to make it extra partaking, 

whereas True:
    main_task = enter("Enter the duty you wish to carry out (or kind 'exit' to stop): ")
    if main_task.decrease() == "exit":
        break
        
    code_interpreter_agent = Agent(
        position="Python Code Interpreter Agent",
        purpose="Run a code to get obtain a process given by the consumer",
        backstory="You're an agent that helps customers run Python code.",
        verbose=True,
        instruments=code_interpreter_tools,
        llm=llm,
    )
    
     sql_agent = Agent(
        position="SQL Agent",
        purpose="Run SQL queries to get obtain a process given by the consumer",
        backstory=(
            "You're an agent that helps customers run SQL queries. "
            "Hook up with the native SQLite DB at connection string = firm.db"
            "Attempt to analyze the tables first by itemizing all of the tables and columns "
            "and doing distinct values for every column and as soon as positive, make a question to 
            get the info you want."
        ),
        verbose=True,
        instruments=sql_tools,
        llm=llm,
        allow_delegation=True,
    )
    code_interpreter_task = Activity(
        description=f"Run Python code to attain the duty - {main_task}. 
        Exit as soon as the picture has been created.",
        expected_output="Python code executed efficiently. Return the picture path.",
        agent=code_interpreter_agent,
    )
    
    sql_task = Activity(
        description=f"Run SQL queries to attain a process - {main_task}",
        expected_output=f"SQL queries executed efficiently. The results of the duty 
        is returned - {main_task}",
        agent=sql_agent,
    )


    crew = Crew(
        brokers=[sql_agent, code_interpreter_agent],
        duties=[sql_task, code_interpreter_task],
    )

    end result = crew.kickoff()
    print(end result)

This can immediate you to enter a question, which is able to then be handed to the Crew of AI brokers. After execution, you’ll have the choice to both ask one other query or exit the loop.

As soon as the execution of a question is accomplished, it should output the file path for the plot’s picture.

I requested it to create a bar plot of the variety of staff in every division. This was the end result.

GitHub Gist:  SQLsgent

These steps demonstrated how you can construct an agentic workflow to automate SQL information extraction and visualization. Nevertheless, you may go additional, and make it extra sturdy and dependable by including a reminiscence part to brokers and the Crew. This can assist the Brokers keep in mind their previous outcomes, which is able to make them steer the workflow higher, You can too add a frontend with Streamlit or Gradio, to make it extra interactive. 

Conclusion

The AI panorama is evolving at an unprecedented tempo. As the standard of AI fashions, frameworks, and instruments continues to enhance, constructing highly effective AI brokers is turning into more and more handy every day.  The way forward for the workforce is agentic, the place people and AI will complement one another to create much more environment friendly programs. With frameworks like CrewAI and Composio, you may conveniently create AI workflows to automate many routine duties. This text demonstrates how you can automate information extraction and visualization. You possibly can increase this workflow to deal with much more advanced situations.

Key Takeaways

• CrewAI is an open-source framework for orchestrating LLM brokers to collaboratively accomplish advanced duties by assigning roles, sharing objectives, and delegating duties.
• Composio is an open-source tooling platform that provides production-ready instruments and integrations to empower AI brokers to perform duties reliably.
• You possibly can combine Composio instruments with well-liked platforms like LangChain, Autogen, CrewAI, and Llamaindex.

Steadily Requested Questions

The media proven on this article will not be owned by Analytics Vidhya and is used on the Writer’s discretion.