Methods to Construct a SQL Agent with CrewAI and Composio?

0
33


Introduction

SQL is definitely probably the most essential 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 big position together with analyzing complicated information, creating information pipelines, and effectively managing information warehouses. Nevertheless, writing optimized SQL queries can usually be difficult and cumbersome. However due to the fast progress in AI up to now few years, we now have AI brokers augmented with Massive Language Fashions able to writing queries on our behalf.

This text demonstrates the right way to construct an AI agent utilizing CrewAI, Composio, and Gemini to entry databases and execute SQL queries to retrieve information.

Studying Targets

  • Perceive what CrewAI is.
  • Find out about 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 Knowledge Science Blogathon.

What’s CrewAI?

CrewAI is an open-source collaborative multi-agent framework. It helps you to construct a crew of AI brokers with varied duties, instruments, roles, and motivations akin to a real-world crew. CrewAI manages the stream of data 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 basic unit of CrewAI and are chargeable for decision-making, performing duties, and speaking with different brokers.
  • Duties: These are the targets Brokers are motivated to perform. A process will be completed by one or many brokers.
  • Instruments: Instruments allow the Brokers to work together with the exterior atmosphere equivalent to utilizing an internet scrapper to retrieve the most recent information or a scheduler to schedule calendar occasions.
  • Course of: The Course of is chargeable for managing duties in CrewAI. It allocates duties to brokers in an outlined order. The method will 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 include collaborative brokers geared up with duties and instruments, all working collectively to perform complicated duties.

Here’s a mind-map CrewAI.

What is CrewAI?

Additionally Learn: Methods 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 gives over 150 instruments and functions 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 preserving real-world readiness of brokers in thoughts.

Composio gives 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 dwell 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 manage app actions. As soon as established, all customers can entry the identical integration by way of 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 equivalent to 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 may have an SQL question author agent and a coding agent to jot down and execute the queries. The SQL agent may have entry to the SQL device from Composio and the coding agent will be capable to use the E2B’s Codeinterpreter through Composio. The Codeinterpreter gives a sandboxed atmosphere for executing Python packages. 

The SQL agent will hook up with a neighborhood database and question from an acceptable desk. The information fetched from the SQL agent can 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 mission efficiently, you will want the Composio API key. First, create an account on Composio and we’ll clarify the right way to get API keys later. Additionally, get a free Gemini API key from Google AI studio, however bear in mind the free account is rate-limited. The execution might take longer.

Moreover, you will want a dummy database for executing queries. When you shouldn’t 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)

# Knowledge to insert into the 'worker' desk
workers = [
    ("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, workers)

# Commit the modifications
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 lined, we will begin with the coding half. As with all Python mission, we’ll first arrange a digital atmosphere and atmosphere variables, and set up libraries. The mission will use Gemini 1.5 Professional because the language mannequin.

Step1: Putting in Libraries

Create a digital atmosphere utilizing Python Venv.

python -m venv sqlagent
cd sqlagent
supply bin/lively

Set up the next libraries utilizing pip set up .

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

Step2: Set Setting Variables

To make use of Composio toolsets, you’ll want to authenticate your Composio account. Run the beneath command to log in to Composio and comply with the login stream.

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 mandatory libraries.

import os

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

# Load atmosphere 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’ll 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",
        objective="Run a code to get obtain a process given by the consumer",
        backstory="You might be an agent that helps customers run Python code.",
        verbose=True,
        instruments=code_interpreter_tools,
        llm=llm,
    )
    
sql_agent = Agent(
        position="SQL Agent",
        objective="Run SQL queries to get obtain a process given by the consumer",
        backstory=(
            "You might be 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, objective, and backstory. The extra data gives further 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 = Job(
        description=f"Run Python code to realize 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 = Job(
        description=f"Run SQL queries to realize 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 chargeable 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],
    )
consequence = crew.kickoff()
print(consequence)

You possibly can put this stream shortly loop to make it extra participating, 

whereas True:
    main_task = enter("Enter the duty you wish to carry out (or sort 'exit' to stop): ")
    if main_task.decrease() == "exit":
        break
        
    code_interpreter_agent = Agent(
        position="Python Code Interpreter Agent",
        objective="Run a code to get obtain a process given by the consumer",
        backstory="You might be an agent that helps customers run Python code.",
        verbose=True,
        instruments=code_interpreter_tools,
        llm=llm,
    )
    
     sql_agent = Agent(
        position="SQL Agent",
        objective="Run SQL queries to get obtain a process given by the consumer",
        backstory=(
            "You might be 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 = Job(
        description=f"Run Python code to realize 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 = Job(
        description=f"Run SQL queries to realize 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],
    )

    consequence = crew.kickoff()
    print(consequence)

It will 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 would output the file path for the plot’s picture.

Building an SQL Agent with CrewAI and Composio

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

Building an SQL Agent with CrewAI and Composio

GitHub Gist:  SQLsgent

These steps demonstrated the right way to 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 element to brokers and the Crew. It will assist the Brokers bear in mind their previous outcomes, which is able to make them steer the workflow higher, It’s also possible to 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 techniques. With frameworks like CrewAI and Composio, you may conveniently create AI workflows to automate many routine duties. This text demonstrates the right way to automate information extraction and visualization. You possibly can develop this workflow to deal with much more complicated situations.

Key Takeaways

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

Steadily Requested Questions

Q1. What’s CrewAI?

A. A.  CrewAI is an open-source agent orchestration framework for constructing role-playing and collaborative brokers.

Q2. What’s the distinction between CrewAI and Autogen?

A.  In Autogen, orchestrating brokers’ interactions requires further programming, which might turn out to be complicated and cumbersome as the size of duties grows. CrewAi has a simplified multi-agent AI implementation.

Q3. What can CrewAI do?

A. A. CrewAI helps you to construct collaborative multi-agent AI techniques to perform complicated automation workflows.

This autumn. Is CrewAI open supply?

A. A.  CrewAI is an open-source AI agent orchestration framework distributed beneath MIT license.

Q5. 5. What’s an SQL agent?

A. An SQL agent is an AI-augmented software program that may autonomously carry out SQL operations like querying, insertion, deletion, and updation.

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