抽象的
Langchain是一个强大的框架,其中包括各种工具,包括agent_toolkits
。在本文中,我们将使用SQLDatabaseToolkit
通过用英语请求来与singlestoredB进行交互。
介绍
一个recent blog post描述了内部单杆木座。其中一个很酷的演示展示了如何将Langchain的SQLDatabaseToolkit
与SinglestoredB一起使用。在这篇简短的文章中,我们将复制演示。
创建一个SinglestoredB云帐户
previous article展示了创建一个免费的SinglestoredB云帐户的步骤。我们将使用 Langchain Demo Group 作为我们的工作空间组名称, langchain-demo 作为我们的工作空间名称。我们将记下我们的密码和主机名称。
创建TPC-H数据库
可用工作空间后,我们将选择教程(由网页右下方的黑色背景上的白色问号标识),然后选择 tutorials>尝试使用示例数据尝试>基准数据集-TPC -H 。我们将检查是否选择了Langchain-Demo工作空间,然后单击按钮至“ 负载tpc-h数据集,如图1。
所示短时间后,数据库将准备就绪。数据库名称将以 s2_dataset_tpch _ 开始,并在工作区概述选项卡下可见。我们将记下整个数据库名称。
如previous article中所述,我们将按照说明创建笔记本。
。填写笔记本
首先,我们将安装Langchain:
!pip install langchain --quiet
并添加一些导入:
from langchain import OpenAI, SQLDatabase
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.agents import create_sql_agent
接下来,我们将设置我们的OpenAI API Key
:
import os
import getpass
os.environ["OPENAI_API_KEY"] = getpass.getpass("OpenAI API Key:")
现在我们创建代理:
s2_password = "<password>"
s2_host = "<host>"
s2_db = "<database>"
db = SQLDatabase.from_uri(f"mysql+pymysql://admin:{s2_password}@{s2_host}:3306/{s2_db}")
llm = OpenAI(
temperature = 0,
verbose = False
)
toolkit = SQLDatabaseToolkit(
db = db,
llm = llm
)
agent_executor = create_sql_agent(
llm = OpenAI(temperature = 0),
toolkit = toolkit,
verbose = True
)
我们将用我们之前保存的值替换<password>
,<host>
和<database>
。
最后,我们将运行代理,如下:
agent_executor.run("Write a SingleStore query that shows the highest paying customers per country, include how much they've spent, use the nation name")
输出应类似于以下内容:
> Entering new AgentExecutor chain...
Thought: I should use the sql_db_list_tables tool to get a list of tables in the database.
Action: sql_db_list_tables
Action Input: ""
Observation: customer, lineitem, nation, orders, part, partsupp, region, supplier
Thought: I should query the schema of the customer, orders, and nation tables.
Action: sql_db_schema
Action Input: "customer, orders, nation"
Observation:
CREATE TABLE customer (
c_custkey INTEGER(11) NOT NULL,
c_name VARCHAR(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
c_address VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
c_nationkey INTEGER(11) NOT NULL,
c_phone VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
c_acctbal DECIMAL(15, 2) NOT NULL,
c_mktsegment VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
c_comment VARCHAR(117) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
)
/*
3 rows from customer table:
c_custkey c_name c_address c_nationkey c_phone c_acctbal c_mktsegment c_comment
3 Customer#000000003 MG9kdTD2WBHm 1 11-719-748-3364 7498.12 AUTOMOBILE deposits eat slyly ironic, even instructions. express foxes detect slyly. blithely even accounts ab
26 Customer#000000026 8ljrc5ZeMl7UciP 22 32-363-455-4837 5182.05 AUTOMOBILE c requests use furiously ironic requests. slyly ironic dependencies us
33 Customer#000000033 qFSlMuLucBmx9xnn5ib2csWUweg D 17 27-375-391-1280 -78.56 AUTOMOBILE s. slyly regular accounts are furiously. carefully pending requests
*/
CREATE TABLE nation (
n_nationkey INTEGER(11) NOT NULL,
n_name VARCHAR(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
n_regionkey INTEGER(11) NOT NULL,
n_comment VARCHAR(152) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
)
/*
3 rows from nation table:
n_nationkey n_name n_regionkey n_comment
3 CANADA 1 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bol
22 RUSSIA 3 requests against the platelets use never according to the quickly regular pint
1 ARGENTINA 1 al foxes promise slyly according to the regular accounts. bold requests alon
*/
CREATE TABLE orders (
o_orderkey BIGINT(11) NOT NULL,
o_custkey INTEGER(11) NOT NULL,
o_orderstatus CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
o_totalprice DECIMAL(15, 2) NOT NULL,
o_orderdate DATE NOT NULL,
o_orderpriority VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
o_clerk VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
o_shippriority INTEGER(11) NOT NULL,
o_comment VARCHAR(79) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
)
/*
3 rows from orders table:
o_orderkey o_custkey o_orderstatus o_totalprice o_orderdate o_orderpriority o_clerk o_shippriority o_comment
17349095 193084 O 73125.77 1996-07-04 4-NOT SPECIFIED Clerk#000002841 0 hely special somas cajole-
17349349 468115 F 230397.76 1992-04-16 1-URGENT Clerk#000002856 0 s. deposits cajole carefully regular ac
17349376 758954 F 199122.15 1993-10-27 1-URGENT Clerk#000000408 0 wake quickly. carefully pending deposits wak
*/
Thought: I should use the sql_db_query tool to query the database.
Action: sql_db_query
Action Input: SELECT c.c_name, c.c_acctbal, n.n_name FROM customer c JOIN nation n ON c.c_nationkey = n.n_nationkey ORDER BY c.c_acctbal DESC LIMIT 10
Observation: [('Customer#000061453', Decimal('9999.99'), 'MOROCCO'), ('Customer#000508503', Decimal('9999.99'), 'UNITED STATES'), ('Customer#001123705', Decimal('9999.98'), 'MOZAMBIQUE'), ('Customer#000399453', Decimal('9999.97'), 'UNITED KINGDOM'), ('Customer#000242308', Decimal('9999.96'), 'RUSSIA'), ('Customer#000348586', Decimal('9999.96'), 'MOROCCO'), ('Customer#000069321', Decimal('9999.96'), 'MOROCCO'), ('Customer#000991983', Decimal('9999.95'), 'IRAN'), ('Customer#001165335', Decimal('9999.94'), 'EGYPT'), ('Customer#000652672', Decimal('9999.93'), 'CANADA')]
Thought: I now know the final answer
Final Answer: The highest paying customers per country are: Customer#000061453 from Morocco with $9999.99, Customer#000508503 from United States with $9999.99, Customer#001123705 from Mozambique with $9999.98, Customer#000399453 from United Kingdom with $9999.97, Customer#000242308 from Russia with $9999.96, Customer#000348586 from Morocco with $9999.96, Customer#000069321 from Morocco with $9999.96, Customer#000991983 from Iran with $9999.95, Customer#001165335 from Egypt with $9999.94, and Customer#000652672 from Canada with $9999.93.
> Finished chain.
'The highest paying customers per country are: Customer#000061453 from Morocco with $9999.99, Customer#000508503 from United States with $9999.99, Customer#001123705 from Mozambique with $9999.98, Customer#000399453 from United Kingdom with $9999.97, Customer#000242308 from Russia with $9999.96, Customer#000348586 from Morocco with $9999.96, Customer#000069321 from Morocco with $9999.96, Customer#000991983 from Iran with $9999.95, Customer#001165335 from Egypt with $9999.94, and Customer#000652672 from Canada with $9999.93.'
概括
使用SQLDatabaseToolkit
,我们可以使用英语与单骨数据库进行交互。这为使用SQL提供了替代方案。
致谢
我感谢我的同事Justin Kuntz的演示代码,该代码适用于本文。