Guide for using LangChain’s SQL Database Chain and Agent with Large Language Models to perform natural language queries on CockroachDB.
In this post, we’ll explore how to generate insights from sample e-commerce data by asking questions, which will be translated into SQL queries and executed against the database. We assume the generated queries are correctly structured, and their results will be transformed into a textual explanation. For example, we might ask, “List the names of customers who have placed at least 5 orders.” This will be converted into an SQL query like:
SELECT c.customer\name FROM customers c INNER JOIN orders o ON c.customer\id \= o.customer\id GROUP BY c.customer\name HAVING COUNT(o.order\_id) \>= 5;
The result will then be presented as a textual explanation, such as: “Wren Helgass, Thorny Nornable, Melissa Partkya, Kristofor Roos, and Dannie Fidler have placed more than 5 orders.”
Source Code
All the source code for this post is available on GitHub.
What we will be using
We will leverage LangChain’s SQL Database Agent and SQL Database Chain, along with OpenAI’s gpt-3.5-turbo-instruct, to execute Natural Language Queries (NLQ) on a CockroachDB for PostgreSQL database. Additionally, we'll utilise techniques such as Prompt Templates, Query Checkers to enhance the accuracy of our results.
Using Amazon SageMaker notebook instances is optional. Alternatively, you can interact with the Jupyter notebooks through your local IDE.
Prerequisites
- Import the sample E-Commerce data, into an CockroachDB database.
- Set up a new Amazon SageMaker notebook instance for this demonstration. Ensure that your database is accessible from the SageMaker notebook environment.
- Clone the GitHub repository for this post to your Amazon SageMaker notebook instance.
- In the terminal of your SageMaker Notebook environment, create a
.envfile to be used by dotenv. A sampleenv.txtfile is included in the project. (Note: When using dotenv, credentials are stored in plain text. For better security, it's recommended to use AWS Secrets Manager.) - Add your OpenAI API credentials and CockroachDB credentials to your .env file:
OpenAI_API_KEY,CCK_DB_NAME,CCK_ENDPOINT,CCK_PASSWORD,CCK_PORT,CCK_NAMEandCCK_URI. - When connecting to CockroachDB for the first time, you’ll need to install the CA certificate. In CockroachDB Cloud, navigate to the “Connect” option. Under the “Download CA Cert” section, select your operating system, copy the provided command, and run it in the SageMaker Notebook terminal.
Installing the required packages
The initial step is to install and import the necessary packages for this demonstration. Please make sure that the LangChain is limited to read-only access to the CockroachDB database.
We are using SQLAlchemy tool while connecting LangChain to CockroachDB. The connection url looks like: cockroachdb://:@:/?sslmode=verify-full . To find your connection string in CockroachDB Cloud, navigate to “Connect” option, choose your SQL Server , Database and then choose your language as Python . In the select tool choose SQLAlchemy and choose your OS .
import os
\# Avoiding tokenizers parallelism error os.environ\["TOKENIZERS\_PARALLELISM"\] = "false"
\# Install the latest versions of required packages %pip install langchain openai langchain\_experimental langchain-openai -Uq %pip install ipywidgets python-dotenv SQLAlchemy psycopg2-binary -Uq %pip install sqlalchemy-cockroachdb
\# List and check the versions of installed packages %pip list | grep "langchain\\|openai"
from langchain import SQLDatabase from langchain\openai import OpenAI from langchain\experimental.sql import SQLDatabaseChain from langchain\_experimental.sql.base import SQLDatabaseSequentialChain from sqlalchemy.exc import ProgrammingError
%load\_ext dotenv %dotenv
CCK\DB\NAME = os.environ.get("CCK\DB\NAME") CCK\ENDPOINT = os.environ.get("CCK\ENDPOINT") CCK\PASSWORD = os.environ.get("CCK\PASSWORD") CCK\PORT = os.environ.get("CCK\PORT") CCK\USERNAME = os.environ.get("CCK\USERNAME") CCK\URI = f"cockroachdb://{CCK\USERNAME}:{CCK\PASSWORD}@{CCK\ENDPOINT}:{CCK\PORT}/{CCK\DB\NAME}?sslmode=verify-full" OpenAI\API\KEY = os.environ.get("OPENAI\API\_KEY")
List some sample questions
We have included some sample questions which we can ask on the data that was previously imported to our database.
\# Loading some sample questions Q1 = "Determine details about the user who ordered most items." Q2 = "Determine order id, date and name of the customer of the most recently placed order" Q3 = "List the name of the customers who have placed atleast 5 orders" Q4 = "What is price of the most profitable sale and the name of the customer who placed that order?" Q5 = "Fetch the most recent order placed by the customer whose id is 282"
Specifying the LLM
We will begin by utilizing OpenAI’s gpt-3.5-turbo-instruct LLM with LangChain for this demonstration.
llm = OpenAI(openai\api\key=OpenAI\API\KEY, model\_name="gpt-3.5-turbo-instruct", temperature=0, verbose=True)
Let’s ask our first question
Let’s ask our first question based on our E-Commerce data
db = SQLDatabase.from\uri(CCK\URI) db\chain = SQLDatabaseSequentialChain.from\llm( llm, db, verbose=True, use\query\checker=True try: db\_chain.run(Q1) except (ProgrammingError, ValueError) as exc: print(f"\\n\\n{exc}")
Successful results should resemble the example below. First, you’ll see the question (“Determine details about the user who ordered the most items.”), followed by the corresponding SQL query:
SELECT customers.customer\name, customers.gender, customers.age, customers.home\address, customers.zip\code, customers.city, customers.state, customers.country FROM customers INNER JOIN orders ON customers.customer\id \= orders.customer\id INNER JOIN products ON orders.order\id \= products.product\_id WHERE products.quantity IS NOT NULL ORDER BY products.quantity DESC LIMIT 1;
Next is the result set (assuming the SQL query was correctly formed), and finally, the SQL result:
SQLResult: \[('Giles Kyne', 'Polygender', 45, '91 Angelina Ridge Apt. 435', '9871', 'Lake Phoenixside', 'South Australia', 'Australia')\]
The answer will then be presented as: “Giles Kyne, a 45-year-old polygender individual from Lake Phoenixside, South Australia, Australia, ordered the most items.”
Table names to use: \['customers', 'orders', 'products'\]Determine details about the user who ordered most items.
SQLQuery:SELECT customers.customer\name, customers.gender, customers.age, customers.home\address, customers.zip\code, customers.city, customers.state, customers.country FROM customers INNER JOIN orders ON customers.customer\id \= orders.customer\id INNER JOIN products ON orders.order\id \= products.product\_id WHERE products.quantity IS NOT NULL ORDER BY products.quantity DESC LIMIT 1;
SQLResult: \[('Giles Kyne', 'Polygender', 45, '91 Angelina RidgeApt. 435', '9871', 'Lake Phoenixside', 'South Australia', 'Australia')\]
Answer:Giles Kyne, a 45 year old polygender person from Lake Phoenixside, South Australia, Australia, ordered the most items.
\> Finished chain.
If the SQL query generated by the LLM was malformed, you would likely see output resembling the following:
Table names to use: \['Customers', 'orders', 'products'\]
\> Entering new SQLDatabaseChain chain... Determine details about the user who ordered the most items. SQLQuery:
table\_names {'Customers'} not found in database
Providing Custom Table Information And Using Query Checker
In some situations, it may be beneficial to provide custom table information rather than relying on the automatically generated table definitions and sample rows. Below is the custom table information for our e-commerce database tables. Also, the language model may sometimes generate invalid SQL with minor errors that can be corrected using the same technique employed by the SQL Database Agent. To enable the query checker and allow the LLM to attempt fixing these errors, simply add the use_query_checker=True parameter to the SQLDatabaseSequentialChain object.
table\info = { "customers": """CREATE TABLE customers ( customer\id INT8 NOT NULL DEFAULT unique\rowid(), customer\name VARCHAR(255) NULL, gender VARCHAR(100) NULL, age INT8 NULL, home\address VARCHAR(255) NULL, zip\code VARCHAR(20) NULL, city VARCHAR(100) NULL, state VARCHAR(100) NULL, country VARCHAR(100) NULL, CONSTRAINT customers\pkey PRIMARY KEY (customer\id ASC))
/\ 3 rows from customers table: "customer\_id" "customer\_name" "gender" "age" "home\_address" "zip\_code" "city" "state" "country" 1 "Leanna Busson" "Female" 30 "8606 Victoria TerraceSuite 560" "5464" "Johnstonhaven" "Northern Territory" "Australia" 2 "Zabrina Harrowsmith" "Genderfluid" 69 "8327 Kirlin SummitApt. 461" "8223" "New Zacharyfort" "South Australia" "Australia" 3 "Shina Dullaghan" "Polygender" 59 "269 Gemma SummitSuite 109" "5661" "Aliburgh" "Australian Capital Territory" "Australia" \/""", "products": """CREATE TABLE products ( product\id INT8 NOT NULL DEFAULT unique\rowid(), product\type VARCHAR(100) NULL, product\name VARCHAR(100) NULL, size VARCHAR(50) NULL, colour VARCHAR(50) NULL, price DECIMAL(10,2) NULL, quantity INT8 NULL, description VARCHAR(255) NULL, CONSTRAINT products\pkey PRIMARY KEY (product\id ASC))
/\ 3 rows from products table: "product\_ID" "product\_type" "product\_name" "size" "colour" "price" "quantity" "description" 0 "Shirt" "Oxford Cloth" "XS" "red" 114.00 66 "A red coloured, XS sized, Oxford Cloth Shirt" 1 "Shirt" "Oxford Cloth" "S" "red" 114.00 53 "A red coloured, S sized, Oxford Cloth Shirt" 2 "Shirt" "Oxford Cloth" "M" "red" 114.00 54 "A red coloured, M sized, Oxford Cloth Shirt" \/""", "orders": """CREATE TABLE orders ( order\id INT8 NOT NULL DEFAULT unique\rowid(), customer\id INT8 NOT NULL, payment DECIMAL(10,2) NOT NULL, order\date DATE NULL DEFAULT current\date(), delivery\date DATE NULL, CONSTRAINT orders\pkey PRIMARY KEY (order\id ASC), CONSTRAINT fk\customer\id FOREIGN KEY (customer\id) REFERENCES public.customers(customer\id))
/\ 3 rows from orders table: "order\_id" "customer\_id" "payment" "order\_date" "delivery\_date" 1 64 30811.00 "2021-08-30" "2021-09-24" 2 473 50490.00 "2021-02-03" "2021-02-13" 3 774 46763.00 "2021-10-08" "2021-11-03" \/""", "sales": """CREATE TABLE sales ( sales\id INT8 NOT NULL DEFAULT unique\rowid(), order\id INT8 NULL, product\id INT8 NULL, price\per\unit DECIMAL(10,2) NULL, quantity INT8 NULL, total\price DECIMAL(10,2) NULL, CONSTRAINT sales\pkey PRIMARY KEY (sales\id ASC), CONSTRAINT fk\product\id FOREIGN KEY (product\id) REFERENCES public.products(product\_id))
/\ 3 rows from sales table: "sales\_id" "order\_id" "product\_id" "price\_per\_unit" "quantity" "total\_price" 0 1 218 106.00 2 212.00 1 1 481 118.00 1 118.00 2 1 2 96.00 3 288.00 \/""", }
Enabling the Query Checker
db = SQLDatabase.from\uri( CCK\URI, include\tables=\["customers", "orders", "products", "sales"\], sample\rows\in\table\info=3, custom\table\info=table\info,
db\chain = SQLDatabaseSequentialChain.from\llm( llm, db, verbose=True, use\query\checker=True, top\_k=3
try: db\_chain.run(Q2) except (ProgrammingError, ValueError) as exc: print(f"\\n\\n{exc}")
The results should resemble the example below. Once again, you’ll see the question, the corresponding SQL query, the result set (assuming the SQL query is correctly formed), and finally, the answer.
\> Entering new SQLDatabaseSequentialChain chain... Table names to use: \['orders', 'customers'\]Determine order id, date and name of the customer of the most recently placed order SQLQuery:SELECT orders.order\id, orders.order\date, customers.customer\name FROM orders INNER JOIN customers ON orders.customer\id \= customers.customer\id ORDER BY orders.order\date DESC LIMIT 1 SQLResult: \[(99764172, datetime.date(2024, 8, 10), 'Wren Helgass')\] Answer:The most recently placed order has an order id of 99764172, a date of August 10, 2024, and the customer's name is Wren Helgass. \> Finished chain.
Customizing Prompt
To enhance the results further, we can also utilize LangChain’s Prompt Template. Below is an example of a prompt template that we will use to increase the chances of obtaining more accurate results.
from langchain.prompts.prompt import PromptTemplate
EXAMPLE\_TEMPLATE = """Given an input question, please create a syntactically correct {dialect} SQL query to retrieve the required information. Then, execute the query, observe the results, and provide a concise answer.
Follow this format strictly:
Question: "Original question here" SQLQuery: "SQL query to execute" SQLResult: "Results of the SQLQuery" Answer: "Final answer based on the SQLResult"
Only reference the following tables:
{table\_info}
Important Notes: \- If joins are necessary to answer the question, include them in the query. \- Ensure that conditions or filters in the question are correctly applied in the SQL query.
Question: {input}"""
PROMPT = PromptTemplate( input\variables=\["input", "table\info", "dialect"\], template=EXAMPLE\_TEMPLATE
Intermediate Steps
We can also retrieve the intermediate steps of the SQLDatabaseChain, allowing us to view the generated SQL statement and the results obtained from executing it against the SQL database.
db = SQLDatabase.from\uri(CCK\URI)
db\chain = SQLDatabaseChain.from\llm( llm, db, prompt=PROMPT, verbose=True, use\query\checker=True, return\intermediate\steps=True,
try: result = db\_chain(Q2) except (ProgrammingError, ValueError) as exc: print(f"\\n\\n{exc}")
result\["intermediate\_steps"\]
The results should resemble the example below. Here, we can see the question, the corresponding SQL query, the result set (assuming the SQL query is correctly formed), the answer, and now, the intermediate steps as well.
\> Entering new SQLDatabaseChain chain... Determine order id, date and name of the customer of the most recently placed order SQLQuery:SELECT orders.order\id, orders.order\date, customers.customer\name FROM orders INNER JOIN customers ON orders.customer\id \= customers.customer\id ORDER BY orders.order\date DESC LIMIT 1; SQLResult: \[(99764172, datetime.date(2024, 8, 10), 'Wren Helgass')\] Answer:The most recently placed order has an order id of 99764172, was placed on August 10th, 2024, and the customer's name is Wren Helgass. \> Finished chain.
\[{'input': 'Determine order id, date and name of the customer of the most recently placed order\\nSQLQuery:', 'top\k': '5', 'dialect': 'cockroachdb', 'table\info': '\\nCREATE TABLE customers (\\n\\tcustomer\id SERIAL NOT NULL, \\n\\tcustomer\name VARCHAR(255), \\n\\tgender VARCHAR(100), \\n\\tage INTEGER, \\n\\thome\address VARCHAR(255), \\n\\tzip\code VARCHAR(20), \\n\\tcity VARCHAR(100), \\n\\tstate VARCHAR(100), \\n\\tcountry VARCHAR(100), \\n\\tCONSTRAINT customers\pkey PRIMARY KEY (customer\id)\\n)\\n\\n/\\\n3 rows from customers table:\\ncustomer\_id\\tcustomer\_name\\tgender\\tage\\thome\_address\\tzip\_code\\tcity\\tstate\\tcountry\\n1\\tLeanna Busson\\tFemale\\t30\\t8606 Victoria TerraceSuite 560\\t5464\\tJohnstonhaven\\tNorthern Territory\\tAustralia\\n2\\tZabrina Harrowsmith\\tGenderfluid\\t69\\t8327 Kirlin SummitApt. 461\\t8223\\tNew Zacharyfort\\tSouth Australia\\tAustralia\\n3\\tShina Dullaghan\\tPolygender\\t59\\t269 Gemma SummitSuite 109\\t5661\\tAliburgh\\tAustralian Capital Territory\\tAustralia\\n\/\\n\\n\\nCREATE TABLE orders (\\n\\torder\id SERIAL NOT NULL, \\n\\tcustomer\id INTEGER NOT NULL, \\n\\tpayment DECIMAL(10, 2) NOT NULL, \\n\\torder\date DATE DEFAULT current\date(), \\n\\tdelivery\date DATE, \\n\\tCONSTRAINT orders\pkey PRIMARY KEY (order\id), \\n\\tCONSTRAINT fk\customer\id FOREIGN KEY(customer\id) REFERENCES customers (customer\id)\\n)\\n\\n/\*\\n3 rows from orders table:\\norder\id\\tcustomer\id\\tpayment\\torder\date\\tdelivery\date\\n1\\t64\\t30811.00\\t2021-08-30\\t2021-09-24\\n2\\t473\\t50490.00\\t2021-02-03\\t2021-02-13\\n3\\t774\\t46763.00\\t2021-10-08\\t2021-11-03\\n\*/\\n\\n\\nCREATE TABLE products (\\n\\tproduct\id SERIAL NOT NULL, \\n\\tproduct\type VARCHAR(100), \\n\\tproduct\name VARCHAR(100), \\n\\tsize VARCHAR(50), \\n\\tcolour VARCHAR(50), \\n\\tprice DECIMAL(10, 2), \\n\\tquantity INTEGER, \\n\\tdescription VARCHAR(255), \\n\\tCONSTRAINT products\pkey PRIMARY KEY (product\id)\\n)\\n\\n/\\\n3 rows from products table:\\nproduct\_id\\tproduct\_type\\tproduct\_name\\tsize\\tcolour\\tprice\\tquantity\\tdescription\\n0\\tShirt\\tOxford Cloth\\tXS\\tred\\t114.00\\t66\\tA red coloured, XS sized, Oxford Cloth Shirt\\n1\\tShirt\\tOxford Cloth\\tS\\tred\\t114.00\\t53\\tA red coloured, S sized, Oxford Cloth Shirt\\n2\\tShirt\\tOxford Cloth\\tM\\tred\\t114.00\\t54\\tA red coloured, M sized, Oxford Cloth Shirt\\n\/\\n\\n\\nCREATE TABLE sales (\\n\\tsales\id SERIAL NOT NULL, \\n\\torder\id INTEGER, \\n\\tproduct\id INTEGER, \\n\\tprice\per\unit DECIMAL(10, 2), \\n\\tquantity INTEGER, \\n\\ttotal\price DECIMAL(10, 2), \\n\\tCONSTRAINT sales\pkey PRIMARY KEY (sales\id), \\n\\tCONSTRAINT fk\product\id FOREIGN KEY(product\id) REFERENCES products (product\id)\\n)\\n\\n/\\\n3 rows from sales table:\\nsales\_id\\torder\_id\\tproduct\_id\\tprice\_per\_unit\\tquantity\\ttotal\_price\\n0\\t1\\t218\\t106.00\\t2\\t212.00\\n1\\t1\\t481\\t118.00\\t1\\t118.00\\n2\\t1\\t2\\t96.00\\t3\\t288.00\\n\/', 'stop': \['\\nSQLResult:'\]}, 'SELECT o.order\id, o.order\date, c.customer\name\\nFROM orders o\\nINNER JOIN customers c ON o.customer\id \= c.customer\id\\nORDER BY o.order\date DESC\\nLIMIT 1;', {'sql\cmd': 'SELECT o.order\id, o.order\date, c.customer\name\\nFROM orders o\\nINNER JOIN customers c ON o.customer\id \= c.customer\id\\nORDER BY o.order\date DESC\\nLIMIT 1;'}, "\[(99764172, datetime.date(2024, 8, 10), 'Wren Helgass')\]", {'input': "Determine order id, date and name of the customer of the most recently placed order\\nSQLQuery:SELECT o.order\id, o.order\date, c.customer\name\\nFROM orders o\\nINNER JOIN customers c ON o.customer\id = c.customer\id\\nORDER BY o.order\date DESC\\nLIMIT 1;\\nSQLResult: \[(99764172, datetime.date(2024, 8, 10), 'Wren Helgass')\]\\nAnswer:", 'top\k': '5', 'dialect': 'cockroachdb', 'table\info': '\\nCREATE TABLE customers (\\n\\tcustomer\id SERIAL NOT NULL, \\n\\tcustomer\name VARCHAR(255), \\n\\tgender VARCHAR(100), \\n\\tage INTEGER, \\n\\thome\address VARCHAR(255), \\n\\tzip\code VARCHAR(20), \\n\\tcity VARCHAR(100), \\n\\tstate VARCHAR(100), \\n\\tcountry VARCHAR(100), \\n\\tCONSTRAINT customers\pkey PRIMARY KEY (customer\id)\\n)\\n\\n/\*\\n3 rows from customers table:\\ncustomer\id\\tcustomer\name\\tgender\\tage\\thome\address\\tzip\code\\tcity\\tstate\\tcountry\\n1\\tLeanna Busson\\tFemale\\t30\\t8606 Victoria TerraceSuite 560\\t5464\\tJohnstonhaven\\tNorthern Territory\\tAustralia\\n2\\tZabrina Harrowsmith\\tGenderfluid\\t69\\t8327 Kirlin SummitApt. 461\\t8223\\tNew Zacharyfort\\tSouth Australia\\tAustralia\\n3\\tShina Dullaghan\\tPolygender\\t59\\t269 Gemma SummitSuite 109\\t5661\\tAliburgh\\tAustralian Capital Territory\\tAustralia\\n\*/\\n\\n\\nCREATE TABLE orders (\\n\\torder\id SERIAL NOT NULL, \\n\\tcustomer\id INTEGER NOT NULL, \\n\\tpayment DECIMAL(10, 2) NOT NULL, \\n\\torder\date DATE DEFAULT current\date(), \\n\\tdelivery\date DATE, \\n\\tCONSTRAINT orders\pkey PRIMARY KEY (order\id), \\n\\tCONSTRAINT fk\customer\id FOREIGN KEY(customer\id) REFERENCES customers (customer\id)\\n)\\n\\n/\\\n3 rows from orders table:\\norder\_id\\tcustomer\_id\\tpayment\\torder\_date\\tdelivery\_date\\n1\\t64\\t30811.00\\t2021-08-30\\t2021-09-24\\n2\\t473\\t50490.00\\t2021-02-03\\t2021-02-13\\n3\\t774\\t46763.00\\t2021-10-08\\t2021-11-03\\n\/\\n\\n\\nCREATE TABLE products (\\n\\tproduct\id SERIAL NOT NULL, \\n\\tproduct\type VARCHAR(100), \\n\\tproduct\name VARCHAR(100), \\n\\tsize VARCHAR(50), \\n\\tcolour VARCHAR(50), \\n\\tprice DECIMAL(10, 2), \\n\\tquantity INTEGER, \\n\\tdescription VARCHAR(255), \\n\\tCONSTRAINT products\pkey PRIMARY KEY (product\id)\\n)\\n\\n/\*\\n3 rows from products table:\\nproduct\id\\tproduct\type\\tproduct\name\\tsize\\tcolour\\tprice\\tquantity\\tdescription\\n0\\tShirt\\tOxford Cloth\\tXS\\tred\\t114.00\\t66\\tA red coloured, XS sized, Oxford Cloth Shirt\\n1\\tShirt\\tOxford Cloth\\tS\\tred\\t114.00\\t53\\tA red coloured, S sized, Oxford Cloth Shirt\\n2\\tShirt\\tOxford Cloth\\tM\\tred\\t114.00\\t54\\tA red coloured, M sized, Oxford Cloth Shirt\\n\/\\n\\n\\nCREATE TABLE sales (\\n\\tsales\_id SERIAL NOT NULL, \\n\\torder\_id INTEGER, \\n\\tproduct\_id INTEGER, \\n\\tprice\_per\_unit DECIMAL(10, 2), \\n\\tquantity INTEGER, \\n\\ttotal\_price DECIMAL(10, 2), \\n\\tCONSTRAINT sales\_pkey PRIMARY KEY (sales\_id), \\n\\tCONSTRAINT fk\_product\_id FOREIGN KEY(product\_id) REFERENCES products (product\_id)\\n)\\n\\n/\\\n3 rows from sales table:\\nsales\id\\torder\id\\tproduct\id\\tprice\per\unit\\tquantity\\ttotal\price\\n0\\t1\\t218\\t106.00\\t2\\t212.00\\n1\\t1\\t481\\t118.00\\t1\\t118.00\\n2\\t1\\t2\\t96.00\\t3\\t288.00\\n\*/', 'stop': \['\\nSQLResult:'\]}, 'The most recently placed order has an order id of 99764172, a date of August 10, 2024, and was placed by a customer named Wren Helgass.'\]
LangChain SQL Database Agent
The SQL Database Agent extends the functionality of SQLDatabaseChain, enabling it to handle more general questions about a database and recover from errors.
Note: There’s no guarantee that the agent won’t execute DML statements if prompted by certain questions, so exercise caution when using it on sensitive data!
With LangChain’s SQL Database Agent, we can ask general questions about the E-Commerce CockroachDB database, such as “Describe the products table.”
from langchain.agents import create\sql\agent from langchain.agents.agent\toolkits import SQLDatabaseToolkit from langchain.sql\database import SQLDatabase
toolkit = SQLDatabaseToolkit(db=db, llm=llm) agent\executor = create\sql\_agent(llm=llm, toolkit=toolkit, verbose=True)
\# Example of describing a table try: agent\_executor.run("Describe the products table.") except (ProgrammingError, ValueError) as exc: print(f"\\n\\n{exc}")
The results should resemble the example below. Here, we observe the LLM’s thought process, actions, and observations as it analyses the question and gives a response.
\> Entering new SQL Agent Executor chain... Action: sql\db\list\tables Action Input: customers, orders, products, sales I should query the schema of the products table. Action: sql\db\schema Action Input: products CREATE TABLE products ( product\id SERIAL NOT NULL, product\type VARCHAR(100), product\name VARCHAR(100), size VARCHAR(50), colour VARCHAR(50), price DECIMAL(10, 2), quantity INTEGER, description VARCHAR(255), CONSTRAINT products\pkey PRIMARY KEY (product\id)
/\ 3 rows from products table: product\_id product\_type product\_name size colour price quantity description 0 Shirt Oxford Cloth XS red 114.00 66 A red coloured, XS sized, Oxford Cloth Shirt 1 Shirt Oxford Cloth S red 114.00 53 A red coloured, S sized, Oxford Cloth Shirt 2 Shirt Oxford Cloth M red 114.00 54 A red coloured, M sized, Oxford Cloth Shirt \/ I now know the final answer Final Answer: The products table contains information about different types of products, including their product ID, type, name, size, colour, price, quantity, and description.
\> Finished chain.
With the Agent, we can also query the data just as we did before.
"""Q4: What is price of the most profitable sale and the name of the customer who placed that order?""" try: agent\_executor.run(Q4) except (ProgrammingError, ValueError) as exc: print(f"\\n\\n{exc}")
The results should resemble the example below. Once again, we observe the LLM’s thought process, actions, and observations as it analyzes the question and formulates a response: “The most profitable sale was $357.00 and the customer who placed that order was Kristofor Roos.”
\> Entering new SQL Agent Executor chain... Action: sql\db\list\tables Action Input: customers, orders, products, sales I should query the schema of the orders and sales tables to see what columns I can use to find the most profitable sale and the customer who placed that order. Action: sql\db\schema Action Input: orders, sales CREATE TABLE orders ( order\id SERIAL NOT NULL, customer\id INTEGER NOT NULL, payment DECIMAL(10, 2) NOT NULL, order\date DATE DEFAULT current\date(), delivery\date DATE, CONSTRAINT orders\pkey PRIMARY KEY (order\id), CONSTRAINT fk\customer\id FOREIGN KEY(customer\id) REFERENCES customers (customer\id)
/\ 3 rows from orders table: order\_id customer\_id payment order\_date delivery\_date 1 64 30811.00 2021-08-30 2021-09-24 2 473 50490.00 2021-02-03 2021-02-13 3 774 46763.00 2021-10-08 2021-11-03 \/
CREATE TABLE sales ( sales\id SERIAL NOT NULL, order\id INTEGER, product\id INTEGER, price\per\unit DECIMAL(10, 2), quantity INTEGER, total\price DECIMAL(10, 2), CONSTRAINT sales\pkey PRIMARY KEY (sales\id), CONSTRAINT fk\product\id FOREIGN KEY(product\id) REFERENCES products (product\id)
/\ 3 rows from sales table: sales\_id order\_id product\_id price\_per\_unit quantity total\_price 0 1 218 106.00 2 212.00 1 1 481 118.00 1 118.00 2 1 2 96.00 3 288.00 \/ I should query the sales table to find the most profitable sale and the customer who placed that order. Action: sql\db\query Action Input: SELECT sales.total\price, customers.name FROM sales INNER JOIN orders ON sales.order\id \= orders.order\id INNER JOIN customers ON orders.customer\id \= customers.customer\id ORDER BY sales.total\price DESC LIMIT 1;Error: (psycopg2.errors.UndefinedColumn) column "customers.name" does not exist
\[SQL: SELECT sales.total\price, customers.name FROM sales INNER JOIN orders ON sales.order\id \= orders.order\id INNER JOIN customers ON orders.customer\id \= customers.customer\id ORDER BY sales.total\price DESC LIMIT 1;\] (Background on this error at: https://sqlalche.me/e/20/f405) I should use sql\db\schema to query the correct table fields. Action: sql\db\schema Action Input: customers, orders, sales CREATE TABLE customers ( customer\id SERIAL NOT NULL, customer\name VARCHAR(255), gender VARCHAR(100), age INTEGER, home\address VARCHAR(255), zip\code VARCHAR(20), city VARCHAR(100), state VARCHAR(100), country VARCHAR(100), CONSTRAINT customers\pkey PRIMARY KEY (customer\id)
/\ 3 rows from customers table: customer\_id customer\_name gender age home\_address zip\_code city state country 1 Leanna Busson Female 30 8606 Victoria TerraceSuite 560 5464 Johnstonhaven Northern Territory Australia 2 Zabrina Harrowsmith Genderfluid 69 8327 Kirlin SummitApt. 461 8223 New Zacharyfort South Australia Australia 3 Shina Dullaghan Polygender 59 269 Gemma SummitSuite 109 5661 Aliburgh Australian Capital Territory Australia \/
CREATE TABLE orders ( order\id SERIAL NOT NULL, customer\id INTEGER NOT NULL, payment DECIMAL(10, 2) NOT NULL, order\date DATE DEFAULT current\date(), delivery\date DATE, CONSTRAINT orders\pkey PRIMARY KEY (order\id), CONSTRAINT fk\customer\id FOREIGN KEY(customer\id) REFERENCES customers (customer\_id)
/\ 3 rows from orders table: order\_id customer\_id payment order\_date delivery\_date 1 64 30811.00 2021-08-30 2021-09-24 2 473 50490.00 2021-02-03 2021-02-13 3 774 46763.00 2021-10-08 2021-11-03 \/
CREATE TABLE sales ( sales\id SERIAL NOT NULL, order\id INTEGER, product\id INTEGER, price\per\unit DECIMAL(10, 2), quantity INTEGER, total\price DECIMAL(10, 2), CONSTRAINT sales\pkey PRIMARY KEY (sales\id), CONSTRAINT fk\product\id FOREIGN KEY(product\id) REFERENCES products (product\id)
/\ 3 rows from sales table: sales\_id order\_id product\_id price\_per\_unit quantity total\_price 0 1 218 106.00 2 212.00 1 1 481 118.00 1 118.00 2 1 2 96.00 3 288.00 \/ I should use the correct column name for the customer's name. Action: sql\db\query Action Input: SELECT sales.total\price, customers.customer\name FROM sales INNER JOIN orders ON sales.order\id = orders.order\id INNER JOIN customers ON orders.customer\id = customers.customer\id ORDER BY sales.total\_price DESC LIMIT 1;\[(Decimal('357.00'), 'Kristofor Roos')\] I now know the final answer.
Final Answer: The most profitable sale was $357.00 and the customer who placed that order was Kristofor Roos.
\> Finished chain.
Conclusion
In this post, we explored how to leverage LangChain’s SQL Database Chain and Agent in combination with OpenAI’s GPT-3.5-turbo-instruct LLM to execute Natural Language Queries (NLQ) on CockroachDB. Additionally, we highlighted the importance of LangChain’s Prompt Template and Query Checker in improving accuracy and optimizing query results.
References
- Dataset: https://www.kaggle.com/datasets/ruchi798/shopping-cart-database/data
- Connecting CockroachDB: https://www.cockroachlabs.com/docs/v24.2/build-a-python-app-with-cockroachdb-sqlalchemy
- Python Dotenv: https://pypi.org/project/python-dotenv/
- OpenAI Models Overview: https://platform.openai.com/docs/models/overview
- LangChain: https://python.langchain.com/docs/tutorials/llm\_chain
- Jupyter Notebook: https://docs.jupyter.org/en/latest
#onaquest #quester #nlp #ai #aiml #agi #ai #langchain #haystack #deepset #cockroachlabs #crdb #genai #Quest1