Text-to-SQL
The Text-to-SQL Tool (TextToSQLTool
) is a powerful component in the Canso toolkit that enables AI agents to convert natural language queries into valid SQL statements. It leverages underlying language models to understand user intent and generate appropriate SQL code based on database schema information.
Overview
The Text-to-SQL Tool bridges the gap between natural language understanding and database querying, allowing agents to:
Parse natural language questions about data
Convert these questions into valid, optimized SQL queries
Maintain context about database schemas and relationships
Leverage examples and domain knowledge for better query generation
Usage
The constructor for the TextToSQLTool
has the following parameters:
service
An instance of TextToSQLService
with configured LLM and context retriever
Yes
The input parameters for the tool, provided at runtime, include:
query
Natural language query to convert to SQL
"Show me all customers who made purchases last month"
Yes
table_info
Optional table schema information to initialize or update context
[{"table": "customers", "schema": "CREATE TABLE..."}]
No
Integration
The following code snippet illustrates how the TextToSQLTool
can be integrated with your AI Agent:
from gru.agents.tools.text_to_sql import TextToSQLTool
from gru.agents.tools.core.services.text_to_sql import TextToSQLService
from gru.agents.tools.core.llm_client.openai import OpenAILLMClient
from gru.agents.tools.core.context_retriever.sql import SQLContextRetriever
from gru.agents.tools.core.vector_db.milvus import MilvusClient
from langchain_openai import ChatOpenAI
from langgraph.prebuilt import ToolNode
# Initialize dependencies
llm_client = OpenAILLMClient(model="gpt-4o")
vector_store = MilvusClient()
context_retriever = SQLContextRetriever(vector_store=vector_store)
# Create the service
text_to_sql_service = TextToSQLService(
llm_client=llm_client,
context_retriever=context_retriever
)
# Initialize the tool
text_to_sql_tool = TextToSQLTool(service=text_to_sql_service)
# Add to your agent's tools
tools = [text_to_sql_tool]
tool_node = ToolNode(tools)
model = ChatOpenAI(model="gpt-4o", temperature=0)
model = model.bind_tools(tools)
Memory Integration
The TextToSQLTool
can leverage CansoMemory to:
Store and retrieve database schemas
Save example queries and their SQL translations
Learn from past query conversions
Maintain domain-specific knowledge related to databases
Memory-enhanced Text-to-SQL transformations become increasingly accurate as the system learns from more examples and builds a richer context of your database structure.
Example
When a user asks a natural language question:
"Which customers spent more than $1000 last quarter and haven't made a purchase this month?"
The TextToSQLTool
can generate a SQL query like:
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
JOIN (
SELECT customer_id, SUM(total_amount) as quarterly_spend
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
) q ON c.customer_id = q.customer_id
WHERE c.customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2024-04-01'
)
ORDER BY q.quarterly_spend DESC;
Tool Tips
Go to Toolkit ⬅️
See SQL Runner Tool ➡️
Learn about Memory ➡️
Explore Examples ➡️
Last updated
Was this helpful?