I have an agent with LangChain.
I have a database with these tables:
CREATE TABLE teams (
team_id INT PRIMARY KEY,
team_name VARCHAR(50) UNIQUE NOT NULL,
coach VARCHAR(50) NOT NULL
);
CREATE TABLE players (
player_id INT PRIMARY KEY,
player_name VARCHAR(50) NOT NULL,
age INT,
player_position VARCHAR(20),
team_id INT REFERENCES teams(team_id) ON DELETE CASCADE
);
And I coded this agent with SQLDatabaseToolkit in order to get data from the database:
prompt = PromptTemplate.from_template(
"""
You are a helpful assistant. Use the tools below to answer questions about the database.
If the question asks for players from a team, make sure to query the 'players' table,
joining it with the 'teams' table on team_id and filtering by team_name.
Example: "List all players from England" → Execute:
SELECT p.player_name
FROM players p
JOIN teams t ON p.team_id = t.team_id
WHERE t.team_name = 'England';
End your answer with a complete sentence like: "The players of England are ...".
{agent_scratchpad}
"""
)
# Set up the SQL database connection
db = SQLDatabase.from_uri("postgresql+psycopg2://postgres:[email protected]:5432/soccer")
# Use SQLDatabaseToolkit to interact with the database and LLM
toolkit = SQLDatabaseToolkit(db=db, llm=llm)
# Get the tools from the toolkit
tools = toolkit.get_tools()
# Create the agent executor using the initialized tools and prompt
agent_executor = initialize_agent(
tools=tools,
llm=llm,
agent_type="tool-calling",
verbose=True
)
If I ask the players of Spain, the logs shows:
> Entering new AgentExecutor chain...
The user is asking for a specific list of players, which is structured data that can be retrieved using the SQLQueryTool.
Action: SQLQueryTool
Action Input: {"query": "SELECT players FROM teams WHERE country = 'Spain'"}
> Entering new AgentExecutor chain...
I need to check if the table 'teams' exists and if the column 'players' and 'country' are present in that table.
Action: sql_db_list_tables
Action Input:
Observation: players, teams
Thought:The 'teams' table exists in the database. Next, I need to check the schema of the 'teams' table to confirm the presence of the 'players' and 'country' columns.
Action: sql_db_schema
Action Input: teams
Observation:
CREATE TABLE teams (
team_id INTEGER NOT NULL,
team_name VARCHAR(50) NOT NULL,
coach VARCHAR(50),
CONSTRAINT teams_pkey PRIMARY KEY (team_id),
CONSTRAINT teams_team_name_key UNIQUE (team_name)
)
/*
3 rows from teams table:
team_id team_name coach
1 Spain coach_1
16 Netherlands coach_2
12 Sweden coach_3
*/
Thought:The 'teams' table does not have a 'players' or 'country' column. It contains columns for 'team_id', 'team_name', and 'coach'. The 'team_name' column seems to be used for the country name based on the sample data provided.
Final Answer: The original query cannot be executed as there is no 'players' or 'country' column in the 'teams' table. The query should be adjusted to use the 'team_name' column for country names. If you need information about players, you would need to check if there is another table containing player information linked to the 'teams' table.
> Finished chain.
Observation: {'input': '{"query": "SELECT players FROM teams WHERE country = \'Spain\'"}', 'output': "The original query cannot be executed as there is no 'players' or 'country' column in the 'teams' table. The query should be adjusted to use the 'team_name' column for country names. If you need information about players, you would need to check if there is another table containing player information linked to the 'teams' table."}
Thought:The SQLQueryTool indicates that the original query was incorrect due to the absence of 'players' or 'country' columns in the 'teams' table. It suggests that the query should be adjusted to use the 'team_name' column for country names and to check if there is another table containing player information linked to the 'teams' table.
Final Answer: I'm sorry, I couldn't find any relevant information in the documents.
> Finished chain.
As we can see, the SQLDatabaseToolkit doesn't perform well the joins as expected. Shouldn't it be able to do that? How can i improve it?
