LLM如何基于SQL做QA（二）#
一篇已经讲了如何基于SQL做QA，但是那篇文章只是简单的介绍了一下，这篇会介绍通过一些方式来让LLM更好的理解SQL。 在开始之前，我们先准备下环境，和上一章一样，具体看上一章。
from langchain_community.utilities import SQLDatabase
db = SQLDatabase.from_uri("sqlite:///Chinook.db")
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM Artist LIMIT 10;")
sqlite
['Album', 'Artist', 'Customer', 'Employee', 'Genre', 'Invoice', 'InvoiceLine', 'MediaType', 'Playlist', 'PlaylistTrack', 'Track']
"[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains'), (6, 'Antônio Carlos Jobim'), (7, 'Apocalyptica'), (8, 'Audioslave'), (9, 'BackBeat'), (10, 'Billy Cobham')]"
在Prompt中增加Example#
LangChain提供了封装，可以支持sql的方言(dialects)，会自动做处理，这样就可以在prompt中增加example，让LLM更好的理解SQL。
create_sql_query_chain和SQLDatabase都会自动处理以下任何方言：
from langchain.chains.sql_database.prompt import SQL_PROMPTS
list(SQL_PROMPTS)
['crate',
'duckdb',
'googlesql',
'mssql',
'mysql',
'mariadb',
'oracle',
'postgresql',
'sqlite',
'clickhouse',
'prestodb']
当前使用的时候的是sqlite，所以我们可以使用sqlite的prompt。
from langchain_openai import ChatOpenAI
llm = ChatOpenAI(model="gpt-3.5-turbo-0125", temperature=0)
from langchain.chains import create_sql_query_chain
chain = create_sql_query_chain(llm, db)
chain.get_prompts()[0].pretty_print()
You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".
Use the following format:
Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here
Only use the following tables:
{table_info}
Question: {input}
我们需要将表的结构传递给模型模型，这样模型才能更好的生成SQL。LangChain提供了一些方法来拿到表结构，同时还可以生成数据示例。
context = db.get_context()
print(list(context))
print("*"*10)
print(context["table_info"])
print("*"*10)
print(context["table_names"])
['table_info', 'table_names']
**********
CREATE TABLE "Album" (
"AlbumId" INTEGER NOT NULL,
"Title" NVARCHAR(160) NOT NULL,
"ArtistId" INTEGER NOT NULL,
PRIMARY KEY ("AlbumId"),
FOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")
)
/*
3 rows from Album table:
AlbumId Title ArtistId
1 For Those About To Rock We Salute You 1
2 Balls to the Wall 2
3 Restless and Wild 2
*/
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 "Customer" (
"CustomerId" INTEGER NOT NULL,
"FirstName" NVARCHAR(40) NOT NULL,
"LastName" NVARCHAR(20) NOT NULL,
"Company" NVARCHAR(80),
"Address" NVARCHAR(70),
"City" NVARCHAR(40),
"State" NVARCHAR(40),
"Country" NVARCHAR(40),
"PostalCode" NVARCHAR(10),
"Phone" NVARCHAR(24),
"Fax" NVARCHAR(24),
"Email" NVARCHAR(60) NOT NULL,
"SupportRepId" INTEGER,
PRIMARY KEY ("CustomerId"),
FOREIGN KEY("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)
/*
3 rows from Customer table:
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 luisg@embraer.com.br 3
2 Leonie Köhler None Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 +49 0711 2842222 None leonekohler@surfeu.de 5
3 François Tremblay None 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 None ftremblay@gmail.com 3
*/
CREATE TABLE "Employee" (
"EmployeeId" INTEGER NOT NULL,
"LastName" NVARCHAR(20) NOT NULL,
"FirstName" NVARCHAR(20) NOT NULL,
"Title" NVARCHAR(30),
"ReportsTo" INTEGER,
"BirthDate" DATETIME,
"HireDate" DATETIME,
"Address" NVARCHAR(70),
"City" NVARCHAR(40),
"State" NVARCHAR(40),
"Country" NVARCHAR(40),
"PostalCode" NVARCHAR(10),
"Phone" NVARCHAR(24),
"Fax" NVARCHAR(24),
"Email" NVARCHAR(60),
PRIMARY KEY ("EmployeeId"),
FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)
/*
3 rows from Employee table:
EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City State Country PostalCode Phone Fax Email
1 Adams Andrew General Manager None 1962-02-18 00:00:00 2002-08-14 00:00:00 11120 Jasper Ave NW Edmonton AB Canada T5K 2N1 +1 (780) 428-9482 +1 (780) 428-3457 andrew@chinookcorp.com
2 Edwards Nancy Sales Manager 1 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 nancy@chinookcorp.com
3 Peacock Jane Sales Support Agent 2 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary AB Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 jane@chinookcorp.com
*/
CREATE TABLE "Genre" (
"GenreId" INTEGER NOT NULL,
"Name" NVARCHAR(120),
PRIMARY KEY ("GenreId")
)
/*
3 rows from Genre table:
GenreId Name
1 Rock
2 Jazz
3 Metal
*/
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 2021-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 1.98
2 4 2021-01-02 00:00:00 Ullevålsveien 14 Oslo None Norway 0171 3.96
3 8 2021-01-03 00:00:00 Grétrystraat 63 Brussels None Belgium 1000 5.94
*/
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
*/
CREATE TABLE "MediaType" (
"MediaTypeId" INTEGER NOT NULL,
"Name" NVARCHAR(120),
PRIMARY KEY ("MediaTypeId")
)
/*
3 rows from MediaType table:
MediaTypeId Name
1 MPEG audio file
2 Protected AAC audio file
3 Protected MPEG-4 video file
*/
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
*/
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 U. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 230619 3990994 0.99
*/
**********
Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
注意看，table_info中已经包含了表的结构，数据示例 完整的Prompt如下：
prompt_with_context = chain.get_prompts()[0].partial(table_info=context["table_info"])
print(prompt_with_context.pretty_repr())
You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".
Use the following format:
Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here
Only use the following tables:
CREATE TABLE "Album" (
"AlbumId" INTEGER NOT NULL,
"Title" NVARCHAR(160) NOT NULL,
"ArtistId" INTEGER NOT NULL,
PRIMARY KEY ("AlbumId"),
FOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")
)
/*
3 rows from Album table:
AlbumId Title ArtistId
1 For Those About To Rock We Salute You 1
2 Balls to the Wall 2
3 Restless and Wild 2
*/
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 "Customer" (
"CustomerId" INTEGER NOT NULL,
"FirstName" NVARCHAR(40) NOT NULL,
"LastName" NVARCHAR(20) NOT NULL,
"Company" NVARCHAR(80),
"Address" NVARCHAR(70),
"City" NVARCHAR(40),
"State" NVARCHAR(40),
"Country" NVARCHAR(40),
"PostalCode" NVARCHAR(10),
"Phone" NVARCHAR(24),
"Fax" NVARCHAR(24),
"Email" NVARCHAR(60) NOT NULL,
"SupportRepId" INTEGER,
PRIMARY KEY ("CustomerId"),
FOREIGN KEY("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)
/*
3 rows from Customer table:
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 luisg@embraer.com.br 3
2 Leonie Köhler None Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 +49 0711 2842222 None leonekohler@surfeu.de 5
3 François Tremblay None 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 None ftremblay@gmail.com 3
*/
CREATE TABLE "Employee" (
"EmployeeId" INTEGER NOT NULL,
"LastName" NVARCHAR(20) NOT NULL,
"FirstName" NVARCHAR(20) NOT NULL,
"Title" NVARCHAR(30),
"ReportsTo" INTEGER,
"BirthDate" DATETIME,
"HireDate" DATETIME,
"Address" NVARCHAR(70),
"City" NVARCHAR(40),
"State" NVARCHAR(40),
"Country" NVARCHAR(40),
"PostalCode" NVARCHAR(10),
"Phone" NVARCHAR(24),
"Fax" NVARCHAR(24),
"Email" NVARCHAR(60),
PRIMARY KEY ("EmployeeId"),
FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)
/*
3 rows from Employee table:
EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City State Country PostalCode Phone Fax Email
1 Adams Andrew General Manager None 1962-02-18 00:00:00 2002-08-14 00:00:00 11120 Jasper Ave NW Edmonton AB Canada T5K 2N1 +1 (780) 428-9482 +1 (780) 428-3457 andrew@chinookcorp.com
2 Edwards Nancy Sales Manager 1 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary AB Canada T2P 2T3 +1 (403) 262-3443 +1 (403) 262-3322 nancy@chinookcorp.com
3 Peacock Jane Sales Support Agent 2 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary AB Canada T2P 5M5 +1 (403) 262-3443 +1 (403) 262-6712 jane@chinookcorp.com
*/
CREATE TABLE "Genre" (
"GenreId" INTEGER NOT NULL,
"Name" NVARCHAR(120),
PRIMARY KEY ("GenreId")
)
/*
3 rows from Genre table:
GenreId Name
1 Rock
2 Jazz
3 Metal
*/
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 2021-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 1.98
2 4 2021-01-02 00:00:00 Ullevålsveien 14 Oslo None Norway 0171 3.96
3 8 2021-01-03 00:00:00 Grétrystraat 63 Brussels None Belgium 1000 5.94
*/
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
*/
CREATE TABLE "MediaType" (
"MediaTypeId" INTEGER NOT NULL,
"Name" NVARCHAR(120),
PRIMARY KEY ("MediaTypeId")
)
/*
3 rows from MediaType table:
MediaTypeId Name
1 MPEG audio file
2 Protected AAC audio file
3 Protected MPEG-4 video file
*/
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
*/
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 U. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 230619 3990994 0.99
*/
Question: {input}
这里有个问题，可以看到，这会将所有的表结构传递给模型，这样会导致模型的输入过大，可能会超过模型的上下文（当然在现在也不是问题，可以一块传递），还有一些优化方式来减少传递的数据，比如只传递部分表结构，或者只传递部分表的数据示例。下面会介绍如何做。
增加示例#
examples = [
{"input": "List all artists.", "query": "SELECT * FROM Artist;"},
{
"input": "Find all albums for the artist 'AC/DC'.",
"query": "SELECT * FROM Album WHERE ArtistId = (SELECT ArtistId FROM Artist WHERE Name = 'AC/DC');",
},
{
"input": "List all tracks in the 'Rock' genre.",
"query": "SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');",
},
{
"input": "Find the total duration of all tracks.",
"query": "SELECT SUM(Milliseconds) FROM Track;",
},
{
"input": "List all customers from Canada.",
"query": "SELECT * FROM Customer WHERE Country = 'Canada';",
},
{
"input": "How many tracks are there in the album with ID 5?",
"query": "SELECT COUNT(*) FROM Track WHERE AlbumId = 5;",
},
{
"input": "Find the total number of invoices.",
"query": "SELECT COUNT(*) FROM Invoice;",
},
{
"input": "List all tracks that are longer than 5 minutes.",
"query": "SELECT * FROM Track WHERE Milliseconds > 300000;",
},
{
"input": "Who are the top 5 customers by total purchase?",
"query": "SELECT CustomerId, SUM(Total) AS TotalPurchase FROM Invoice GROUP BY CustomerId ORDER BY TotalPurchase DESC LIMIT 5;",
},
{
"input": "Which albums are from the year 2000?",
"query": "SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';",
},
{
"input": "How many employees are there",
"query": 'SELECT COUNT(*) FROM "Employee"',
},
]
# 创建 FewShotPrompt
from langchain_core.prompts import FewShotPromptTemplate, PromptTemplate
example_prompt = PromptTemplate.from_template("User input: {input}\nSQL query: {query}")
prompt = FewShotPromptTemplate(
examples=examples[:5],
example_prompt=example_prompt,
prefix="You are a SQLite expert. Given an input question, create a syntactically correct SQLite query to run. Unless otherwise specificed, do not return more than {top_k} rows.\n\nHere is the relevant table info: {table_info}\n\nBelow are a number of examples of questions and their corresponding SQL queries.",
suffix="User input: {input}\nSQL query: ",
input_variables=["input", "top_k", "table_info"],
)
print(prompt.format(input="How many artists are there?", top_k=3, table_info="foo"))
You are a SQLite expert. Given an input question, create a syntactically correct SQLite query to run. Unless otherwise specificed, do not return more than 3 rows.
Here is the relevant table info: foo
Below are a number of examples of questions and their corresponding SQL queries.
User input: List all artists.
SQL query: SELECT * FROM Artist;
User input: Find all albums for the artist 'AC/DC'.
SQL query: SELECT * FROM Album WHERE ArtistId = (SELECT ArtistId FROM Artist WHERE Name = 'AC/DC');
User input: List all tracks in the 'Rock' genre.
SQL query: SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');
User input: Find the total duration of all tracks.
SQL query: SELECT SUM(Milliseconds) FROM Track;
User input: List all customers from Canada.
SQL query: SELECT * FROM Customer WHERE Country = 'Canada';
User input: How many artists are there?
SQL query:
动态的few-shot prompt#
如果我们有足够的例子，我们可能只想在提示中包含最相关的例子，要么是因为它们不适合模型的上下文窗口，要么是因为长尾的例子会分散模型的注意力。所以，对于任何输入，我们希望包含与该输入最相关的示例。
可以使用SemanticSimilarityExampleSelector,它会根据输入的相似度来选择最相关的例子。底层是将数据放在向量数据库中，然后使用向量相似度来选择最相关的例子。
from langchain_community.vectorstores import Chroma
from langchain_core.example_selectors import SemanticSimilarityExampleSelector
from langchain_openai import OpenAIEmbeddings
example_selector = SemanticSimilarityExampleSelector.from_examples(
examples,
OpenAIEmbeddings(),
Chroma,
k=5,
input_keys=["input"],
)
example_selector.select_examples({"input": "how many artists are there?"})
[{'input': 'List all artists.', 'query': 'SELECT * FROM Artist;'},
{'input': 'How many employees are there',
'query': 'SELECT COUNT(*) FROM "Employee"'},
{'input': 'How many tracks are there in the album with ID 5?',
'query': 'SELECT COUNT(*) FROM Track WHERE AlbumId = 5;'},
{'input': 'Which albums are from the year 2000?',
'query': "SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';"},
{'input': "List all tracks in the 'Rock' genre.",
'query': "SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');"}]
可以看到已经选择好了,可以代入到FewShotPromptTemplate中如下
prompt = FewShotPromptTemplate(
example_selector=example_selector,
example_prompt=example_prompt,
prefix="You are a SQLite expert. Given an input question, create a syntactically correct SQLite query to run. Unless otherwise specificed, do not return more than {top_k} rows.\n\nHere is the relevant table info: {table_info}\n\nBelow are a number of examples of questions and their corresponding SQL queries.",
suffix="User input: {input}\nSQL query: ",
input_variables=["input", "top_k", "table_info"],
)
print(prompt.format(input="how many artists are there?", top_k=3, table_info="foo"))
You are a SQLite expert. Given an input question, create a syntactically correct SQLite query to run. Unless otherwise specificed, do not return more than 3 rows.
Here is the relevant table info: foo
Below are a number of examples of questions and their corresponding SQL queries.
User input: List all artists.
SQL query: SELECT * FROM Artist;
User input: How many employees are there
SQL query: SELECT COUNT(*) FROM "Employee"
User input: How many tracks are there in the album with ID 5?
SQL query: SELECT COUNT(*) FROM Track WHERE AlbumId = 5;
User input: Which albums are from the year 2000?
SQL query: SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';
User input: List all tracks in the 'Rock' genre.
SQL query: SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');
User input: how many artists are there?
SQL query:
验证查询和处理无效查询#
验证查询#
原理：增加一个新的chain，用来验证查询，如果查询有问题，就重写查询，如果没有问题，就直接返回查询。
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import ChatPromptTemplate
system = """Double check the user's {dialect} query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins
If there are any of the above mistakes, rewrite the query.
If there are no mistakes, just reproduce the original query with no further commentary.
Output the final SQL query only."""
prompt = ChatPromptTemplate.from_messages(
[("system", system), ("human", "{query}")]
).partial(dialect=db.dialect)
validation_chain = prompt | llm | StrOutputParser()
full_chain = {"query": chain} | validation_chain
from langchain.globals import set_debug
set_debug(True)
query = full_chain.invoke(
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
)
print(query)
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query>] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info>] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [0ms] Exiting Chain run with output:
{
"output": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: "
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [3ms] Exiting Chain run with output:
{
"output": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] [4ms] Exiting Chain run with output:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableAssign<input,table_info>] [6ms] Exiting Chain run with output:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:RunnableLambda] [0ms] Exiting Chain run with output:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > prompt:PromptTemplate] Entering Prompt run with input:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > prompt:PromptTemplate] [0ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"Human: You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.\nUnless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.\nNever query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (\") to denote them as delimited identifiers.\nPay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\nPay attention to use date('now') function to get the current date, if the question involves \"today\".\n\nUse the following format:\n\nQuestion: Question here\nSQLQuery: SQL Query to run\nSQLResult: Result of the SQLQuery\nAnswer: Final answer here\n\nOnly use the following tables:\n\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/\n\nQuestion: What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery:"
]
}
[llm/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > llm:ChatOpenAI] [2.63s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';",
"generation_info": {
"finish_reason": "stop",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';",
"response_metadata": {
"token_usage": {
"completion_tokens": 69,
"prompt_tokens": 2386,
"total_tokens": 2455
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "stop",
"logprobs": null
},
"type": "ai",
"id": "run-ba2cdcc1-5fd5-49f6-8130-3e22bf588cdb-0",
"usage_metadata": {
"input_tokens": 2386,
"output_tokens": 69,
"total_tokens": 2455
},
"tool_calls": [],
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 69,
"prompt_tokens": 2386,
"total_tokens": 2455
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > parser:StrOutputParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > parser:StrOutputParser] [1ms] Exiting Parser run with output:
{
"output": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/start] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:_strip] Entering Chain run with input:
{
"input": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence > chain:_strip] [1ms] Exiting Chain run with output:
{
"output": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query> > chain:RunnableSequence] [2.65s] Exiting Chain run with output:
{
"output": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/end] [chain:RunnableSequence > chain:RunnableParallel<query>] [2.65s] Exiting Chain run with output:
{
"query": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/start] [chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"query": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/end] [chain:RunnableSequence > prompt:ChatPromptTemplate] [1ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: Double check the user's sqlite query for common mistakes, including:\n- Using NOT IN with NULL values\n- Using UNION when UNION ALL should have been used\n- Using BETWEEN for exclusive ranges\n- Data type mismatch in predicates\n- Properly quoting identifiers\n- Using the correct number of arguments for functions\n- Casting to the correct data type\n- Using the proper columns for joins\n\nIf there are any of the above mistakes, rewrite the query.\nIf there are no mistakes, just reproduce the original query with no further commentary.\n\nOutput the final SQL query only.\nHuman: SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice i\nJOIN Customer c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [1.41s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice AS i\nJOIN Customer AS c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';",
"generation_info": {
"finish_reason": "stop",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice AS i\nJOIN Customer AS c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';",
"response_metadata": {
"token_usage": {
"completion_tokens": 71,
"prompt_tokens": 194,
"total_tokens": 265
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "stop",
"logprobs": null
},
"type": "ai",
"id": "run-02c0a598-6ccc-4a0a-93a5-24604576658f-0",
"usage_metadata": {
"input_tokens": 194,
"output_tokens": 71,
"total_tokens": 265
},
"tool_calls": [],
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 71,
"prompt_tokens": 194,
"total_tokens": 265
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:StrOutputParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:StrOutputParser] [2ms] Exiting Parser run with output:
{
"output": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice AS i\nJOIN Customer AS c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
[chain/end] [chain:RunnableSequence] [4.09s] Exiting Chain run with output:
{
"output": "SELECT AVG(i.Total) AS AverageInvoice\nFROM Invoice AS i\nJOIN Customer AS c ON i.CustomerId = c.CustomerId\nWHERE c.Country = 'USA' \nAND c.Fax IS NULL\nAND i.InvoiceDate >= '2003-01-01' \nAND i.InvoiceDate < '2010-01-01';"
}
SELECT AVG(i.Total) AS AverageInvoice
FROM Invoice AS i
JOIN Customer AS c ON i.CustomerId = c.CustomerId
WHERE c.Country = 'USA'
AND c.Fax IS NULL
AND i.InvoiceDate >= '2003-01-01'
AND i.InvoiceDate < '2010-01-01';
langsmith地址：https://smith.langchain.com/public/f761d577-b89b-4944-9faa-c07c0df89c6b/r
这个方法的缺点是，需要进行两次模型调用，可以在一次模型调用中完成，下面会介绍如何做。 其实就是将两个chain中的promot合并为一个Prompt
system = """You are a {dialect} expert. Given an input question, create a syntactically correct {dialect} query to run.
Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per {dialect}. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".
Only use the following tables:
{table_info}
Write an initial draft of the query. Then double check the {dialect} query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins
Use format:
First draft: <<FIRST_DRAFT_QUERY>>
Final answer: <<FINAL_ANSWER_QUERY>>
"""
prompt = ChatPromptTemplate.from_messages(
[("system", system), ("human", "{input}")]
).partial(dialect=db.dialect)
def parse_final_answer(output: str) -> str:
return output.split("Final answer: ")[1]
chain = create_sql_query_chain(llm, db, prompt=prompt) | parse_final_answer
prompt.pretty_print()
================================ System Message ================================
You are a {dialect} expert. Given an input question, create a syntactically correct {dialect} query to run.
Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per {dialect}. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".
Only use the following tables:
{table_info}
Write an initial draft of the query. Then double check the {dialect} query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins
Use format:
First draft: <<FIRST_DRAFT_QUERY>>
Final answer: <<FINAL_ANSWER_QUERY>>
================================ Human Message =================================
{input}
query = chain.invoke(
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
)
print(query)
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info>] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [1ms] Exiting Chain run with output:
{
"output": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: "
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [3ms] Exiting Chain run with output:
{
"output": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] [6ms] Exiting Chain run with output:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info>] [8ms] Exiting Chain run with output:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableLambda] [0ms] Exiting Chain run with output:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"input": "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > prompt:ChatPromptTemplate] [1ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: You are a sqlite expert. Given an input question, create a syntactically correct sqlite query to run.\nUnless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per sqlite. You can order the results to return the most informative data in the database.\nNever query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (\") to denote them as delimited identifiers.\nPay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\nPay attention to use date('now') function to get the current date, if the question involves \"today\".\n\nOnly use the following tables:\n\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/\n\nWrite an initial draft of the query. Then double check the sqlite query for common mistakes, including:\n- Using NOT IN with NULL values\n- Using UNION when UNION ALL should have been used\n- Using BETWEEN for exclusive ranges\n- Data type mismatch in predicates\n- Properly quoting identifiers\n- Using the correct number of arguments for functions\n- Casting to the correct data type\n- Using the proper columns for joins\n\nUse format:\n\nFirst draft: <<FIRST_DRAFT_QUERY>>\nFinal answer: <<FINAL_ANSWER_QUERY>>\n\nHuman: What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010\nSQLQuery:"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [2.38s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'",
"generation_info": {
"finish_reason": "stop",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'",
"response_metadata": {
"token_usage": {
"completion_tokens": 154,
"prompt_tokens": 2443,
"total_tokens": 2597
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "stop",
"logprobs": null
},
"type": "ai",
"id": "run-9ac5a736-1a48-41b2-a8cd-9291146438d5-0",
"usage_metadata": {
"input_tokens": 2443,
"output_tokens": 154,
"total_tokens": 2597
},
"tool_calls": [],
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 154,
"prompt_tokens": 2443,
"total_tokens": 2597
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:StrOutputParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:StrOutputParser] [1ms] Exiting Parser run with output:
{
"output": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
[chain/start] [chain:RunnableSequence > chain:_strip] Entering Chain run with input:
{
"input": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
[chain/end] [chain:RunnableSequence > chain:_strip] [1ms] Exiting Chain run with output:
{
"output": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
[chain/start] [chain:RunnableSequence > chain:parse_final_answer] Entering Chain run with input:
{
"input": "First draft: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'\n\nFinal answer: \nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
[chain/end] [chain:RunnableSequence > chain:parse_final_answer] [1ms] Exiting Chain run with output:
{
"output": "\nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
[chain/end] [chain:RunnableSequence] [2.41s] Exiting Chain run with output:
{
"output": "\nSELECT AVG(\"Total\") as \"AverageInvoice\"\nFROM \"Invoice\" i\nJOIN \"Customer\" c ON i.\"CustomerId\" = c.\"CustomerId\"\nWHERE c.\"Country\" = 'USA' \nAND c.\"Fax\" IS NULL\nAND i.\"InvoiceDate\" BETWEEN '2003-01-01' AND '2010-01-01'"
}
SELECT AVG("Total") as "AverageInvoice"
FROM "Invoice" i
JOIN "Customer" c ON i."CustomerId" = c."CustomerId"
WHERE c."Country" = 'USA'
AND c."Fax" IS NULL
AND i."InvoiceDate" BETWEEN '2003-01-01' AND '2010-01-01'
langsmith地址：https://smith.langchain.com/public/339a1303-f015-4b19-bd95-916dc35e5c5e/r
处理无效查询#
建议还是人为的处理，因为无效查询的原因太多了，模型无法处理所有的情况。并且不能保证生产的sql是绝对安全的，需要人为介入。
处理大量表的情况#
在前面讲过，产生这个问题的背景，下面会介绍如何处理这个问题。 其实这里的思想还是计算机中经典的思想，分治法，将大问题分解为小问题，然后递归的解决小问题，最后合并结果。
将问题归类到相关的表中（一次性拿到所有的表）#
有个点。 我们需要在Prompt中包含的主要信息是相关表的schema。当我们有很多表时，我们无法将所有schema都放在一个Prompt中。在这种情况下，我们可以首先提取与用户输入相关的表的名称，然后只包含它们的schema。
## 第一步，通过问题找到相关的表
from langchain_core.output_parsers.openai_tools import PydanticToolsParser
from langchain_core.prompts import ChatPromptTemplate
from langchain_core.pydantic_v1 import BaseModel, Field
class Table(BaseModel):
"""Table in SQL database."""
name: str = Field(description="Name of table in SQL database.")
table_names = "\n".join(db.get_usable_table_names())
system = f"""Return the names of ALL the SQL tables that MIGHT be relevant to the user question. \
The tables are:
{table_names}
Remember to include ALL POTENTIALLY RELEVANT tables, even if you're not sure that they're needed."""
prompt = ChatPromptTemplate.from_messages(
[
("system", system),
("human", "{input}"),
]
)
llm_with_tools = llm.bind_tools([Table])
output_parser = PydanticToolsParser(tools=[Table])
table_chain = prompt | llm_with_tools | output_parser
table_chain.invoke({"input": "What are all the genres of Alanis Morisette songs"})
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > prompt:ChatPromptTemplate] [1ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: Return the names of ALL the SQL tables that MIGHT be relevant to the user question. The tables are:\n\nAlbum\nArtist\nCustomer\nEmployee\nGenre\nInvoice\nInvoiceLine\nMediaType\nPlaylist\nPlaylistTrack\nTrack\n\nRemember to include ALL POTENTIALLY RELEVANT tables, even if you're not sure that they're needed.\nHuman: What are all the genres of Alanis Morisette songs"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [957ms] Exiting LLM run with output:
{
"generations": [
[
{
"text": "",
"generation_info": {
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "",
"additional_kwargs": {
"tool_calls": [
{
"id": "call_7ExZIq7rLhGMV2ld6HLxf7Id",
"function": {
"arguments": "{\"name\":\"Genre\"}",
"name": "Table"
},
"type": "function"
}
]
},
"response_metadata": {
"token_usage": {
"completion_tokens": 13,
"prompt_tokens": 133,
"total_tokens": 146
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ai",
"id": "run-326aaa8c-4052-4cd2-b68e-b78b03a58a04-0",
"tool_calls": [
{
"name": "Table",
"args": {
"name": "Genre"
},
"id": "call_7ExZIq7rLhGMV2ld6HLxf7Id",
"type": "tool_call"
}
],
"usage_metadata": {
"input_tokens": 133,
"output_tokens": 13,
"total_tokens": 146
},
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 13,
"prompt_tokens": 133,
"total_tokens": 146
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:PydanticToolsParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:PydanticToolsParser] [1ms] Exiting Parser run with output:
[outputs]
[chain/end] [chain:RunnableSequence] [963ms] Exiting Chain run with output:
[outputs]
[Table(name='Genre')]
我们已经找到了相关的表，还有一种方式，可以对表做分组，然后根据分组来找到相关的表。如下：
将问题归类到相关的表中（做归类查询，先将表做归类）#
system = """Return the names of any SQL tables that are relevant to the user question.
The tables are:
Music
Business
"""
prompt = ChatPromptTemplate.from_messages(
[
("system", system),
("human", "{input}"),
]
)
category_chain = prompt | llm_with_tools | output_parser
category_chain.invoke({"input": "What are all the genres of Alanis Morisette songs"})
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > prompt:ChatPromptTemplate] [2ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: Return the names of any SQL tables that are relevant to the user question.\nThe tables are:\n\nMusic\nBusiness\n\nHuman: What are all the genres of Alanis Morisette songs"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [1.44s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "",
"generation_info": {
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "",
"additional_kwargs": {
"tool_calls": [
{
"id": "call_AXXRNckuZyvNfGoTplFOLCZM",
"function": {
"arguments": "{\"name\": \"Music\"}",
"name": "Table"
},
"type": "function"
},
{
"id": "call_TUFDoinWFIZvuBfNcXFgYlKm",
"function": {
"arguments": "{\"name\": \"Business\"}",
"name": "Table"
},
"type": "function"
}
]
},
"response_metadata": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-3.5-turbo-0125",
"system_fingerprint": null,
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ai",
"id": "run-c4ed0fdb-03c3-4ef2-9451-ce2b44864457-0",
"tool_calls": [
{
"name": "Table",
"args": {
"name": "Music"
},
"id": "call_AXXRNckuZyvNfGoTplFOLCZM",
"type": "tool_call"
},
{
"name": "Table",
"args": {
"name": "Business"
},
"id": "call_TUFDoinWFIZvuBfNcXFgYlKm",
"type": "tool_call"
}
],
"usage_metadata": {
"input_tokens": 86,
"output_tokens": 41,
"total_tokens": 127
},
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-3.5-turbo-0125",
"system_fingerprint": null
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:PydanticToolsParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:PydanticToolsParser] [3ms] Exiting Parser run with output:
[outputs]
[chain/end] [chain:RunnableSequence] [1.45s] Exiting Chain run with output:
[outputs]
[Table(name='Music'), Table(name='Business')]
# 上一步中拿到了分类，其实就是对表做了归类，下面在继续找，从分类中找
from typing import List
## 从分类中找
def get_tables(categories: List[Table]) -> List[str]:
tables = []
for category in categories:
if category.name == "Music":
tables.extend(
[
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
]
)
elif category.name == "Business":
tables.extend(["Customer", "Employee", "Invoice", "InvoiceLine"])
return tables
## 链接到chain中。
table_chain = category_chain | get_tables
table_chain.invoke({"input": "What are all the genres of Alanis Morisette songs"})
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > prompt:ChatPromptTemplate] [1ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: Return the names of any SQL tables that are relevant to the user question.\nThe tables are:\n\nMusic\nBusiness\n\nHuman: What are all the genres of Alanis Morisette songs"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [1.24s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "",
"generation_info": {
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "",
"additional_kwargs": {
"tool_calls": [
{
"id": "call_YDLkOS5gts7PPs6UJhNr9UjH",
"function": {
"arguments": "{\"name\": \"Music\"}",
"name": "Table"
},
"type": "function"
},
{
"id": "call_tjgOcYd75Und00C6arb4JUIG",
"function": {
"arguments": "{\"name\": \"Business\"}",
"name": "Table"
},
"type": "function"
}
]
},
"response_metadata": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ai",
"id": "run-5d89c804-8649-4079-bd96-f381291f6fae-0",
"tool_calls": [
{
"name": "Table",
"args": {
"name": "Music"
},
"id": "call_YDLkOS5gts7PPs6UJhNr9UjH",
"type": "tool_call"
},
{
"name": "Table",
"args": {
"name": "Business"
},
"id": "call_tjgOcYd75Und00C6arb4JUIG",
"type": "tool_call"
}
],
"usage_metadata": {
"input_tokens": 86,
"output_tokens": 41,
"total_tokens": 127
},
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:PydanticToolsParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:PydanticToolsParser] [2ms] Exiting Parser run with output:
[outputs]
[chain/start] [chain:RunnableSequence > chain:get_tables] Entering Chain run with input:
[inputs]
[chain/end] [chain:RunnableSequence > chain:get_tables] [2ms] Exiting Chain run with output:
{
"output": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence] [1.26s] Exiting Chain run with output:
{
"output": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
['Album',
'Artist',
'Genre',
'MediaType',
'Playlist',
'PlaylistTrack',
'Track',
'Customer',
'Employee',
'Invoice',
'InvoiceLine']
这里已经找到了相关的表，我们再来回顾下，先找到分类，在通过分类找到表，这样就可以减少传递的数据，减少模型的输入。
from operator import itemgetter
from langchain.chains import create_sql_query_chain
from langchain_core.runnables import RunnablePassthrough
query_chain = create_sql_query_chain(llm, db)
# Convert "question" key to the "input" key expected by current table_chain.
table_chain = {"input": itemgetter("question")} | table_chain
# Set table_names_to_use using table_chain.
full_chain = RunnablePassthrough.assign(table_names_to_use=table_chain) | query_chain
query = full_chain.invoke(
{"question": "What are all the genres of Alanis Morisette songs"}
)
print(query)
[chain/start] [chain:RunnableSequence] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use>] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use>] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:RunnableParallel<input>] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:RunnableParallel<input> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:RunnableParallel<input> > chain:RunnableLambda] [0ms] Exiting Chain run with output:
{
"output": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:RunnableParallel<input>] [1ms] Exiting Chain run with output:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > prompt:ChatPromptTemplate] Entering Prompt run with input:
{
"input": "What are all the genres of Alanis Morisette songs"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > prompt:ChatPromptTemplate] [0ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"System: Return the names of any SQL tables that are relevant to the user question.\nThe tables are:\n\nMusic\nBusiness\n\nHuman: What are all the genres of Alanis Morisette songs"
]
}
[llm/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > llm:ChatOpenAI] [1.91s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "",
"generation_info": {
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "",
"additional_kwargs": {
"tool_calls": [
{
"id": "call_nEjvSiqnayVyXA6G0p1oHlef",
"function": {
"arguments": "{\"name\": \"Music\"}",
"name": "Table"
},
"type": "function"
},
{
"id": "call_E8OTTaITC4oB6tdIVThOMykG",
"function": {
"arguments": "{\"name\": \"Business\"}",
"name": "Table"
},
"type": "function"
}
]
},
"response_metadata": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-3.5-turbo-0125",
"system_fingerprint": null,
"finish_reason": "tool_calls",
"logprobs": null
},
"type": "ai",
"id": "run-f92252c5-3afb-477a-879a-cc88dc930a8d-0",
"tool_calls": [
{
"name": "Table",
"args": {
"name": "Music"
},
"id": "call_nEjvSiqnayVyXA6G0p1oHlef",
"type": "tool_call"
},
{
"name": "Table",
"args": {
"name": "Business"
},
"id": "call_E8OTTaITC4oB6tdIVThOMykG",
"type": "tool_call"
}
],
"usage_metadata": {
"input_tokens": 86,
"output_tokens": 41,
"total_tokens": 127
},
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 41,
"prompt_tokens": 86,
"total_tokens": 127
},
"model_name": "gpt-3.5-turbo-0125",
"system_fingerprint": null
},
"run": null
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > parser:PydanticToolsParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > parser:PydanticToolsParser] [2ms] Exiting Parser run with output:
[outputs]
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:get_tables] Entering Chain run with input:
[inputs]
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence > chain:get_tables] [1ms] Exiting Chain run with output:
{
"output": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use> > chain:RunnableSequence] [1.92s] Exiting Chain run with output:
{
"output": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use> > chain:RunnableParallel<table_names_to_use>] [1.92s] Exiting Chain run with output:
{
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<table_names_to_use>] [1.93s] Exiting Chain run with output:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info>] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [1ms] Exiting Chain run with output:
{
"output": "What are all the genres of Alanis Morisette songs\nSQLQuery: "
}
[chain/start] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
]
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info> > chain:RunnableLambda] [3ms] Exiting Chain run with output:
{
"output": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info> > chain:RunnableParallel<input,table_info>] [7ms] Exiting Chain run with output:
{
"input": "What are all the genres of Alanis Morisette songs\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableAssign<input,table_info>] [12ms] Exiting Chain run with output:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
],
"input": "What are all the genres of Alanis Morisette songs\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > chain:RunnableLambda] Entering Chain run with input:
{
"question": "What are all the genres of Alanis Morisette songs",
"table_names_to_use": [
"Album",
"Artist",
"Genre",
"MediaType",
"Playlist",
"PlaylistTrack",
"Track",
"Customer",
"Employee",
"Invoice",
"InvoiceLine"
],
"input": "What are all the genres of Alanis Morisette songs\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > chain:RunnableLambda] [0ms] Exiting Chain run with output:
{
"input": "What are all the genres of Alanis Morisette songs\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/start] [chain:RunnableSequence > prompt:PromptTemplate] Entering Prompt run with input:
{
"input": "What are all the genres of Alanis Morisette songs\nSQLQuery: ",
"table_info": "\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/"
}
[chain/end] [chain:RunnableSequence > prompt:PromptTemplate] [0ms] Exiting Prompt run with output:
[outputs]
[llm/start] [chain:RunnableSequence > llm:ChatOpenAI] Entering LLM run with input:
{
"prompts": [
"Human: You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.\nUnless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.\nNever query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (\") to denote them as delimited identifiers.\nPay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\nPay attention to use date('now') function to get the current date, if the question involves \"today\".\n\nUse the following format:\n\nQuestion: Question here\nSQLQuery: SQL Query to run\nSQLResult: Result of the SQLQuery\nAnswer: Final answer here\n\nOnly use the following tables:\n\nCREATE TABLE \"Album\" (\n\t\"AlbumId\" INTEGER NOT NULL, \n\t\"Title\" NVARCHAR(160) NOT NULL, \n\t\"ArtistId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"AlbumId\"), \n\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n)\n\n/*\n3 rows from Album table:\nAlbumId\tTitle\tArtistId\n1\tFor Those About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE \"Artist\" (\n\t\"ArtistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"ArtistId\")\n)\n\n/*\n3 rows from Artist table:\nArtistId\tName\n1\tAC/DC\n2\tAccept\n3\tAerosmith\n*/\n\n\nCREATE TABLE \"Customer\" (\n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"FirstName\" NVARCHAR(40) NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"Company\" NVARCHAR(80), \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60) NOT NULL, \n\t\"SupportRepId\" INTEGER, \n\tPRIMARY KEY (\"CustomerId\"), \n\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Customer table:\nCustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n*/\n\n\nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/\n\n\nCREATE TABLE \"Genre\" (\n\t\"GenreId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"GenreId\")\n)\n\n/*\n3 rows from Genre table:\nGenreId\tName\n1\tRock\n2\tJazz\n3\tMetal\n*/\n\n\nCREATE TABLE \"Invoice\" (\n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"CustomerId\" INTEGER NOT NULL, \n\t\"InvoiceDate\" DATETIME NOT NULL, \n\t\"BillingAddress\" NVARCHAR(70), \n\t\"BillingCity\" NVARCHAR(40), \n\t\"BillingState\" NVARCHAR(40), \n\t\"BillingCountry\" NVARCHAR(40), \n\t\"BillingPostalCode\" NVARCHAR(10), \n\t\"Total\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"InvoiceId\"), \n\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n)\n\n/*\n3 rows from Invoice table:\nInvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n1\t2\t2021-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2021-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2021-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n*/\n\n\nCREATE TABLE \"InvoiceLine\" (\n\t\"InvoiceLineId\" INTEGER NOT NULL, \n\t\"InvoiceId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\t\"Quantity\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"InvoiceLineId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n)\n\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n1\t1\t2\t0.99\t1\n2\t1\t4\t0.99\t1\n3\t2\t6\t0.99\t1\n*/\n\n\nCREATE TABLE \"MediaType\" (\n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"MediaTypeId\")\n)\n\n/*\n3 rows from MediaType table:\nMediaTypeId\tName\n1\tMPEG audio file\n2\tProtected AAC audio file\n3\tProtected MPEG-4 video file\n*/\n\n\nCREATE TABLE \"Playlist\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(120), \n\tPRIMARY KEY (\"PlaylistId\")\n)\n\n/*\n3 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n3\tTV Shows\n*/\n\n\nCREATE TABLE \"PlaylistTrack\" (\n\t\"PlaylistId\" INTEGER NOT NULL, \n\t\"TrackId\" INTEGER NOT NULL, \n\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n)\n\n/*\n3 rows from PlaylistTrack table:\nPlaylistId\tTrackId\n1\t3402\n1\t3389\n1\t3390\n*/\n\n\nCREATE TABLE \"Track\" (\n\t\"TrackId\" INTEGER NOT NULL, \n\t\"Name\" NVARCHAR(200) NOT NULL, \n\t\"AlbumId\" INTEGER, \n\t\"MediaTypeId\" INTEGER NOT NULL, \n\t\"GenreId\" INTEGER, \n\t\"Composer\" NVARCHAR(220), \n\t\"Milliseconds\" INTEGER NOT NULL, \n\t\"Bytes\" INTEGER, \n\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY (\"TrackId\"), \n\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n)\n\n/*\n3 rows from Track table:\nTrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n2\tBalls to the Wall\t2\t2\t1\tU. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann\t342562\t5510424\t0.99\n3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/\n\nQuestion: What are all the genres of Alanis Morisette songs\nSQLQuery:"
]
}
[llm/end] [chain:RunnableSequence > llm:ChatOpenAI] [1.59s] Exiting LLM run with output:
{
"generations": [
[
{
"text": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;",
"generation_info": {
"finish_reason": "stop",
"logprobs": null
},
"type": "ChatGeneration",
"message": {
"lc": 1,
"type": "constructor",
"id": [
"langchain",
"schema",
"messages",
"AIMessage"
],
"kwargs": {
"content": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;",
"response_metadata": {
"token_usage": {
"completion_tokens": 86,
"prompt_tokens": 2376,
"total_tokens": 2462
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9",
"finish_reason": "stop",
"logprobs": null
},
"type": "ai",
"id": "run-d4b46529-37a3-4561-b693-dfa8965d8a09-0",
"usage_metadata": {
"input_tokens": 2376,
"output_tokens": 86,
"total_tokens": 2462
},
"tool_calls": [],
"invalid_tool_calls": []
}
}
}
]
],
"llm_output": {
"token_usage": {
"completion_tokens": 86,
"prompt_tokens": 2376,
"total_tokens": 2462
},
"model_name": "gpt-35-turbo",
"system_fingerprint": "fp_e49e4201a9"
},
"run": null
}
[chain/start] [chain:RunnableSequence > parser:StrOutputParser] Entering Parser run with input:
[inputs]
[chain/end] [chain:RunnableSequence > parser:StrOutputParser] [2ms] Exiting Parser run with output:
{
"output": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;"
}
[chain/start] [chain:RunnableSequence > chain:_strip] Entering Chain run with input:
{
"input": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;"
}
[chain/end] [chain:RunnableSequence > chain:_strip] [2ms] Exiting Chain run with output:
{
"output": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;"
}
[chain/end] [chain:RunnableSequence] [3.56s] Exiting Chain run with output:
{
"output": "SELECT DISTINCT G.\"Name\"\nFROM \"Genre\" G\nJOIN \"Track\" T ON G.\"GenreId\" = T.\"GenreId\"\nJOIN \"Album\" A ON T.\"AlbumId\" = A.\"AlbumId\"\nJOIN \"Artist\" Ar ON A.\"ArtistId\" = Ar.\"ArtistId\"\nWHERE Ar.\"Name\" = 'Alanis Morissette'\nORDER BY G.\"Name\"\nLIMIT 5;"
}
SELECT DISTINCT G."Name"
FROM "Genre" G
JOIN "Track" T ON G."GenreId" = T."GenreId"
JOIN "Album" A ON T."AlbumId" = A."AlbumId"
JOIN "Artist" Ar ON A."ArtistId" = Ar."ArtistId"
WHERE Ar."Name" = 'Alanis Morissette'
ORDER BY G."Name"
LIMIT 5;
langsmith：https://smith.langchain.com/public/05f006ca-9d84-43e5-baa2-79a948971f24/r 这里是将上面两个chain连接起来了。
# 执行查询
db.run(query)
"[('Rock',)]"
处理表中高基数列#
高基数列其实就是包含了很多专有名词的列，在上一篇文章中说过他的处理方式
比如作者的名字，用户的问题可能是 张国荣的歌有什么？比如我们有一个auths的表，对应的sql应该是 select * from auths where name='张国荣'
上面说的是理想的情况，实际中用户的输入的问题可能是 张国立的歌有什么？ 我们期望生成的sql也应该是上面那样。这种专有名词的列，叫做高基数列。
处理方式：
将专有名字做向量化，在传递给模型之前， 先做一遍向量化，拿到相似的top5的名字，传递给模型来做。
# 构建专有名词
import ast
import re
def query_as_list(db, query):
res = db.run(query)
res = [el for sub in ast.literal_eval(res) for el in sub if el]
res = [re.sub(r"\b\d+\b", "", string).strip() for string in res]
return res
proper_nouns = query_as_list(db, "SELECT Name FROM Artist")
proper_nouns += query_as_list(db, "SELECT Title FROM Album")
proper_nouns += query_as_list(db, "SELECT Name FROM Genre")
len(proper_nouns)
proper_nouns[:5]
['AC/DC', 'Accept', 'Aerosmith', 'Alanis Morissette', 'Alice In Chains']
将上面的词建立向量化数据库
from langchain_community.vectorstores import Chroma
from langchain_openai import OpenAIEmbeddings
## 建立向量化数据库
vector_db = Chroma.from_texts(proper_nouns, OpenAIEmbeddings())
retriever = vector_db.as_retriever(search_kwargs={"k": 15})
将retrieve和chain结合起来
from operator import itemgetter
from langchain_core.prompts import ChatPromptTemplate
from langchain_core.runnables import RunnablePassthrough
from langchain.chains import create_sql_query_chain
from langchain_openai import ChatOpenAI
system = """You are a SQLite expert. Given an input question, create a syntactically
correct SQLite query to run. Unless otherwise specificed, do not return more than
{top_k} rows.
Only return the SQL query with no markup or explanation.
Here is the relevant table info: {table_info}
Here is a non-exhaustive list of possible feature values. If filtering on a feature
value make sure to check its spelling against this list first:
{proper_nouns}
"""
llm = ChatOpenAI(model="gpt-3.5-turbo-0125", temperature=0)
prompt = ChatPromptTemplate.from_messages([("system", system), ("human", "{input}")])
query_chain = create_sql_query_chain(llm, db, prompt=prompt)
retriever_chain = (
itemgetter("question")
| retriever
| (lambda docs: "\n".join(doc.page_content for doc in docs))
)
chain = RunnablePassthrough.assign(proper_nouns=retriever_chain) | query_chain
下面的是传入了一个错误的人名，最终没有查出来
query = query_chain.invoke(
{"question": "What are all the genres of elenis moriset songs", "proper_nouns": ""}
)
print(query)
db.run(query)
SELECT DISTINCT g.Name
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN Album a ON t.AlbumId = a.AlbumId
JOIN Artist ar ON a.ArtistId = ar.ArtistId
WHERE ar.Name = 'Elenis Moriset';
''
下面是结合了retriever_chain的样子，会对专业名词做了一个相似性搜索，会修正错误的单词
# With retrieval
query = chain.invoke({"question": "What are all the genres of eleni moriset songs"})
print(query)
db.run(query)
SELECT DISTINCT g.Name
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN Album a ON t.AlbumId = a.AlbumId
WHERE a.Title LIKE '%Eleni Moriset%';
''
langsmith：https://smith.langchain.com/public/dfac0151-eea6-4a30-8881-f3c0de443772/r 注意看这里的执行过程，这里去做了retrieve
基于csv做问答#
官方文档： https://python.langchain.com/v0.2/docs/how_to/sql_csv/ 推荐的方式是将csv存储在sql中，变为结构化数据方便些，它比使用Python更容易限制权限。 LangChain有处理方式，是让模型返回python代码，我们提供一个python的运行环境，来运行python代码，这种方式在生产里面很不推荐，这里就不过多的介绍了。
从后面开始，LangChain相关的技术了，开始LangGraph的学习