i am working on SQL Langchain to ask natural english questions and get answers from postgress sql but not getting any answers

[BalajiPolisetty2207]

Discussed in https://github.com/langchain-ai/langchain/discussions/15928

^{Originally posted by **BalajiPolisetty2207** January 11, 2024} i am using this langchain to query my postgres sql with natural language. with sql server i am successful in asking and getting answers . I am using azure open ai key and endpoint and gpt 4 model and deployment . Can some one help what is missing here to query postgressql and how i can set the schema before querying. Below is the code # Importing necessary libraries from sqlalchemy import create_engine, text from langchain.chat_models import AzureChatOpenAI from langchain.prompts.chat import ChatPromptTemplate from langchain.agents import AgentType, create_sql_agent from langchain.sql_database import SQLDatabase from langchain.agents.agent_toolkits.sql.toolkit import SQLDatabaseToolkit import os db_url = 'postgresql://asdas:e?Yasdad@asdasd-qwewqe.qwewqe.database.azure.com:5432/asdad' # PostgreSQL connection openai_api_key = 'asdsadad' # Azure OpenAI key openai_model = 'eqwewqe-4' # OpenAI model, like GPT-4 openai_deployment = 'eqwewqe' # OpenAI deployment name os.environ["OPENAI_API_KEY"] = 'xxxx' os.environ["OPENAI_API_BASE"]="https://asdasd.qweqw.azure.com/" os.environ["OPENAI_API_TYPE"]="azure" os.environ["OPENAI_API_VERSION"]="2023-07-01-preview" # Creating the database engine engine = create_engine(db_url) # Setting up the Azure OpenAI for LangChain llm = AzureChatOpenAI( model=openai_model, deployment_name=openai_deployment, temperature=0 ) # Setting up the prompt template final_prompt = ChatPromptTemplate.from_messages( [ ("system", """ You are an AI assistant expert in querying the PostgreSQL database. you are going to respond the question on the students schema. """ ), ("user", "{question}\n ai: "), ] ) # Creating the SQL Database object for LangChain db = SQLDatabase(engine) # Setting up the SQL Toolkit for LangChain sql_toolkit = SQLDatabaseToolkit(db=db, llm=llm) sql_toolkit.get_tools() # Creating the SQL Agent for LangChain sqldb_agent = create_sql_agent( llm=llm, toolkit=sql_toolkit, agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION, verbose=True, handle_parsing_errors=True, ) # Example usage of the agent to answer a question question = "get me list of tables in the students schema ?" final_prompt.messages[-1] = ("user", f"{question}\n ai: ") response = sqldb_agent.run(final_prompt) print(response)

[dosubot[bot]]

🤖

Hello @BalajiPolisetty2207! I'm Dosu, a friendly bot here to assist you while we wait for a human maintainer. I can help troubleshoot issues, answer your queries, and guide you on your journey as a contributor. Don't worry, we'll work through this challenge together!

Based on the code you've shared, it seems like you've set up the connection to your PostgreSQL database correctly. However, there could be a few reasons why you're not getting any responses.

Firstly, ensure that your PostgreSQL database is accessible and that the connection string is correct. The connection string should be in the format: postgresql://user:password@localhost/dbname. Replace user, password, localhost, and dbname with your actual PostgreSQL credentials.

Secondly, check if the schema you're trying to query exists in your PostgreSQL database. In your code, you're trying to query the students schema. If this schema doesn't exist, you won't get any responses.

To set the schema before querying, you can pass the schema name as an argument when creating an instance of the SQLDatabase class. Here's how you can do it:

db = SQLDatabase(engine, schema="your_schema")

Replace "your_schema" with the name of your schema.

Lastly, ensure that the Azure OpenAI key, model, and deployment are correctly set up. If there's an issue with the Azure OpenAI setup, it could prevent LangChain from querying your PostgreSQL database.

If you're still facing issues, could you please provide more information about the error messages you're getting? This would help in diagnosing the problem more accurately.

For more information, you can refer to the LangChain documentation and the source code:

• SQLDatabase class
• AzureChatOpenAI class
• create_sql_agent function

I hope this helps! Let me know if you have any other questions.

Sources

• libs/langchain/tests/unit_tests/test_sql_database_schema.py
• libs/community/langchain_community/utilities/sql_database.py

---

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[BalajiPolisetty2207]

its working now.. thanks .