In this tutorial Agent to answer questions about SQL database Introducing how to build step by step.
The flow of agents running SQL queries is:
Identify database schemas : Get a list of available tables.
Select related table : Select the table associated with the question.
DDL lookup : Get the schema definition (DDL) of the selected table.
Query generation : Create SQL queries based on questions and DDL information.
Query check : Use LLM to review common errors and improve queries.
Query execution and error handling : Run the query on the database engine, fix it in case of an error, and successfully perform the query.
Create response : Provides final answer based on query results.
Main contents
database : SQLite database settings and chinook Sample database load
Utility function : Define utility functions for agent implementation
Tool definition : Define tools to interact with the database
Define walkflow : Define the agent's workflow (graph)
Graph visualization : Defined graph visualization
Agent execution : Agent execution and results verification
evaluation : Agent evaluation and performance comparison
Preferences
First, install the required package and set the API key.
Copy
Copy
Copy
Copy
Copy
Copy
Database settings
This tutorial creates an SQLite database. SQLite is a lightweight database that is easy to set up and use.
In this tutorial, it is a sample database chinook The database will be loaded, which is a sample database representing the digital media store.
For more information about the database here You can check in. First, use it for practice chinook Download the database.
Copy
Copy
Next downloadable chinook Using the database SQLDatabase Generate tools and sample queries "SELECT * FROM Artist LIMIT 5;" Run.
Copy
Copy
Copy
If error occurs, it returns an error message.
db_query_tool In the case of, run the query against the database and return the results.
next db_query_tool Defines.
Copy
Copy
Below list_tables_tool and get_schema_tool Example of execution for.
Copy
Copy
function : Validation before Query runs
Inspection items :
Using NULL values and NOT IN
UNION vs UNION ALL adequacy
BETWEEN range setting
Data type match
Identifier citation adequacy
Number of Function arguments
Data type casting
Join column accuracy
Features : GPT-4 model based verification
QuerySQLCheckerTool
function : Database View my table list
ListSQLDatabaseTool
function : Table schema and sample data lookup
Input : List of tables separated by comas
Example of use : table1, table2, table3
caution : sql_db_list_tables Pre-check availability as table
InfoSQLDatabaseTool
function : Execute SQL query and return results
Input : Accurate SQL query
Output : Database results or error message
Error processing :
Query error generation and retry
Unknown column Error sql_db_schema Check accurate table fields with
QuerySQLDataBaseTool
The following is intended to interact with SQL database. SQLDatabaseToolkit List of tools.
Database query related tools
CREATE : Create database objects.
Yes: CREATE TABLE users (id INT, name VARCHAR(100));
ALTER : Modifies existing database objects.
Yes: ALTER TABLE users ADD COLUMN email VARCHAR(100);
DROP : Delete database objects.
Yes: DROP TABLE users;
Main DDL instructions
DDL (Data Definition Language, Data Definition Language ) Refers to the structure of the database and the SQL instructions used to define or modify the schema. Mainly used to create, modify, and delete database objects such as tables, indexes, views, and schemas.
Reference
list_tables_tool : Get the available tables from the database.
get_schema_tool : Get the DDL of the table.
db_query_tool : Run the query and get the result or return an error message if the query fails.
Define some tools to help agents interact with the database.
SQL Query Execution Tool
Copy
Copy
When running normally
Copy
Copy
Example if an error occurs
Copy
Copy
SQL Query Checker
Next, we will use LLM to check for common mistakes in SQL queries.
This is not a tool, strictly speaking, but will be added as a node to the subsequent workflow.
Copy
Blow and call the wrong query to make sure the results are well corrected.
Reference
LIMIT instead LIMITS I used to blow the query.
Copy
Copy
The results were well corrected.
Graph definition
Defines the agent's workflow.
Agent first list_tables_tool After forcibly calling to get the available table from the database, follow the steps mentioned at the beginning of the tutorial.
Copy
Copy
Copy
Copy
Copy
Copy
Copy
SQL Agent evaluation using LangSmith Evaluator
Now evaluate the SQL query response of the Agent you created. Generate a data set for evaluation to evaluate query responses.
Next, define the evaluator and proceed with the evaluation.
The evaluators utilized at this time are LLM-as-judge, and the prompts used utilize the prompts provided by the default hub.
However, it is recommended to tune and use each prompt for a more accurate evaluation.
Copy
The following defines the function to predict the SQL query response of the agent we created.
Copy
Defines the prompt and evaluator (LLM-as-judge) to evaluate SQL query responses.
Copy
Now, conduct an assessment and confirm the results.
Copy
Copy
Copy
Copy
The evaluation results can be checked at the URL created.
# Configuration file for managing API keys as environment variables
from dotenv import load_dotenv
# Load API key information
load_dotenv()
True
# Set up LangSmith tracking. https://smith.langchain.com
# !pip install -qU langchain-teddynote
from langchain_teddynote import logging
# Enter a project name.
logging.langsmith("CH17-LangGraph-Use-Cases")
from langchain_teddynote.models import get_model_name, LLMs
MODEL_NAME = get_model_name(LLMs.GPT4o)
print(f"Model name used: {MODEL_NAME}")
Model name used: gpt-4o
import requests
url = "https://storage.googleapis.com/benchmarks-artifacts/chinook/Chinook.db"
response = requests.get(url)
if response.status_code == 200:
with open("Chinook.db", "wb") as file:
file.write(response.content)
print("File downloaded and saved as Chinook.db")
else:
print(f"Failed to download the file. Status code: {response.status_code}")
File downloaded and saved as Chinook.db
from langchain_community.utilities import SQLDatabase
# Creating a SQLDatabase instance from a SQLite database file
db = SQLDatabase.from_uri("sqlite:///Chinook.db")
# DB dialect ouput of power(sqlite)
print(db.dialect)
# Output a list of table names available in the database.
print(db.get_usable_table_names())
# Execute SQL queries
db.run("SELECT * FROM Artist LIMIT 5;")
"[(1,'AC/DC'), (2,'Accept'), (3,'Aerosmith'), (4,'Alanis Morissette'), (5,'Alice In Chains')]"
Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
CREATE TABLE "Artist" (
"ArtistId" INTEGER NOT NULL,
"Name" NVARCHAR (120),
PRIMARY KEY ("ArtistId")
)
/*
3 rows from Artist table:
ArtistId Name
1 AC/DC
2 Accept
3 Aerosmith
*/
# Select a tool to list the tables available in your database
list_tables_tool = next(tool for tool in tools if tool.name == "sql_db_list_tables")
# Select a tool to retrieve the DDL of a specific table
get_schema_tool = next(tool for tool in tools if tool.name == "sql_db_schema")
# Output a list of all tables in the database
print(list_tables_tool.invoke(""))
# Output DDL information of artist table
print(get_schema_tool.invoke("Artist"))
[QuerySQLDatabaseTool (description=" Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be be returned. If an error is returned, rewrite the query, check the query, and try again. If you engineer an issue with Unknown column'xxxx' in'field list', use sql_db_schema to query the correct table fields.", db= ), InfoSQLDatabaseTool (description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for thatose tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db= ), ListSQLDatabaseTool (db= ), QuerySQLCheckerTool (description='Use this tool to double check if your query is correct before checking it. Always use this tool before checking a query with sql_db_query!', db= , llm=ChatOpenAI (client= , async_client= , root_client= , root_async_client= , model_name='gpt-4o', model_kwargs={}, openai_api_key=SecretStr('*********'), llm_chain=LLMChain (verbose=False, promptn- Using UNION when UNION ALL should have be used\n- Using BETWEEN for exclusive ranges\n- Data type mismatch in predicates\n- Property quoting identifiers\n- Using the correct number If there are no mistakes, just reproduce the original query.\n\nOutput the final SQL query only.\n\nSQL Query:'), llm=ChatOpenAI(client= , async_client= , root_client= , root_async_client= , model_name='gpt-4o', model_kwargs={}, openai_api_key=SecretStr('************)), output_parser=StrOutputParser(), llm_kw
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import ChatOpenAI
# SQLDatabaseToolkit generation
toolkit = SQLDatabaseToolkit(db=db, llm=ChatOpenAI(model=MODEL_NAME))
# List of tools available in SQLDatabaseToolkit
tools = toolkit.get_tools()
tools
from typing import Any
from langchain_core.messages import ToolMessage
from langchain_core.runnables import RunnableLambda, RunnableWithFallbacks
from langgraph.prebuilt import ToolNode
# error handling function
def handle_tool_error(state) -> dict:
# view error information
error = state.get("error")
# view tool information
tool_calls = state["woolMessage 로 래핑 후 반환
return {
"messages": [
ToolMessage(
content=f"Here is the error: {repr(error)}\n\nPlease fix your mistakes.",
tool_call_id=tc["id"],
)
for tc in tool_calls
]
}
# Create a ToolNode to handle errors and pass them on to the agent.
def create_tool_node_with_fallback(tools: list) -> RunnableWithFallbacks[Any, dict]:
"""
Create a ToolNode with a fallback to handle errors and surface them to the agent.
"""
# Add a ToolNode with a fallback action defined when an error occurs.
return ToolNode(tools).with_fallbacks(
[RunnableLambda(handle_tool_error)], exception_key="error"
)
from langchain_core.tools import tool
# Query exwecution tool
@tool
def db_query_tool(query: str) -> str:
"""
Run SQL queries against a database and return results
Returns an error message if the query is incorrect
If an error is returned, rewrite the query, check, and retry
"""
# exewcute query
result = db.run_no_throw(query)
# ERROR: If no result is found, return error message
if not result:
return "Error: Query failed. Please rewrite your query and try again."
# Normal: Returns query execution results
return result
# Select top 10 rows from Artist table and output execution results
print(db_query_tool.invoke("SELECT * FROM Artist LIMIT 10;"))
[(1,'AC/DC'), (2,'Accept'), (3,'Aerosmith'), (4,'Alanis Morissette'), (5,'Alice In Chains'), (6,'Antônio Carlos Jobim'), (7,'Apocalyptica'), (8,
# Select top 10 rows from Artist table and output execution results
print(db_query_tool.invoke("SELECT * FROM Artist LIMITS 10;"))
Error: (sqlite3.OperationalError) near "10": syntax error
[SQL: SELECT * FROM Artist LIMITS 10;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
from langchain_core.prompts import ChatPromptTemplate
from langchain_openai import ChatOpenAI
# Define system messages to check for common mistakes in SQL queries.
query_check_system = """You are a SQL expert with a strong attention to detail.
Double check the SQLite query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins
If there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.
You will call the appropriate tool to execute the query after running this check."""
# generate prompt
query_check_prompt = ChatPromptTemplate.from_messages(
[("system", query_check_system), ("placeholder", "{messages}")]
)
# Creating a Query Checker Chain
query_check = query_check_prompt | ChatOpenAI(
model=MODEL_NAME, temperature=0
).bind_tools([db_query_tool], tool_choice="db_query_tool")
# Run a query check node using user messages
response = query_check.invoke(
{"messages": [("user", "SELECT * FROM Artist LIMITS 10;")]}
)
print(response.tool_calls[0])
{'name':'db_query_tool','args': {'query':'SELECT * FROM Artist LIMIT 10;'},'id':'call_yX4yc3O8jF9GJNTP1ocsrWkU','type':
from typing import Annotated, Literal
from langchain_core.messages import AIMessage
from langchain_openai import ChatOpenAI
from pydantic import BaseModel, Field
from typing_extensions import TypedDict
from langgraph.graph import END, StateGraph, START
from langgraph.graph.message import AnyMessage, add_messages
from langgraph.checkpoint.memory import MemorySaver
# Defining the state of an agent
class State(TypedDict):
messages: Annotated[list[AnyMessage], add_messages]
# New graph definition
workflow = StateGraph(State)
# Add a node for the first tool call
def first_tool_call(state: State) -> dict[str, list[AIMessage]]:
return {
"messages": [
AIMessage(
content="",
tool_calls=[
{
"name": "sql_db_list_tables",
"args": {},
"id": "initial_tool_call_abc123",
}
],
)
]
}
# Define a function to check the correctness of a query against a model.
def model_check_query(state: State) -> dict[str, list[AIMessage]]:
"""
Use this tool to check that your query is correct before you run it
"""
return {"messages": [query_check.invoke({"messages": [state["messages"][-1]]})]}
# Add the first tool call node
workflow.add_node("first_tool_call", first_tool_call)
# Add nodes for the first two tools
workflow.add_node(
"list_tables_tool", create_tool_node_with_fallback([list_tables_tool])
)
workflow.add_node("get_schema_tool", create_tool_node_with_fallback([get_schema_tool]))
# Add a model node that selects relevant tables based on the question and available tables.
model_get_schema = ChatOpenAI(model=MODEL_NAME, temperature=0).bind_tools(
[get_schema_tool]
)
workflow.add_node(
"model_get_schema",
lambda state: {
"messages": [model_get_schema.invoke(state["messages"])],
},
)
# Tooltip indicating the final state
class SubmitFinalAnswer(BaseModel):
"""Submit final answer to user based on query results"""
final_answer: str = Field(..., description="The final answer to the user")
# Add a model node to generate queries based on questions and schema.
QUERY_GEN_INSTRUCTION = """You are a SQL expert with a strong attention to detail.
You can define SQL queries, analyze queries results and interpretate query results to response an answer.
Read the messages bellow and identify the user question, table schemas, query statement and query result, or error if they exist.
1. If there's not any query result that make sense to answer the question, create a syntactically correct SQLite query to answer the user question. DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.
2. If you create a query, response ONLY the query statement. For example, "SELECT id, name FROM pets;"
3. If a query was already executed, but there was an error. Response with the same error message you found. For example: "Error: Pets table doesn't exist"
4. If a query was already executed successfully interpretate the response and answer the question following this pattern: Answer: <<question answer>>. For example: "Answer: There three cats registered as adopted"
"""
query_gen_prompt = ChatPromptTemplate.from_messages(
[("system", QUERY_GEN_INSTRUCTION), ("placeholder", "{messages}")]
)
query_gen = query_gen_prompt | ChatOpenAI(model=MODEL_NAME, temperature=0).bind_tools(
[SubmitFinalAnswer, model_check_query]
)
# Conditional edge definition
def should_continue(state: State) -> Literal[END, "correct_query", "query_gen"]:
messages = state["messages"]
last_message = messages[-1]
if last_message.content.startswith("Answer:"):
return END
if last_message.content.startswith("Error:"):
return "query_gen"
else:
return "correct_query"
# defining a query generation node
def query_gen_node(state: State):
message = query_gen.invoke(state)
# LLM returns an error message if it calls the wrong tool.
tool_messages = []
message.pretty_print()
if message.tool_calls:
for tc in message.tool_calls:
if tc["name"] != "SubmitFinalAnswer":
tool_messages.append(
ToolMessage(
content=f"Error: The wrong tool was called: {tc['name']}. Please fix your mistakes. Remember to only call SubmitFinalAnswer to submit the final answer. Generated queries should be outputted WITHOUT a tool call.",
tool_call_id=tc["id"],
)
)
else:
tool_messages = []
return {"messages": [message] + tool_messages}
# Add a query generation node
workflow.add_node("query_gen", query_gen_node)
# Add a node to check the model before executing the query
workflow.add_node("correct_query", model_check_query)
# Add a node to run the query
workflow.add_node("execute_query", create_tool_node_with_fallback([db_query_tool]))
# Specifying edges between nodes
workflow.add_edge(START, "first_tool_call")
workflow.add_edge("first_tool_call", "list_tables_tool")
workflow.add_edge("list_tables_tool", "model_get_schema")
workflow.add_edge("model_get_schema", "get_schema_tool")
workflow.add_edge("get_schema_tool", "query_gen")
workflow.add_conditional_edges(
"query_gen",
should_continue,
)
workflow.add_edge("correct_query", "execute_query")
workflow.add_edge("execute_query", "query_gen")
# Compile into an executable workflow
app = workflow.compile(checkpointer=MemorySaver())
from langchain_teddynote.graphs import visualize_graph
visualize_graph(app, xray=True)
output = run_graph(
"View all personal information for Andrew Adam's staff",
stream=False,
)
from langchain_core.runnables import RunnableConfig
from langchain_teddynote.messages import random_uuid, invoke_graph, stream_graph
from langchain_core.messages import HumanMessage
from langgraph.errors import GraphRecursionError
def run_graph(
message: str, recursive_limit: int = 30, node_names=[], stream: bool = False
):
# config settings (maximum number of recursion, thread_id)
config = RunnableConfig(
recursion_limit=recursive_limit, configurable={"thread_id": random_uuid()}
)
# Enter your question
inputs = {
"messages": [HumanMessage(content=message)],
}
try:
if stream:
# running the graph
stream_graph(app, inputs, config, node_names=node_names)
else:
invoke_graph(app, inputs, config, node_names=node_names)
output = app.get_state(config).values
return output
except GraphRecursionError as recursion_error:
print(f"GraphRecursionError: {recursion_error}")
output = app.get_state(config).values
return output
==================================================
🔄 Node: first_tool_call 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
Tool Calls:
sql_db_list_tables (initial_tool_call_abc123)
Call ID: initial_tool_call_abc123
Args:
==================================================
==================================================
🔄 Node: list_tables_tool 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================= Tool Message =================================
Name: sql_db_list_tables
Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
==================================================
==================================================
🔄 Node: model_get_schema 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
Tool Calls:
sql_db_schema (call_o1gJuyskM6IxyFpp2yjmr8M4)
Call ID: call_o1gJuyskM6IxyFpp2yjmr8M4
Args:
table_names: Customer, Invoice
==================================================
==================================================
🔄 Node: get_schema_tool 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================= Tool Message =================================
Name: sql_db_schema
CREATE TABLE "Customer" (
"CustomerId" INTEGER NOT NULL,
"FirstName" NVARCHAR (40) NOT NULL,
"LastName" NVARCHAR (20) NOT NULL,
"Company" NVARCHAR (80),
"Address" NVARCHAR (70),
"City" NVARCHAR (40),
"State" NVARCHAR (40),
"Country" NVARCHAR (40),
"PostalCode" NVARCHAR (10),
"Phone" NVARCHAR (24),
"Fax" NVARCHAR (24),
"Email" NVARCHAR (60) NOT NULL,
"SupportRepId" INTEGER,
PRIMARY KEY ("CustomerId"),
FOREIGN KEY ("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)
/*
3 rows from Customer table:
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 luisg@embraer.com.br 3
2 Leonie Köhler None Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 +49 0711 2842222 None leonekohler@surfeu.de 5
3 François Tremblay None 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 None ftremblay@gmail.com 3
*/
CREATE TABLE "Invoice" (
"InvoiceId" INTEGER NOT NULL,
"CustomerId" INTEGER NOT NULL,
"InvoiceDate" DATETIME NOT NULL,
"BillingAddress" NVARCHAR (70),
"BillingCity" NVARCHAR (40),
"BillingState" NVARCHAR (40),
"BillingCountry" NVARCHAR (40),
"BillingPostalCode" NVARCHAR (10),
"Total" NUMERIC (10, 2) NOT NULL,
PRIMARY KEY ("InvoiceId"),
FOREIGN KEY ("CustomerId") REFERENCES "Customer" ("CustomerId")
)
/*
3 rows from Invoice table:
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total
1 2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 1.98
2 4 2009-01-02 00:00:00 Ullevålsveien 14 Oslo None Norway 0171 3.96
3 8 2009-01-03 00:00:00 Grétrystraat 63 Brussels None Belgium 1000 5.94
*/
==================================================
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_nKKhCLjOm713LvqkMSqtn2xI)
Call ID: call_nKKhCLjOm713LvqkMSqtn2xI
Args:
state: {'messages': [{'content': 'Which country's customers spent the most in 2009? And how much did you spend? 'Request':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track','type':'function', 'name'
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
==================================================
🔄 Node: query_gen 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_nKKhCLjOm713LvqkMSqtn2xI)
Call ID: call_nKKhCLjOm713LvqkMSqtn2xI
Args:
state: {'messages': [{'content': 'Which country's customers spent the most in 2009? And how much did you spend? NUL','type':'TAG1>, }'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track','type':'function','name'City" NVARCHAR(40), \n\t" State" NVARCHAR(40), \n\t" Country" NVARCHAR(40), \n\t"PostalCode" NVARCHAR(10), \n\t"Phone" NVARCHAR(24), \n\t" Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY ("CustomerId"), \n\tFOREIGN KEY("SupportRep
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
================================= Tool Message =================================
Error: The wrong tool was cled: model_check_query. Please fix your mistakes. Remember to only call SubmitFinalAnswer to submit the final answer. Generated queries should be outputted WITHOUT a tool call.
==================================================
================================== Ai Message ==================================
SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;
==================================================
🔄 Node: query_gen 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;
==================================================
==================================================
🔄 Node: correct_query 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
Tool Calls:
db_query_tool (call_uugf85ZxwGL9mkEBJetSr3Cr)
Call ID: call_uugf85ZxwGL9mkEBJetSr3Cr
Args:
query: SELECT c.Country, SUM(i.Total) AS TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
WHERE strftime ('%Y', i.InvoiceDate) = '2009'
GROUP BY c.Country
ORDER BY TotalSpent DESC
LIMIT 1;
==================================================
==================================================
🔄 Node: execute_query 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================= Tool Message =================================
Name: db_query_tool
[('USA', 103.95)]
==================================================
================================== Ai Message ==================================
Answer: In 2009, US (USA) customers spent the most, and the total expenditure was 103.95.
==================================================
🔄 Node: query_gen 🔄
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
================================== Ai Message ==================================
Answer: In 2009, US (USA) customers spent the most, and the total expenditure was 103.95.
==================================================
from langsmith import Client
# client initialization
client = Client()
# create and uplaod a dataset
examples = [
(
"Which country's customers spent the most? And how much did they spend?",
"The country whose customers spent the most is the USA, with a total spending of 523.06.",
),
(
"What was the most purchased track of 2013?",
"The most purchased track of 2013 was Hot Girl.",
),
(
"How many albums does the artist Led Zeppelin have?",
"Led Zeppelin has 14 albums",
),
(
"What is the total price for the album “Big Ones”?",
"The total price for the album 'Big Ones' is 14.85",
),
(
"Which sales agent made the most in sales in 2009?",
"Steve Johnson made the most sales in 2009",
),
]
dataset_name = "SQL Agent Response"
if not client.has_dataset(dataset_name=dataset_name):
dataset = client.create_dataset(dataset_name=dataset_name)
inputs, outputs = zip(
*[({"input": text}, {"output": label}) for text, label in examples]
)
client.create_examples(inputs=inputs, outputs=outputs, dataset_id=dataset.id)
# Define a function to predict the agent's SQL query response.
def predict_sql_agent_answer(example: dict):
"""Use this for answer evaluation"""
config = RunnableConfig(configurable={"thread_id": random_uuid()})
inputs = {
"messages": [HumanMessage(content=example["input"])],
}
# Run the graph to view message results
messages = app.invoke(inputs, config)
answer = messages["messages"][-1].content
# return results
return {"response": answer}
from langsmith.evaluation import evaluate
# name of the dataswet for evaluation
dataset_name = "SQL Agent Response"
try:
# evaluation in progress
experiment_results = evaluate(
predict_sql_agent_answer, # prediction function to uswe in evaluation
data=dataset_name, # Name of the dataset for evaluation
evaluators=[answer_evaluator], # liswt of reviewers
num_repetitions=3, # Set the number of repetitions of the experiment
experiment_prefix="sql-agent-eval",
metadata={"version": "chinook db, sql-agent-eval: gpt-4o"}, # Experimental metadata
)
except Exception as e:
print(e)
View the evaluation results for expert:'sql-agent-eval-2f44937f' at:
https://smith.langchain.com/o/42ebd69b-2565-441c-b868-9709c2e20267/datasets/3e801af3-038d-445d-887b-379a30a14939/compare?selectedSessions=6cc0987a-e16f-475c-b574-36c83aa19f2c
0it [00:00, ?it/s]
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_5XnBKYwbPqbktaHqDdZ8gQG4)
Call ID: call_5XnBKYwbPqbktaHqDdZ8gQG4
Args:
state: {'messages': [{'content': "SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSales FROM Employee e JOIN Customer c ON e.EmployeeId = c.SupportRep
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_oUXAuRH6WDE6NtGzS9F8NYWm)
Call ID: call_oUXAuRH6WDE6NtGzS9F8NYWm
Args:
state:'TagG1>'messages':'{'content':'Which sales agent made the most in sales in 2009?','type':'human'{, }'content':'Album, Artist, Customer, Employee, Genre
================================== Ai Message ==================================
SELECT e.FirstName, e.LastName, SUM(i.Total) AS TotalSalesFROM Employee eJOIN Customer c ON e.EmployeeId = c.SupportRepIdJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY e.EmployeeIdORDER BY TotalSales DESCLIMIT 1;
================================== Ai Message ==================================
SELECT e.FirstName, e.LastName, SUM (i.Total) as TotalSales
FROM Employee e
JOIN Customer c ON e.EmployeeId = c.SupportRepId
JOIN Invoice i ON c.CustomerId = i.CustomerId
WHERE strftime ('%Y', i.InvoiceDate) = '2009'
GROUP BY e.EmployeeId
ORDER BY TotalSales DESC
LIMIT 1;
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_NVlHV7r4RoqyaualVfIagTSW)
Call ID: call_NVlHV7r4RoqyaualVfIagTSW
Args:
state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table: \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_urK1QsY7wgMQhrKasG3qXjFh)
Call ID: call_urK1QsY7wgMQhrKasG3qXjFh
Args:
state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table: \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_dh9fi3gIxYV9BEKWsGPdoLpj)
Call ID: call_dh9fi3gIxYV9BEKWsGPdoLpj
Args:
state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table: \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';
================================== Ai Message ==================================
Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with total sales of 164.34.
================================== Ai Message ==================================
SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';
================================== Ai Message ==================================
SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';
================================== Ai Message ==================================
Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with a total of $164.34.
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_LEgQpLMGNaDhsOvVKH37k79m)
Call ID: call_LEgQpLMGNaDhsOvVKH37k79m
Args:
state:'TAG1>'messages': [{'content':'What is the total price for the album { Big Ones“?','type':'human'”, }'content':'Album, Artist, Customer,
(...sway...)
("InvoiceLineId"), \n\tFOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), \n\tFOREIGN KEY ("Invoice\n")
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_r3302vpy5ndcVHTOx9Zev6zs)
Call ID: call_r3302vpy5ndcVHTOx9Zev6zs
Args:
state:'TAG1>'messages':'What is the total price for the album { Big Ones{?','type':'human'“,'TAG1>'
(...sway...)
\n\t"UnitPrice" NUMERIC (10, 2) NOT NULL, \n\t"Quantity" INTEGER NOT NULL, \n\tPRIMARY KEY ("InvoiceLineId"), \n\tFOREIGNKEY ("TrackId") REFERENCt2\t6\t0.99\t1\n*/','type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
Answer: Led Zeppelin has 14 albums.
================================== Ai Message ==================================
Answer: Led Zeppelin has 14 albums.
================================== Ai Message ==================================
Answer: Led Zeppelin has 14 albums.
================================== Ai Message ==================================
SELECT SUM(InvoiceLine.UnitPrice * InvoiceLine.Quantity) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdWHERE Album.Title = 'Big Ones';
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_D7AbojfxcmXZ5dkxrJgT8hwa)
Call ID: call_D7AbojfxcmXZ5dkxrJgT8hwa
Args:
state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track', 'type':namefunction:
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_lFJl9jvKDaaZVuxsNLP0wUXk)
Call ID: call_lFJl9jvKDaaZVuxsNLP0wUXk
Args:
state:'TAG1>'messages': [{'content':'What is the total price for the album { Big Ones“?','type':'human'”, }'content':'Album, Artist, Customer,
(...sway...)
\n\tFOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")\n)\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tUnitPrice\tant
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_XqY2Ih4NcvF1sSKHzhOdFF7b)
Call ID: call_XqY2Ih4NcvF1sSKHzhOdFF7b
Args:
state: {'messages': [{'content':'What was the most purchased track of 2013?','type':'human'},'CREATE TABLE "Invoice" (\n\t"n\t"BillingPostalCode" NVARCHAR(10), \n\t" Total" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY ("InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES
(...sway...)
table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n]','type':'ai'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_wRjsL2MKclHQfJInY4rz0YI)
Call ID: call_wRjsL2MKclHQfJInY4rz0YI
Args:
state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, PlaylistTrack, Track','type':n NVARCHAR (70), \n\t" City" NVARCHAR (40), \n\t" State" NVARCHAR (40), \n\t"Country" NVARCHAR (40), \n\t"PostalCode" NVARCHAR (10), \n\t"Phone" NVAR \n\t" Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY ("CustomerId"), \n\tFOREIGN KEY(
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
================================== Ai Message ==================================
SELECT SUM(Invoice.Total) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdJOIN Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceIdWHERE Album.Title = 'Big Ones';
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_nAcQGCnL0IIstzNY1qGOZUw0)
Call ID: call_nAcQGCnL0IIstzNY1qGOZUw0
Args:
state:'{'messages':'What was the most purchased track of 2013?','type':'human'{,'Album, Artist, Customer, Employee, Genre, NVARCHAR (40), \n\t"BillingState" NVARCHAR (40), \n\t"BillingCountry" NVARCHAR (40), \n\t"BillingPostalCode" NVARCHAR (10), \n\t"Total" NUMERIC (10, 2) NOT InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES "Customer" ("CustomerId")\n)\n\n/*\n3
(...sway...)
table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/','type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_hQNm0vVAFZCyl16igsRctogd)
Call ID: call_hQNm0vVAFZCyl16igsRctogd
Args:
state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, PlaylistTrack, Track','type':n NVARCHAR (70), \n\t" City" NVARCHAR (40), \n\t" State" NVARCHAR (40), \n\t"Country" NVARCHAR (40), \n\t"PostalCode" NVARCHAR (10), \n\t"Phone" NVAR \n\t" Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_80FZIrpx0dPg8lIyBwgJ1cxc)
Call ID: call_80FZIrpx0dPg8lIyBwgJ1cxc
Args:
'Nvoice','Nvoice','Nvoice','EBillingAddress" NVARCHAR(70), \n\t"BillingCity" NVARCHAR(40), \n\t"BillingState" NVARCHAR(40), \n\t"BillingCountry" NVARCHAR(40), \n\t"BillingPostalCode" NVARCHAR (10), \n\t" Total" NUMERIC (10, 2) NOT NULL, \n\tPRIMARY KEY ("InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES "Customer" ("Custom
(...sway...)
table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/','type':'function','name':'sql_db_schema'}]}
================================== Ai Message ==================================
SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;
================================== Ai Message ==================================
SELECT SUM(Invoice.Total) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdJOIN Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceIdWHERE Album.Title = 'Big Ones';
================================== Ai Message ==================================
SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;
================================== Ai Message ==================================
Answer: The total price for the album “Big Ones” is 9.9.
================================== Ai Message ==================================
SELECT t.Name, SUM(il.Quantity) AS TotalPurchasesFROM Track tJOIN InvoiceLine il ON t.TrackId = il.TrackIdJOIN Invoice i ON il.InvoiceId = i.InvoiceIdWHERE strftime('%Y', i.InvoiceDate) = '2013'GROUP BY t.TrackIdORDER BY TotalPurchases DESCLIMIT 1;
================================== Ai Message ==================================
SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;
================================== Ai Message ==================================
Tool Calls:
model_check_query (call_tyWKRw7J30PswHjcAmiKPW4Q)
Call ID: call_tyWKRw7J30PswHjcAmiKPW4Q
Args:
'Ton':'TonG1>'messages':'Which sales agent made the most in sales in 2009?','type':'human'{,'Album, Artist, Customer, Employee NVARCHAR(20) NOT NULL, \n\t"FirstName" NVARCHAR(20) NOT NULL, \n\t"Title" NVARCHAR(30), \n\t"ReportsTo" INTEGER, \n\t" BirthDate" DATETIME, \n\t HireDate" DATETIME, \n\t"Address" NVARCHAR(70), \n\t"City" NVARCHAR(40), \n\t"State" NVARCHAR(40), \n\t"Country" NVARCHAR(40), \n\t"PostalCode) REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee \n\tFOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee \n\tFOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee
(...sway...)
table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]}
================================== Ai Message ==================================
SELECT t.Name, SUM (il.Quantity) AS TotalQuantity
FROM Track t
JOIN InvoiceLine il ON t.TrackId = il.TrackId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
WHERE strftime ('%Y', i.InvoiceDate) = '2013'
GROUP BY t.TrackId
ORDER BY TotalQuantity DESC
LIMIT 1;
================================== Ai Message ==================================
SELECT t.Name, SUM(il.Quantity) AS TotalQuantityFROM InvoiceLine ilJOIN Invoice i ON il.InvoiceId = i.InvoiceIdJOIN Track t ON il.TrackId = t.TrackIdWHERE strftime('%Y', i.InvoiceDate) = '2013'GROUP BY t.NameORDER BY TotalQuantity DESCLIMIT 1;
================================== Ai Message ==================================
Answer: The total price for the album “Big Ones” is 82.17.
================================== Ai Message ==================================
Answer: The country white customers spent the most is the USA, with a total speaking of 523.06.
================================== Ai Message ==================================
SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSalesFROM Employee eJOIN Customer c ON e.EmployeeId = c.SupportRepIdJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY e.EmployeeIdORDER BY TotalSales DESCLIMIT 1;
================================== Ai Message ==================================
Answer: The country white customers spent the most is the USA, with a total speaking of 523.06.
================================== Ai Message ==================================
Answer: The total price for the album “Big Ones” is 82.17.
================================== Ai Message ==================================
Answer: The country's customers who sent the most are from the USA, with a total speaking of 523.06.
================================== Ai Message ==================================
Answer: The most purchased track of 2013 was "Hot Girl" with 1 purchase.
================================== Ai Message ==================================
Answer: The most purchased track of 2013 was "Where Eagles Dare".
================================== Ai Message ==================================
Answer: The most purchased track of 2013 was "Hot Girl".
================================== Ai Message ==================================
Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with total sales of 164.34.