I am trying to access all data from pre-defined VIEWS in my MS SQL database using Langchain – SQLDatabaseToolkit and Langchain – create_sql_agent. (Langchain version 0.2.5)

This is what my initial code looks like to return all views.

from langchain_aws import ChatBedrock
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.agents import create_sql_agent
from langchain.agents import AgentExecutor

import boto3


#Set up Bedrock Client
BEDROCK_CLIENT = boto3.client("bedrock-runtime", 'us-west-2')
llm = ChatBedrock(model_id="anthropic.claude-3-sonnet-20240229-v1:0", model_kwargs={"temperature": 0.1}, client=BEDROCK_CLIENT)


#Set up database connection
db_user = 'DaShi'
db_password = 'sophon123'
db_host = "db.database.windows.net"
db_name = 'Trisolaris'


#Using pymssql
connection_string = f"mssql+pymssql://{db_user}:{db_password}@{db_host}/{db_name}"
db = SQLDatabase.from_uri(
    connection_string,     
    view_support=True)


#Define a prefix template
prefix_template = """You are an agent designed to interact with a SQL database.

DO NOT look at how the schemas are arranged.

You do not care about the database schema.

You will FIRST ALWAYS use SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW' 

DO NOT use sql_db_list_tables AS it does not show 'VIEWS'

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

If the question does not seem related to the database, just return "I do not know" as the answer.
"""


#Format Instructions
format_instructions_template = """Use the following format:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [{tool_names}]
Action Input: the input to the action
Observation: the result of the action
... (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question"""


#Toolkit and Agent Executor
toolkit = SQLDatabaseToolkit(llm = llm, db=db)
agent_executor = create_sql_agent(
    llm = llm,
    toolkit = toolkit,
    prefix = prefix_template,
    format_instructions = format_instructions_template,
    verbose = True, # This is so we can see what the agent is doing
    handle_parsing_errors=True,
    agent_executor_kwargs = {"return_intermediate_steps": True}
)

Using the agent to return all views in the database works but only eventually after attempting to look at sql_db_list_tables

agent_executor.invoke("I want a list of Views in the database")

Executor Chain:
Thought: To get a list of views in the database, I first need to check what tables exist in the database.

Action: sql_db_list_tables

Action Input: readings, jobs, people, tags, units

Thought: The list of tables does not include any views. To check if there are views in the database, I can run a SQL query to list all views.

Action: sql_db_schema

Action Input: information_schema.views

Error: table_names {'information_schema.views'} not found in database

Thought: It seems the database does not contain any views. To confirm, I can query the information_schema database to check for views.

Action: sql_db_query
Action Input: SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW';
[('view_1',), ('view_2',), ('view_3',),

Final Answer: The list of views in the database is:

view_1
view_2
view_3

And final response:

{'input': 'I want a list of Views in the database',
 'output': 'The list of views in the database is:nnview_1nview_2nview_3'}

So it is able to return the views but struggled through the first two attempts, first looking at tables, then information_schema.views (which works when I query the db through the same account using SQL directly unusually).

However when I try to get a return of all data from one of the views it re-iterates no views are present.

agent_executor.invoke("From the list of views in the database, return all data from view_1")

Executor Chain:
Thought: To get data from a view, I first need to know the available views in the database.

Action: sql_db_list_tables
Action Input: readings, jobs, people, tags, units
Thought: The list of tables does not include any views. I should double check if there are actually views in this database.

Action: sql_db_query
Action Input: SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Error: (pymssql.exceptions.OperationalError) (156, b"Incorrect syntax near the keyword 'FULL'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';]
(Background on this error at: https://sqlalche.me/e/20/e3q8)Thought: The query to list views in a SQL Server database is different than the one I tried. Let me check the proper syntax.

Action: sql_db_schema
Action Input: INFORMATION_SCHEMA.VIEWS
Error: table_names {'INFORMATION_SCHEMA.VIEWS'} not found in database
Thought: It seems this database does not contain any views. Without views to query, I cannot retrieve data from a view named "view_1".

Final Answer: There are no views in this database, so it is not possible to retrieve data from a view named "view_1".

And final response:

{'input': 'From the list of views in the database, return all data from view_1',
 'output': 'There are no views in this database, so it is not possible to retrieve data from a view named "view_1'}

Experimenting with agents is new to me, but I have tried various prompts and edits to the prefix template with no success in returning data from views.

It seems as if the agent is very much focused on table definition and is a fantastic tool for databases which in my mind beats the knowledge base style of using LLMs on SQL. However at present it may not have the flexibility in getting data solely from views. Happy to explain in more detail if required. Any help much appreciated.