做网站卖机器怎么弄,上海互联网企业,开源门户系统,网站列表功能验证 Mixtral-8x7B-Instruct-v0.1 和 LangChain SQLDatabaseToolkit 的集成效果 0. 背景1. 验证环境说明2. 验证开始2-1. 准备测试数据库2-2. 读取环境配置信息2-3. 导入依赖包2-3. 创建 SQLDatabaseToolkit 对象和 AgentExecutor 对象2-4. 第1个测试 - 描述一个表2-5. 第2个测… 验证 Mixtral-8x7B-Instruct-v0.1 和 LangChain SQLDatabaseToolkit 的集成效果 0. 背景1. 验证环境说明2. 验证开始2-1. 准备测试数据库2-2. 读取环境配置信息2-3. 导入依赖包2-3. 创建 SQLDatabaseToolkit 对象和 AgentExecutor 对象2-4. 第1个测试 - 描述一个表2-5. 第2个测试 - 描述一个表从错误中恢复2-6. 第3个测试 - 运行查询12-7. 第4个测试 - 运行查询22-8. 第5个测试 - 从错误中恢复 3. 结论 0. 背景
自然语言 to SQL 一直是我比较关注的领域LangChain 也一直在做这方面的探索之前有发布过 SQLDatabaseChain估计是问题比较多的原因吧后来有重新发布了 SQLDatabaseToolkit 替代 SQLDatabaseChain。
使用 SQLDatabaseToolkit 需要 ChatGPT 3.5 或者 ChatGPT 4 的能力才能获得很好的效果。
最近 Mixtral-8x7B-Instruct-v0.1 发布之后发现 Mixtral-8x7B-Instruct-v0.1 的性能太强大了。
那今天就来验证一下 Mixtral-8x7B-Instruct-v0.1 和 LangChain SQLDatabaseToolkit 的集成效果怎么样。
1. 验证环境说明
Mixtral-8x7B-Instruct-v0.1 这个模型比较大相当于 56B 的模型我个人是没有那么大的 GPU 资源来启动 Mixtral-8x7B-Instruct-v0.1所以这次验证使用了 CPU 启动的 Mixtral-8x7B-Instruct-v0.1 量化版 Mixtral-8x7B-Instruct-v0.1-GGUF(Q8_0)。估计原生 Mixtral-8x7B-Instruct-v0.1 的性能应该比量化版要好
2. 验证开始
2-1. 准备测试数据库
我们使用 SQLite 数据库下载测试数据 Chinook_Sqlite.sql然后创建 SQLite 数据库
sqlite3 ./Chinook.db .read Chinook_Sqlite.sqlSELECT * FROM Artist LIMIT 10;输出结果如下 2-2. 读取环境配置信息
import os
import sysimport openai
from dotenv import load_dotenv, find_dotenvsys.path.append(../..)# read local .env file
_ load_dotenv(find_dotenv())openai.api_key os.environ[OPENAI_API_KEY]
openai.api_base os.environ[OPENAI_API_BASE]2-3. 导入依赖包
from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.agents.agent_types import AgentType
from langchain.chat_models import ChatOpenAI
from langchain.sql_database import SQLDatabase2-3. 创建 SQLDatabaseToolkit 对象和 AgentExecutor 对象
db SQLDatabase.from_uri(sqlite:///Chinook.db)
toolkit SQLDatabaseToolkit(dbdb, llmChatOpenAI(temperature0, model_namegpt-4))agent_executor create_sql_agent(llmChatOpenAI(temperature0, model_namegpt-4),toolkittoolkit,verboseTrue,agent_typeAgentType.ZERO_SHOT_REACT_DESCRIPTION,
)2-4. 第1个测试 - 描述一个表
agent_executor.run(Describe the playlisttrack table)执行过程 Entering new AgentExecutor chain...
Action: sql_db_list_tables
Action Input:
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought:The PlaylistTrack table seems to be the most relevant one for the question. I will now query its schema to get more information about it.Action: sql_db_schema
Action Input: PlaylistTrack
Observation:
CREATE TABLE PlaylistTrack (PlaylistId INTEGER NOT NULL, TrackId INTEGER NOT NULL, PRIMARY KEY (PlaylistId, TrackId), FOREIGN KEY(TrackId) REFERENCES Track (TrackId), FOREIGN KEY(PlaylistId) REFERENCES Playlist (PlaylistId)
)/*
3 rows from PlaylistTrack table:
PlaylistId TrackId
1 3402
1 3389
1 3390
*/
Thought:The PlaylistTrack table has two columns: PlaylistId and TrackId. Both of these columns are integers and cannot be null. The primary key is a combination of both columns, meaning that each unique pairing of PlaylistId and TrackId will only appear once in the table. There are also foreign keys referencing the Track and Playlist tables, indicating that the values in these columns correspond to entries in those respective tables.Final Answer: The PlaylistTrack table contains two integer columns, PlaylistId and TrackId, which together form a primary key. There are also foreign keys referencing the Track and Playlist tables. Finished chain.最后输出结果
The PlaylistTrack table contains two integer columns, PlaylistId and TrackId, which together form a primary key. There are also foreign keys referencing the Track and Playlist tables.2-5. 第2个测试 - 描述一个表从错误中恢复
在此示例中Agent 尝试搜索不存在的表但找到了下一个最佳结果
agent_executor.run(Describe the playlistsong table)执行过程 Entering new AgentExecutor chain...
Action: sql_db_list_tables
Action Input:
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought:The PlaylistSong table seems to be related to the question. I will now query its schema.
Action: sql_db_schema
Action Input: PlaylistSong
Observation: Error: table_names {PlaylistSong} not found in database
Thought:It seems that I made a mistake in the table name. The correct name is PlaylistTrack. I will query its schema now.Action: sql_db_schema
Action Input: PlaylistTrack
Observation:
CREATE TABLE PlaylistTrack (PlaylistId INTEGER NOT NULL, TrackId INTEGER NOT NULL, PRIMARY KEY (PlaylistId, TrackId), FOREIGN KEY(TrackId) REFERENCES Track (TrackId), FOREIGN KEY(PlaylistId) REFERENCES Playlist (PlaylistId)
)/*
3 rows from PlaylistTrack table:
PlaylistId TrackId
1 3402
1 3389
1 3390
*/
Thought:I now know the schema of the PlaylistTrack table. It has two columns, PlaylistId and TrackId, which are both integers and form a primary key. There are also foreign keys referencing the Playlist and Track tables. Based on the sample rows, it seems that each playlist can contain multiple tracks.Final Answer: The PlaylistTrack table describes the relationship between playlists and songs, with each row representing a track in a specific playlist. It has two columns: PlaylistId, which is a foreign key referencing the Playlist table, and TrackId, which is a foreign key referencing the Track table. Finished chain.最后输出结果
The PlaylistTrack table describes the relationship between playlists and songs, with each row representing a track in a specific playlist. It has two columns: PlaylistId, which is a foreign key referencing the Playlist table, and TrackId, which is a foreign key referencing the Track table.2-6. 第3个测试 - 运行查询1
agent_executor.run(List the total sales per country. Which countrys customers spent the most?
)执行过程 Entering new AgentExecutor chain...
Action: sql_db_list_tables
Action Input:
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought:The Invoice table seems relevant as it might contain sales information. I will query its schema to see the available columns.
Action: sql_db_schema
Action Input: Invoice
Observation:
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
*/
Thought:The Invoice table has a column named BillingCountry, which seems to contain the country information for each sale. I will now write a SQL query to get the total sales per country and order them in descending order of total sales to find out which countrys customers spent the most.Query: SELECT BillingCountry, SUM(Total) as TotalSales FROM Invoice GROUP BY BillingCountry ORDER BY TotalSales DESC;
Action: sql_db_query_checker
Action Input: SELECT BillingCountry, SUM(Total) as TotalSales FROM Invoice GROUP BY BillingCountry ORDER BY TotalSales DESC;
Observation: SELECT BillingCountry, SUM(Total) as TotalSales FROM Invoice GROUP BY BillingCountry ORDER BY TotalSales DESC;The given SQLite query does not contain any of the common mistakes mentioned. Therefore, I am reproducing the original query below:SELECT BillingCountry, SUM(Total) as TotalSales FROM Invoice GROUP BY BillingCountry ORDER BY TotalSales DESC;
Thought:I now have a syntactically correct SQL query to find out which countrys customers spent the most. I will execute this query and return the result.Action: sql_db_query
Action Input: SELECT BillingCountry, SUM(Total) as TotalSales FROM Invoice GROUP BY BillingCountry ORDER BY TotalSales DESC;
Observation: [(USA, 523.0600000000003), (Canada, 303.9599999999999), (France, 195.09999999999994), (Brazil, 190.09999999999997), (Germany, 156.48), (United Kingdom, 112.85999999999999), (Czech Republic, 90.24000000000001), (Portugal, 77.23999999999998), (India, 75.25999999999999), (Chile, 46.62), (Ireland, 45.62), (Hungary, 45.62), (Austria, 42.62), (Finland, 41.620000000000005), (Netherlands, 40.62), (Norway, 39.62), (Sweden, 38.620000000000005), (Poland, 37.620000000000005), (Italy, 37.620000000000005), (Denmark, 37.620000000000005), (Australia, 37.620000000000005), (Argentina, 37.620000000000005), (Spain, 37.62), (Belgium, 37.62)]
Thought:I now know the final answer
Final Answer: Customers from the USA spent the most with a total of $523.06, followed by customers from Canada who spent $303.96, and customers from France who spent $195.10. Finished chain.最后输出结果
Customers from the USA spent the most with a total of $523.06, followed by customers from Canada who spent $303.96, and customers from France who spent $195.10.2-7. 第4个测试 - 运行查询2
agent_executor.run(Show the total number of tracks in each playlist. The Playlist name should be included in the result.
)执行过程 Entering new AgentExecutor chain...
Action: sql_db_list_tables
Action Input:
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought:The Playlist and PlaylistTrack tables seem relevant. I should query their schema to see what columns they have.
Action: sql_db_schema
Action Input: Playlist, PlaylistTrack
Observation:
CREATE TABLE Playlist (PlaylistId INTEGER NOT NULL, Name NVARCHAR(120), PRIMARY KEY (PlaylistId)
)/*
3 rows from Playlist table:
PlaylistId Name
1 Music
2 Movies
3 TV Shows
*/CREATE TABLE PlaylistTrack (PlaylistId INTEGER NOT NULL, TrackId INTEGER NOT NULL, PRIMARY KEY (PlaylistId, TrackId), FOREIGN KEY(TrackId) REFERENCES Track (TrackId), FOREIGN KEY(PlaylistId) REFERENCES Playlist (PlaylistId)
)/*
3 rows from PlaylistTrack table:
PlaylistId TrackId
1 3402
1 3389
1 3390
*/
Thought:I can construct a SQL query to join the Playlist and PlaylistTrack tables on their common column PlaylistId, then group by the Name column in the Playlist table to get the total number of tracks in each playlist. I should double check this query before executing it.
Action: sql_db_query_checker
Action Input: SELECT Playlist.Name, COUNT(PlaylistTrack.TrackId) FROM Playlist INNER JOIN PlaylistTrack ON Playlist.PlaylistId PlaylistTrack.PlaylistId GROUP BY Playlist.Name
Observation: SELECT Playlist.Name, COUNT(PlaylistTrack.TrackId) FROM Playlist
INNER JOIN PlaylistTrack ON Playlist.PlaylistId PlaylistTrack.PlaylistId
GROUP BY Playlist.NameThe SQL query above does not contain any of the common mistakes listed. Therefore, I will reproduce the original query as the final SQL query:SELECT Playlist.Name, COUNT(PlaylistTrack.TrackId) FROM Playlist
INNER JOIN PlaylistTrack ON Playlist.PlaylistId PlaylistTrack.PlaylistId
GROUP BY Playlist.Name
Thought:I can now execute the final SQL query to get the answer.
Action: sql_db_query
Action Input: SELECT Playlist.Name, COUNT(PlaylistTrack.TrackId) FROM Playlist INNER JOIN PlaylistTrack ON Playlist.PlaylistId PlaylistTrack.PlaylistId GROUP BY Playlist.Name
Observation: [(90’s Music, 1477), (Brazilian Music, 39), (Classical, 75), (Classical 101 - Deep Cuts, 25), (Classical 101 - Next Steps, 25), (Classical 101 - The Basics, 25), (Grunge, 15), (Heavy Metal Classic, 26), (Music, 6580), (Music Videos, 1), (On-The-Go 1, 1), (TV Shows, 426)]
Thought:I now know the final answer.
Final Answer: Here are the total number of tracks in each playlist with their respective names: [(90’s Music, 1477), (Brazilian Music, 39), (Classical, 75), (Classical 101 - Deep Cuts, 25), (Classical 101 - Next Steps, 25), (Classical 101 - The Basics, 25), (Grunge, 15), (Heavy Metal Classic, 26), (Music, 6580), (Music Videos, 1), (On-The-Go 1, 1), (TV Shows, 426)] Finished chain.最后输出结果
Here are the total number of tracks in each playlist with their respective names: [(90’s Music, 1477), (Brazilian Music, 39), (Classical, 75), (Classical 101 - Deep Cuts, 25), (Classical 101 - Next Steps, 25), (Classical 101 - The Basics, 25), (Grunge, 15), (Heavy Metal Classic, 26), (Music, 6580), (Music Videos, 1), (On-The-Go 1, 1), (TV Shows, 426)]2-8. 第5个测试 - 从错误中恢复
agent_executor.run(Who are the top 3 best selling artists?)执行过程 Entering new AgentExecutor chain...
Action: sql_db_list_tables
Action Input:
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought:The Artist and Invoice tables seem relevant. I should query their schema to see what columns they have.
Action: sql_db_schema
Action Input: Artist, Invoice
Observation:
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
*/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
*/
Thought:The Artist table has an ArtistId and a Name column, but it doesnt seem to have any information about sales. The Invoice table has an InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, and Total column, but it also doesnt seem to have any information about artists. I need to find a table that links artists to invoices or sales.Action: sql_db_schema
Action Input: InvoiceLine
Observation:
CREATE TABLE InvoiceLine (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 Track (TrackId), FOREIGN KEY(InvoiceId) REFERENCES Invoice (InvoiceId)
)/*
3 rows from InvoiceLine table:
InvoiceLineId InvoiceId TrackId UnitPrice Quantity
1 1 2 0.99 1
2 1 4 0.99 1
3 2 6 0.99 1
*/
Thought:The InvoiceLine table seems to link invoices to tracks, and the Track table might have information about artists. I should query the schema of the Track table to see what columns it has.Action: sql_db_schema
Action Input: Track
Observation:
CREATE TABLE Track (TrackId INTEGER NOT NULL, Name NVARCHAR(200) NOT NULL, AlbumId INTEGER, MediaTypeId INTEGER NOT NULL, GenreId INTEGER, Composer NVARCHAR(220), Milliseconds INTEGER NOT NULL, Bytes INTEGER, UnitPrice NUMERIC(10, 2) NOT NULL, PRIMARY KEY (TrackId), FOREIGN KEY(MediaTypeId) REFERENCES MediaType (MediaTypeId), FOREIGN KEY(GenreId) REFERENCES Genre (GenreId), FOREIGN KEY(AlbumId) REFERENCES Album (AlbumId)
)/*
3 rows from Track table:
TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes UnitPrice
1 For Those About To Rock (We Salute You) 1 1 1 Angus Young, Malcolm Young, Brian Johnson 343719 11170334 0.99
2 Balls to the Wall 2 2 1 None 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider W. Hoffman 230619 3990994 0.99
*/
Thought:The Track table has a TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, and UnitPrice column, but it doesnt seem to have any information about artists. However, the ArtistId from the Artist table is not present in any of the tables I have queried so far. It seems like there might not be a direct link between the artists and their sales.In this case, I can provide the top 3 best-selling tracks instead of artists, as it is the closest information I can extract from the schema provided.Question: Who are the top 3 best selling artists?
Thought: It seems like there isnt a direct link between the artists and their sales in the database schema. I will provide the top 3 best-selling tracks instead.
Final Answer: Here are the top 3 best-selling tracks: Finished chain.最后输出结果(实际没有输出正确的结果)
Here are the top 3 best-selling tracks:3. 结论
从执行过程可知SQLDatabaseToolkit 有下面 4 个预置 Agent根据我们发送的消息调用相应的 Agent查看数据库中有什么表将要使用的表有什么字段然后根据自然语言的消息生成 SQL 语句并且进行生成的 SQL。
sql_db_query, sql_db_schema, sql_db_list_tables, sql_db_query_checker从执行过程可知在我们发送的信息还比较 “靠谱” 的情况大概率是能够得到正确的结果的。如果我们发送的信息 “不靠谱” 的情况大语言模型就不一定给我们反馈什么结果了。
完结
