QA over structured dataIntegration-specificSQL Database ChainSQL Database ChainThis example demonstrates the use of the SQLDatabaseChain for answering questions over a SQL database.Under the hood, LangChain uses SQLAlchemy to connect to SQL databases. The SQLDatabaseChain can therefore be used with any SQL dialect supported by SQLAlchemy, such as MS SQL, MySQL, MariaDB, PostgreSQL, Oracle SQL, Databricks and SQLite. Please refer to the SQLAlchemy documentation for more information about requirements for connecting to your database. For example, a connection to MySQL requires an appropriate connector such as PyMySQL. A URI for a MySQL connection might look like: mysql+pymysql://user:pass@some_mysql_db_address/db_name.This demonstration uses SQLite and the example Chinook database.
To set it up, follow the instructions on https://database.guide/2-sample-databases-sqlite/, placing the .db file in a notebooks folder at the root of this repository.from langchain.llms import OpenAIfrom langchain.utilities import SQLDatabasefrom langchain_experimental.sql import SQLDatabaseChaindb = SQLDatabase.from_uri("sqlite:///../../../../notebooks/Chinook.db")llm = OpenAI(temperature=0, verbose=True)NOTE: For data-sensitive projects, you can specify return_direct=True in the SQLDatabaseChain initialization to directly return the output of the SQL query without any additional formatting. This prevents the LLM from seeing any contents within the database. Note, however, the LLM still has access to the database scheme (i.e. dialect, table and key names) by default.db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)db_chain.run("How many employees are there?")            > Entering new SQLDatabaseChain chain...    How many employees are there?    SQLQuery:    /workspace/langchain/langchain/sql_database.py:191: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.      sample_rows = connection.execute(command)    SELECT COUNT(*) FROM "Employee";    SQLResult: [(8,)]    Answer:There are 8 employees.    > Finished chain.    'There are 8 employees.'Use Query Checker​Sometimes the Language Model generates invalid SQL with small mistakes that can be self-corrected using the same technique used by the SQL Database Agent to try and fix the SQL using the LLM. You can simply specify this option when creating the chain:db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, use_query_checker=True)db_chain.run("How many albums by Aerosmith?")            > Entering new SQLDatabaseChain chain...    How many albums by Aerosmith?    SQLQuery:SELECT COUNT(*) FROM Album WHERE ArtistId = 3;    SQLResult: [(1,)]    Answer:There is 1 album by Aerosmith.    > Finished chain.    'There is 1 album by Aerosmith.'Customize Prompt​You can also customize the prompt that is used. Here is an example prompting it to understand that foobar is the same as the Employee tablefrom langchain.prompts.prompt import PromptTemplate_DEFAULT_TEMPLATE = """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.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}If someone asks for the table foobar, they really mean the employee table.Question: {input}"""PROMPT = PromptTemplate(    input_variables=["input", "table_info", "dialect"], template=_DEFAULT_TEMPLATE)db_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=True)db_chain.run("How many employees are there in the foobar table?")            > Entering new SQLDatabaseChain chain...    How many employees are there in the foobar table?    SQLQuery:SELECT COUNT(*) FROM Employee;    SQLResult: [(8,)]    Answer:There are 8 employees in the foobar table.    > Finished chain.    'There are 8 employees in the foobar table.'Return Intermediate Steps​You can also return the intermediate steps of the SQLDatabaseChain. This allows you to access the SQL statement that was generated, as well as the result of running that against the SQL Database.db_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=True, use_query_checker=True, return_intermediate_steps=True)result = db_chain("How many employees are there in the foobar table?")result["intermediate_steps"]            > Entering new SQLDatabaseChain chain...    How many employees are there in the foobar table?    SQLQuery:SELECT COUNT(*) FROM Employee;    SQLResult: [(8,)]    Answer:There are 8 employees in the foobar table.    > Finished chain.    [{'input': 'How many employees are there in the foobar table?\nSQLQuery:SELECT COUNT(*) FROM Employee;\nSQLResult: [(8,)]\nAnswer:',      'top_k': '5',      'dialect': 'sqlite',      'table_info': '\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 "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 "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 "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 "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 "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\t2009-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n2\t4\t2009-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n3\t8\t2009-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\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\tNone\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\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 "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*/',      'stop': ['\nSQLResult:']},     'SELECT COUNT(*) FROM Employee;',     {'query': 'SELECT COUNT(*) FROM Employee;', 'dialect': 'sqlite'},     'SELECT COUNT(*) FROM Employee;',     '[(8,)]']Adding Memory​How to add memory to a SQLDatabaseChain:from langchain.llms import OpenAIfrom langchain.utilities import SQLDatabasefrom langchain_experimental.sql import SQLDatabaseChainSet up the SQLDatabase and LLMdb = SQLDatabase.from_uri("sqlite:///../../../../notebooks/Chinook.db")llm = OpenAI(temperature=0, verbose=True)Set up the memoryfrom langchain.memory import ConversationBufferMemorymemory = ConversationBufferMemory()Now we need to add a place for memory in the prompt templatefrom langchain.prompts import PromptTemplatePROMPT_SUFFIX = """Only use the following tables:{table_info}Previous Conversation:{history}Question: {input}"""_DEFAULT_TEMPLATE = """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer. Unless the user specifies in his question a specific number of examples he wishes to obtain, always limit your query to at most {top_k} results. You can order the results by a relevant column to return the most interesting examples in the database.Never query for all the columns from a specific table, only ask for a the few relevant columns given the question.Pay attention to use only the column names that you can see in the schema description. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.Use the following format:Question: Question hereSQLQuery: SQL Query to runSQLResult: Result of the SQLQueryAnswer: Final answer here"""PROMPT = PromptTemplate.from_template(    _DEFAULT_TEMPLATE + PROMPT_SUFFIX,)Now let's create and run out chaindb_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=True, memory=memory)db_chain.run("name one employee")    > Entering new SQLDatabaseChain chain...    name one employee    SQLQuery:SELECT FirstName, LastName FROM Employee LIMIT 1    SQLResult: [('Andrew', 'Adams')]    Answer:Andrew Adams    > Finished chain.    'Andrew Adams'db_chain.run("how many letters in their name?")    > Entering new SQLDatabaseChain chain...    how many letters in their name?    SQLQuery:SELECT LENGTH(FirstName) + LENGTH(LastName) AS 'NameLength' FROM Employee WHERE FirstName = 'Andrew' AND LastName = 'Adams'    SQLResult: [(11,)]    Answer:Andrew Adams has 11 letters in their name.    > Finished chain.    'Andrew Adams has 11 letters in their name.'Choosing how to limit the number of rows returned​If you are querying for several rows of a table you can select the maximum number of results you want to get by using the 'top_k' parameter (default is 10). This is useful for avoiding query results that exceed the prompt max length or consume tokens unnecessarily.db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, use_query_checker=True, top_k=3)db_chain.run("What are some example tracks by composer Johann Sebastian Bach?")            > Entering new SQLDatabaseChain chain...    What are some example tracks by composer Johann Sebastian Bach?    SQLQuery:SELECT Name FROM Track WHERE Composer = 'Johann Sebastian Bach' LIMIT 3    SQLResult: [('Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace',), ('Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria',), ('Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude',)]    Answer:Examples of tracks by Johann Sebastian Bach are Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace, Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria, and Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude.    > Finished chain.    'Examples of tracks by Johann Sebastian Bach are Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace, Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria, and Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude.'Adding example rows from each table​Sometimes, the format of the data is not obvious and it is optimal to include a sample of rows from the tables in the prompt to allow the LLM to understand the data before providing a final query. Here we will use this feature to let the LLM know that artists are saved with their full names by providing two rows from the Track table.db = SQLDatabase.from_uri(    "sqlite:///../../../../notebooks/Chinook.db",    include_tables=['Track'], # we include only one table to save tokens in the prompt :)    sample_rows_in_table_info=2)The sample rows are added to the prompt after each corresponding table's column information:print(db.table_info)        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")    )        /*    2 rows from Track table:    TrackId Name    AlbumId MediaTypeId GenreId Composer    Milliseconds    Bytes   UnitPrice    1   For Those About To Rock (We Salute You) 1   1   1   Angus Young, Malcolm Young, Brian Johnson   343719  11170334    0.99    2   Balls to the Wall   2   2   1   None    342562  5510424 0.99    */db_chain = SQLDatabaseChain.from_llm(llm, db, use_query_checker=True, verbose=True)db_chain.run("What are some example tracks by Bach?")            > Entering new SQLDatabaseChain chain...    What are some example tracks by Bach?    SQLQuery:SELECT "Name", "Composer" FROM "Track" WHERE "Composer" LIKE '%Bach%' LIMIT 5    SQLResult: [('American Woman', 'B. Cummings/G. Peterson/M.J. Kale/R. Bachman'), ('Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace', 'Johann Sebastian Bach'), ('Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria', 'Johann Sebastian Bach'), ('Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude', 'Johann Sebastian Bach'), ('Toccata and Fugue in D Minor, BWV 565: I. Toccata', 'Johann Sebastian Bach')]    Answer:Tracks by Bach include 'American Woman', 'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace', 'Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria', 'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude', and 'Toccata and Fugue in D Minor, BWV 565: I. Toccata'.    > Finished chain.    'Tracks by Bach include \'American Woman\', \'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace\', \'Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria\', \'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude\', and \'Toccata and Fugue in D Minor, BWV 565: I. Toccata\'.'Custom Table Info​In some cases, it can be useful to provide custom table information instead of using the automatically generated table definitions and the first sample_rows_in_table_info sample rows. For example, if you know that the first few rows of a table are uninformative, it could help to manually provide example rows that are more diverse or provide more information to the model. It is also possible to limit the columns that will be visible to the model if there are unnecessary columns. This information can be provided as a dictionary with table names as the keys and table information as the values. For example, let's provide a custom definition and sample rows for the Track table with only a few columns:custom_table_info = {    "Track": """CREATE TABLE Track (    "TrackId" INTEGER NOT NULL,     "Name" NVARCHAR(200) NOT NULL,    "Composer" NVARCHAR(220),    PRIMARY KEY ("TrackId"))/*3 rows from Track table:TrackId Name    Composer1   For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson2   Balls to the Wall   None3   My favorite song ever   The coolest composer of all time*/"""}db = SQLDatabase.from_uri(    "sqlite:///../../../../notebooks/Chinook.db",    include_tables=['Track', 'Playlist'],    sample_rows_in_table_info=2,    custom_table_info=custom_table_info)print(db.table_info)        CREATE TABLE "Playlist" (        "PlaylistId" INTEGER NOT NULL,         "Name" NVARCHAR(120),         PRIMARY KEY ("PlaylistId")    )        /*    2 rows from Playlist table:    PlaylistId  Name    1   Music    2   Movies    */        CREATE TABLE Track (        "TrackId" INTEGER NOT NULL,         "Name" NVARCHAR(200) NOT NULL,        "Composer" NVARCHAR(220),        PRIMARY KEY ("TrackId")    )    /*    3 rows from Track table:    TrackId Name    Composer    1   For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson    2   Balls to the Wall   None    3   My favorite song ever   The coolest composer of all time    */Note how our custom table definition and sample rows for Track overrides the sample_rows_in_table_info parameter. Tables that are not overridden by custom_table_info, in this example Playlist, will have their table info gathered automatically as usual.db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)db_chain.run("What are some example tracks by Bach?")            > Entering new SQLDatabaseChain chain...    What are some example tracks by Bach?    SQLQuery:SELECT "Name" FROM Track WHERE "Composer" LIKE '%Bach%' LIMIT 5;    SQLResult: [('American Woman',), ('Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace',), ('Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria',), ('Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude',), ('Toccata and Fugue in D Minor, BWV 565: I. Toccata',)]    Answer:text='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.\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 "Playlist" (\n\t"PlaylistId" INTEGER NOT NULL, \n\t"Name" NVARCHAR(120), \n\tPRIMARY KEY ("PlaylistId")\n)\n\n/*\n2 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n*/\n\nCREATE TABLE Track (\n\t"TrackId" INTEGER NOT NULL, \n\t"Name" NVARCHAR(200) NOT NULL,\n\t"Composer" NVARCHAR(220),\n\tPRIMARY KEY ("TrackId")\n)\n/*\n3 rows from Track table:\nTrackId\tName\tComposer\n1\tFor Those About To Rock (We Salute You)\tAngus Young, Malcolm Young, Brian Johnson\n2\tBalls to the Wall\tNone\n3\tMy favorite song ever\tThe coolest composer of all time\n*/\n\nQuestion: What are some example tracks by Bach?\nSQLQuery:SELECT "Name" FROM Track WHERE "Composer" LIKE \'%Bach%\' LIMIT 5;\nSQLResult: [(\'American Woman\',), (\'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace\',), (\'Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria\',), (\'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude\',), (\'Toccata and Fugue in D Minor, BWV 565: I. Toccata\',)]\nAnswer:'    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.        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 "Playlist" (        "PlaylistId" INTEGER NOT NULL,         "Name" NVARCHAR(120),         PRIMARY KEY ("PlaylistId")    )        /*    2 rows from Playlist table:    PlaylistId  Name    1   Music    2   Movies    */        CREATE TABLE Track (        "TrackId" INTEGER NOT NULL,         "Name" NVARCHAR(200) NOT NULL,        "Composer" NVARCHAR(220),        PRIMARY KEY ("TrackId")    )    /*    3 rows from Track table:    TrackId Name    Composer    1   For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson    2   Balls to the Wall   None    3   My favorite song ever   The coolest composer of all time    */        Question: What are some example tracks by Bach?    SQLQuery:SELECT "Name" FROM Track WHERE "Composer" LIKE '%Bach%' LIMIT 5;    SQLResult: [('American Woman',), ('Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace',), ('Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria',), ('Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude',), ('Toccata and Fugue in D Minor, BWV 565: I. Toccata',)]    Answer:    {'input': 'What are some example tracks by Bach?\nSQLQuery:SELECT "Name" FROM Track WHERE "Composer" LIKE \'%Bach%\' LIMIT 5;\nSQLResult: [(\'American Woman\',), (\'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace\',), (\'Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria\',), (\'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude\',), (\'Toccata and Fugue in D Minor, BWV 565: I. Toccata\',)]\nAnswer:', 'top_k': '5', 'dialect': 'sqlite', 'table_info': '\nCREATE TABLE "Playlist" (\n\t"PlaylistId" INTEGER NOT NULL, \n\t"Name" NVARCHAR(120), \n\tPRIMARY KEY ("PlaylistId")\n)\n\n/*\n2 rows from Playlist table:\nPlaylistId\tName\n1\tMusic\n2\tMovies\n*/\n\nCREATE TABLE Track (\n\t"TrackId" INTEGER NOT NULL, \n\t"Name" NVARCHAR(200) NOT NULL,\n\t"Composer" NVARCHAR(220),\n\tPRIMARY KEY ("TrackId")\n)\n/*\n3 rows from Track table:\nTrackId\tName\tComposer\n1\tFor Those About To Rock (We Salute You)\tAngus Young, Malcolm Young, Brian Johnson\n2\tBalls to the Wall\tNone\n3\tMy favorite song ever\tThe coolest composer of all time\n*/', 'stop': ['\nSQLResult:']}    Examples of tracks by Bach include "American Woman", "Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace", "Aria Mit 30 Veränderungen, BWV 988 'Goldberg Variations': Aria", "Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude", and "Toccata and Fugue in D Minor, BWV 565: I. Toccata".    > Finished chain.    'Examples of tracks by Bach include "American Woman", "Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace", "Aria Mit 30 Veränderungen, BWV 988 \'Goldberg Variations\': Aria", "Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude", and "Toccata and Fugue in D Minor, BWV 565: I. Toccata".'SQL Views​In some case, the table schema can be hidden behind a JSON or JSONB column. Adding row samples into the prompt might help won't always describe the data perfectly. For this reason, a custom SQL views can help.CREATE VIEW accounts_v AS    select id, firstname, lastname, email, created_at, updated_at,        cast(stats->>'total_post' as int) as total_post,        cast(stats->>'total_comments' as int) as total_comments,        cast(stats->>'ltv' as int) as ltv        FROM accounts;Then limit the tables visible from SQLDatabase to the created view.db = SQLDatabase.from_uri(    "sqlite:///../../../../notebooks/Chinook.db",    include_tables=['accounts_v']) # we include only the viewSQLDatabaseSequentialChain​Chain for querying SQL database that is a sequential chain.The chain is as follows:1. Based on the query, determine which tables to use.2. Based on those tables, call the normal SQL database chain.This is useful in cases where the number of tables in the database is large.from langchain_experimental.sql import SQLDatabaseSequentialChaindb = SQLDatabase.from_uri("sqlite:///../../../../notebooks/Chinook.db")chain = SQLDatabaseSequentialChain.from_llm(llm, db, verbose=True)chain.run("How many employees are also customers?")            > Entering new SQLDatabaseSequentialChain chain...    Table names to use:    ['Employee', 'Customer']        > Entering new SQLDatabaseChain chain...    How many employees are also customers?    SQLQuery:SELECT COUNT(*) FROM Employee e INNER JOIN Customer c ON e.EmployeeId = c.SupportRepId;    SQLResult: [(59,)]    Answer:59 employees are also customers.    > Finished chain.        > Finished chain.    '59 employees are also customers.'Using Local Language Models​Sometimes you may not have the luxury of using OpenAI or other service-hosted large language model. You can, ofcourse, try to use the SQLDatabaseChain with a local model, but will quickly realize that most models you can run locally even with a large GPU struggle to generate the right output.import loggingimport torchfrom transformers import AutoTokenizer, GPT2TokenizerFast, pipeline, AutoModelForSeq2SeqLM, AutoModelForCausalLMfrom langchain.llms import HuggingFacePipeline# Note: This model requires a large GPU, e.g. an 80GB A100. See documentation for other ways to run private non-OpenAI models.model_id = "google/flan-ul2"model = AutoModelForSeq2SeqLM.from_pretrained(model_id, temperature=0)device_id = -1  # default to no-GPU, but use GPU and half precision mode if availableif torch.cuda.is_available():    device_id = 0    try:        model = model.half()    except RuntimeError as exc:        logging.warn(f"Could not run model in half precision mode: {str(exc)}")tokenizer = AutoTokenizer.from_pretrained(model_id)pipe = pipeline(task="text2text-generation", model=model, tokenizer=tokenizer, max_length=1024, device=device_id)local_llm = HuggingFacePipeline(pipeline=pipe)    /workspace/langchain/.venv/lib/python3.9/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html      from .autonotebook import tqdm as notebook_tqdm    Loading checkpoint shards: 100%|██████████| 8/8 [00:32<00:00,  4.11s/it]from langchain.utilities import SQLDatabasefrom langchain_experimental.sql import SQLDatabaseChaindb = SQLDatabase.from_uri("sqlite:///../../../../notebooks/Chinook.db", include_tables=['Customer'])local_chain = SQLDatabaseChain.from_llm(local_llm, db, verbose=True, return_intermediate_steps=True, use_query_checker=True)This model should work for very simple SQL queries, as long as you use the query checker as specified above, e.g.:local_chain("How many customers are there?")            > Entering new SQLDatabaseChain chain...    How many customers are there?    SQLQuery:    /workspace/langchain/.venv/lib/python3.9/site-packages/transformers/pipelines/base.py:1070: UserWarning: You seem to be using the pipelines sequentially on GPU. In order to maximize efficiency please use a dataset      warnings.warn(    /workspace/langchain/.venv/lib/python3.9/site-packages/transformers/pipelines/base.py:1070: UserWarning: You seem to be using the pipelines sequentially on GPU. In order to maximize efficiency please use a dataset      warnings.warn(    SELECT count(*) FROM Customer    SQLResult: [(59,)]    Answer:    /workspace/langchain/.venv/lib/python3.9/site-packages/transformers/pipelines/base.py:1070: UserWarning: You seem to be using the pipelines sequentially on GPU. In order to maximize efficiency please use a dataset      warnings.warn(    [59]    > Finished chain.    {'query': 'How many customers are there?',     'result': '[59]',     'intermediate_steps': [{'input': 'How many customers are there?\nSQLQuery:SELECT count(*) FROM Customer\nSQLResult: [(59,)]\nAnswer:',       'top_k': '5',       'dialect': 'sqlite',       'table_info': '\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