from langchain import OpenAI, SQLDatabase
from langchain.chat_models import ChatOpenAI
from langchain_experimental.sql import SQLDatabaseChain, SQLDatabaseSequentialChain
import psycopg2
import os
from langchain import PromptTemplate
#Load the .env file
from dotenv import load_dotenv,find_dotenv
load_dotenv(find_dotenv())
# reference https://github.com/bhattbhavesh91/langchain-crashcourse/blob/main/sql-agent-notebook.ipynb
username = "postgres"
password = "postgres"
host = "localhost"
port = "5432"
mydatabase = "adventureworks"
pg_uri = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{mydatabase}"
input_db = SQLDatabase.from_uri(pg_uri)
# llm = ChatOpenAI(temperature=0, openai_api_key=API_KEY, model_name='gpt-3.5-turbo')
chat = ChatOpenAI(model_name="gpt-3.5-turbo",temperature=0.0, openai_api_key=os.getenv("OPENAI_API_KEY"))
db_agent = SQLDatabaseChain(llm = chat,
database = input_db,
verbose=True)
db_agent
temmplate = """You are generating queries and results from Postgres Database. The humanresources schema contains employee table. {question}"""
prompt = PromptTemplate(
input_variables=["question"],
template = temmplate,
)
db_agent(prompt.format(question ="How many employees are there?"))
# Another method using sql chain:
PROMPT = """
Given an input question, first create a syntactically correct postgresql query to run,
then look at the results of the query and return the answer.
The question: {question}
"""
db_chain = SQLDatabaseSequentialChain(llm=llm, database=db_agent, verbose=True, top_k=3)
question = "what is the average rent price in chicago in nov 2022 according to redfin?"
# use db_chain.run(question) instead if you don't have a prompt
db_chain.run(PROMPT.format(question=question))
#https://medium.com/dataherald/how-to-connect-llm-to-sql-database-with-langchain-sqlagent-48635fddaa74
#https://coinsbench.com/chat-with-your-databases-using-langchain-bb7d31ed2e76