Well, not strictly a post on using agents. Rather, as mentioned last time, I am going to code a module to “double-check” the SQL queries generated by the agent in the previous post. So a short post, more or less completely off topic. Curiosity eh?
New Module
I will start a new module, query_tst.py, to use for this post. Nothing too fancy. I will just be copying over the SQL queries from the previous post, executing them and comparing the results to the chatbot’s responses for each prompt in that prior post.
Simple Start
Okay, let’s, once again, list the table names, but with a SQL query rather than using the LangChain tools.
# query_tst.py:
# version: 0.1.0: 2025.07.07, rek, double check SQL queries used by chatbot to
# reply to prompts about the chinook.db used for a chatbot post
import pandas as pd
import sqlite3
prn_tbl_nms = True
con = sqlite3.connect("chinook.db")
cur = con.cursor()
# get name of all tables in database
res = cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
db_tbls = [tp[0] for tp in res.fetchall()]
if prn_tbl_nms:
print(db_tbls)
And…
(agnt-3.12) PS R:\learn\ds_agent> python query_tst.py
['albums', 'sqlite_sequence', 'artists', 'customers', 'employees', 'genres', 'invoices', 'invoice_items', 'media_types', 'playlists', 'playlist_track', 'tracks', 'sqlite_stat1']
A couple of new tables that didn’t show in the previous post: sqlite_sequence and sqlite_statl. Haven’t looked at them but I expect they may have been present all along. Perhaps the database tool knew not to list them.
Process the Chatbot Queries
Now, let’s get to running the SQL queries generated by the chatbot in that last post. I instantiated lists for the prompt, the query generated by the chatbot and the bots response for each of the questions shown in the previous post. I looped through the lists, locally running the query against the Sqlite database. Then display all the pertinent information in the terminal.
I did, at first, limit the number of iterations to make sure things were working as expected. And there were a few refactorings of the output. No need to include all of that in the post. The code sure requires a lot less lines of text than do the three lists.
cb_prompts = [
"Which country's customers spent the most?",
"Which genre had the most spent on it.",
"Which customer purchased the most albums?",
"Who were the top 10 customers based on total album purchases?",
"What is the average number of album purchases by customer?",
"Which customers purchased less than 38 albums?",
"What is the average length of time employees have been working for the company given today is June 5, 2020?",
]
cb_queries = [
"""SELECT c.Country, SUM(i.Total) AS TotalSpent
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY TotalSpent DESC
LIMIT 5;""",
"""SELECT g.Name, SUM(ii.UnitPrice * ii.Quantity) AS TotalSpent
FROM genres g
JOIN tracks t ON g.GenreId = t.GenreId
JOIN invoice_items ii ON t.TrackId = ii.TrackId
GROUP BY g.Name
ORDER BY TotalSpent DESC
LIMIT 5;""",
"""SELECT c.FirstName, c.LastName, COUNT(ii.TrackId) AS AlbumsPurchased
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY AlbumsPurchased DESC
LIMIT 5;""",
"""SELECT c.FirstName, c.LastName, COUNT(ii.TrackId) AS AlbumsPurchased
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY AlbumsPurchased DESC
LIMIT 10;""",
"""SELECT AVG(AlbumsPurchased) AS AveragePurchases
FROM (SELECT COUNT(ii.TrackId) AS AlbumsPurchased
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
GROUP BY c.CustomerId) AS Subquery;""",
"""SELECT c.FirstName, c.LastName, COUNT(ii.TrackId) AS AlbumsPurchased
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
GROUP BY c.CustomerId, c.FirstName, c.LastName
HAVING AlbumsPurchased < 38
ORDER BY AlbumsPurchased;""",
"SELECT AVG(JULIANDAY('2020-06-05') - JULIANDAY(HireDate)) AS AverageDaysWorked FROM employees;",
]
cb_reply = [
"""The countries where customers spent the most are:
1. **USA**: $523.06
2. **Canada**: $303.96
3. **France**: $195.10
4. **Brazil**: $190.10
5. **Germany**: $156.48""",
"""The genre with the most spent on it is:
1. **Rock**: $826.65
2. **Latin**: $382.14
3. **Metal**: $261.36
4. **Alternative & Punk**: $241.56
5. **TV Shows**: $93.53""",
"""The customers who purchased the most albums are:
1. **Luís Gonçalves**: 38 albums
2. **Leonie Köhler**: 38 albums
3. **François Tremblay**: 38 albums
4. **Bjørn Hansen**: 38 albums
5. **František Wichterlová**: 38 albums
These customers are tied with the same number of purchases.""",
"""The top 10 customers based on total album purchases are:
1. **Luís Gonçalves**: 38 albums
2. **Leonie Köhler**: 38 albums
3. **François Tremblay**: 38 albums
4. **Bjørn Hansen**: 38 albums
5. **František Wichterlová**: 38 albums
6. **Helena Holý**: 38 albums
7. **Astrid Gruber**: 38 albums
8. **Daan Peeters**: 38 albums
9. **Kara Nielsen**: 38 albums
10. **Eduardo Martins**: 38 albums
All listed customers have made the same number of purchases.""",
"""The average number of album purchases by customer is approximately **37.97** albums.""",
"""The customer who purchased less than 38 albums is:
- **Puja Srivastava**: 36 albums.""",
"The average length of time employees have been working for the company is approximately **6260.75 days**, which is about **17.15 years**.",
]
for i, cbp in enumerate(cb_prompts):
print(f"\n{i+1}: chatbot prompt:\n{cbp}:\n{"-"*8}")
res = pd.read_sql_query(cb_queries[i], con)
if i != len(cb_prompts) - 1:
print(f"\nlocal query returns:\n{res}")
else:
davg = res.iat[0, 0]
yavg = davg / 365
print(f"\nlocal query returns:\n{davg} hours or {yavg:0.2f} years")
print(f"\nchatbot earlier replied:\n{cb_reply[i]}\n")
The terminal output follows.
(agnt-3.12) PS R:\learn\ds_agent> python query_tst.py
1: chatbot prompt:
Which country's customers spent the most?:
--------
local query returns:
Country TotalSpent
0 USA 523.06
1 Canada 303.96
2 France 195.10
3 Brazil 190.10
4 Germany 156.48
chatbot earlier replied:
The countries where customers spent the most are:
1. **USA**: $523.06
2. **Canada**: $303.96
3. **France**: $195.10
4. **Brazil**: $190.10
5. **Germany**: $156.48
2: chatbot prompt:
Which genre had the most spent on it.:
--------
local query returns:
Name TotalSpent
0 Rock 826.65
1 Latin 382.14
2 Metal 261.36
3 Alternative & Punk 241.56
4 TV Shows 93.53
chatbot earlier replied:
The genre with the most spent on it is:
1. **Rock**: $826.65
2. **Latin**: $382.14
3. **Metal**: $261.36
4. **Alternative & Punk**: $241.56
5. **TV Shows**: $93.53
3: chatbot prompt:
Which customer purchased the most albums?:
--------
local query returns:
FirstName LastName AlbumsPurchased
0 Luís Gonçalves 38
1 Leonie Köhler 38
2 François Tremblay 38
3 Bjørn Hansen 38
4 František Wichterlová 38
chatbot earlier replied:
The customers who purchased the most albums are:
1. **Luís Gonçalves**: 38 albums
2. **Leonie Köhler**: 38 albums
3. **François Tremblay**: 38 albums
4. **Bjørn Hansen**: 38 albums
5. **František Wichterlová**: 38 albums
These customers are tied with the same number of purchases.
4: chatbot prompt:
Who were the top 10 customers based on total album purchases?:
--------
local query returns:
FirstName LastName AlbumsPurchased
0 Luís Gonçalves 38
1 Leonie Köhler 38
2 François Tremblay 38
3 Bjørn Hansen 38
4 František Wichterlová 38
5 Helena Holý 38
6 Astrid Gruber 38
7 Daan Peeters 38
8 Kara Nielsen 38
9 Eduardo Martins 38
chatbot earlier replied:
The top 10 customers based on total album purchases are:
1. **Luís Gonçalves**: 38 albums
2. **Leonie Köhler**: 38 albums
3. **François Tremblay**: 38 albums
4. **Bjørn Hansen**: 38 albums
5. **František Wichterlová**: 38 albums
6. **Helena Holý**: 38 albums
7. **Astrid Gruber**: 38 albums
8. **Daan Peeters**: 38 albums
9. **Kara Nielsen**: 38 albums
10. **Eduardo Martins**: 38 albums
All listed customers have made the same number of purchases.
5: chatbot prompt:
What is the average number of album purchases by customer?:
--------
local query returns:
AveragePurchases
0 37.966102
chatbot earlier replied:
The average number of album purchases by customer is approximately **37.97** albums.
6: chatbot prompt:
Which customers purchased less than 38 albums?:
--------
local query returns:
FirstName LastName AlbumsPurchased
0 Puja Srivastava 36
chatbot earlier replied:
The customer who purchased less than 38 albums is:
- **Puja Srivastava**: 36 albums.
7: chatbot prompt:
What is the average length of time employees have been working for the company given today is June 5, 2020?:
--------
local query returns:
6260.75 hours or 17.15 years
chatbot earlier replied:
The average length of time employees have been working for the company is approximately **6260.75 days**, which is about **17.15 years**.
And, the queries appear to work just as the chatbot said they would.
Wow, a rather short post!
A Bit More
One thing I didn’t do in the prior post was ask the chatbot a question that was not directly related to the data in the chinook.db. So, figured I’d do that now.
I did modify the terminal output below slightly so that my code block, in this post, displayed correctly.
(agnt-3.12) PS R:\learn\ds_agent> python chat_bot_4.py
User: What is chinook.db?
================================ Human Message =================================
What is chinook.db?
================================== Ai Message ==================================
Chinook.db is a sample database that is often used for learning SQL and database concepts. It represents a digital media store, similar to iTunes, and contains various tables that provide information about entities such as customers, invoices, tracks, albums, artists, and genres. The database allows users to execute SQL queries to practice and learn how to interact with relational databases, as it has a realistic structure and relationships among different data entities.
If you're interested, I can provide you with the schema of the tables present in the chinook.db. Would you like me to do that?
User: which country's customers spent the least?
================================ Human Message =================================
which country's customers spent the least?
================================== Ai Message ==================================
Tool Calls:
sql_db_list_tables (call_VNMc8zmlKIRVXKzEwaN2Dm0N)
Call ID: call_VNMc8zmlKIRVXKzEwaN2Dm0N
Args:
================================= Tool Message =================================
Name: sql_db_list_tables
albums, artists, customers, employees, genres, invoice_items, invoices, media_types, playlist_track, playlists, tracks
================================== Ai Message ==================================
Tool Calls:
sql_db_schema (call_83z5UZc0euGJqewkBQKKqn2i)
Call ID: call_83z5UZc0euGJqewkBQKKqn2i
Args:
table_names: customers,invoices
sql_db_schema (call_Xouregm8aUOcYiREFJsVAfL8)
Call ID: call_Xouregm8aUOcYiREFJsVAfL8
Args:
table_names: invoice_items
================================= Tool Message =================================
Name: sql_db_schema
CREATE TABLE invoice_items (
"InvoiceLineId" INTEGER NOT NULL,
"InvoiceId" INTEGER NOT NULL,
"TrackId" INTEGER NOT NULL,
"UnitPrice" NUMERIC(10, 2) NOT NULL,
"Quantity" INTEGER NOT NULL,
PRIMARY KEY ("InvoiceLineId"),
FOREIGN KEY("TrackId") REFERENCES tracks ("TrackId"),
FOREIGN KEY("InvoiceId") REFERENCES invoices ("InvoiceId")
)
/*
3 rows from invoice_items table:
InvoiceLineId InvoiceId TrackId UnitPrice Quantity
1 1 2 0.99 1
2 1 4 0.99 1
3 2 6 0.99 1
*/
================================== Ai Message ==================================
Tool Calls:
sql_db_query_checker (call_cz3e5243DTIDrZM4PutD5xKN)
Call ID: call_cz3e5243DTIDrZM4PutD5xKN
Args:
query: SELECT c.Country, SUM(i.Total) AS TotalSpent
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY TotalSpent ASC
LIMIT 5;
================================= Tool Message =================================
Name: sql_db_query_checker
sql:
SELECT c.Country, SUM(i.Total) AS TotalSpent
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY TotalSpent ASC
LIMIT 5;
================================== Ai Message ==================================
Tool Calls:
sql_db_query (call_K8g1hZI6M7P1slHjI5kkCboc)
Call ID: call_K8g1hZI6M7P1slHjI5kkCboc
Args:
query: SELECT c.Country, SUM(i.Total) AS TotalSpent
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
GROUP BY c.Country
ORDER BY TotalSpent ASC
LIMIT 5;
================================= Tool Message =================================
Name: sql_db_query
[('Argentina', 37.62), ('Australia', 37.62), ('Belgium', 37.62), ('Denmark', 37.62), ('Italy', 37.62)]
================================== Ai Message ==================================
The countries with the least spending from customers are:
1. Argentina - $37.62
2. Australia - $37.62
3. Belgium - $37.62
4. Denmark - $37.62
5. Italy - $37.62
All of these countries have the same total spending amount.
User: What is the tallest waterfall in the world?
================================ Human Message =================================
What is the tallest waterfall in the world?
================================== Ai Message ==================================
The tallest waterfall in the world is Angel Falls, located in Venezuela. Angel Falls has an overall height of 979 meters (3,212 feet) and an uninterrupted drop of 807 meters (2,648 feet). It is named after Jimmy Angel, an American explorer who was the first to fly over the falls in 1933. Angel Falls is a popular tourist destination and is known for its stunning natural beauty.
User: Which waterfall in the world has the highest water flow per minute?
================================ Human Message =================================
Which waterfall in the world has the highest water flow per minute?
================================== Ai Message ==================================
The waterfall with the highest water flow per minute is Inga Falls, located on the Congo River in the Democratic Republic of the Congo. Inga Falls has an average flow rate of about 25,768 cubic meters per second (910,000 cubic feet per second). This immense flow rate is due to the large volume of water from the Congo River, one of the largest rivers in the world by discharge. Inga Falls is not only impressive for its water flow but is also a significant hydroelectric power site.
User: x
AI: See you later!
There you go, it apparently knows when to and when not to query the database. Though I am sure there would need to be a significant refactoring if there were multiple databases involved.
And, sorry, had the bot code in “examine” mode; so lot’s of extra terminal output. Didn’t feel like changing that and running through the prompts/queries again.
Done
Okay, that’s it for this one. Not horribly informative. No real interesting code. But, it satisfied my curiosity; so all good.
Until next time do remember that, though perhaps not for cats, curiosity is generally a good thing.