from example import example from datetime import datetime import pandas as pd # agent will directly create query and run the query in DB from langchain.agents import create_sql_agent # Simple chain to create the SQL statements, it doesn't execute the query from langchain.chains import create_sql_query_chain # to execute the query from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool from langchain_community.agent_toolkits import SQLDatabaseToolkit from langchain.sql_database import SQLDatabase from langchain.agents import AgentExecutor from langchain.agents.agent_types import AgentType from langchain_experimental.sql import SQLDatabaseChain from langchain_community.vectorstores import Chroma from langchain.prompts import SemanticSimilarityExampleSelector # Prompt input for MYSQL from langchain.chains.sql_database.prompt import PROMPT_SUFFIX, _mysql_prompt # Create the prompt template for creating the prompt for mysqlprompt from langchain.prompts.prompt import PromptTemplate from langchain.prompts import FewShotPromptTemplate # to create the tools to be used by agent from langchain.agents import Tool # create the agent prompts from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder # Huggingface embeddings using Langchain from langchain_community.embeddings import HuggingFaceEmbeddings from langchain_core.messages import HumanMessage, SystemMessage from langchain_core.prompts import ChatPromptTemplate from langchain_core.prompts import HumanMessagePromptTemplate from langchain_core.output_parsers import StrOutputParser # Load Env parameters from dotenv import load_dotenv from langchain_openai import ChatOpenAI from sqlalchemy import create_engine, text, URL def config(): load_dotenv() # load env parameters llm = ChatOpenAI(temperature=0.5, model="gpt-3.5-turbo") # create LLM #llm = OpenAI(temperature=0.5) # create LLM return llm # Setting up URL parameter to connect to MySQL Database def get_db_chain(question): db_user="root" db_password="root" db_host="localhost" db_name="retail" # create LLM llm = ChatOpenAI(temperature=0.5, model="gpt-3.5-turbo") # Initialize SQL DB using Langchain db = SQLDatabase.from_uri(f"mysql://{db_user}:{db_password}@{db_host}/{db_name}") toolkit = SQLDatabaseToolkit(db=db, llm=llm) embeddings = HuggingFaceEmbeddings(model_name='sentence-transformers/all-MiniLM-L6-v2') # create the list with only values and ready to be vectorized to_vectorize = [" ".join(example.values()) for example in example] # use join to convert Dict to list # Setup the Chroma database and vectorize vectorstore = Chroma.from_texts(to_vectorize, embedding=embeddings, metadatas=example) # Based on the user question, convert them to vector and take the similar looking vectors from Chroma DB example_selector = SemanticSimilarityExampleSelector( vectorstore = vectorstore, k=2) example_prompt = PromptTemplate( input_variables=["Question", "SQLQuery", "SQLResult", "Answer",], template="\nQuestion: {Question}\nSQLQuery: {SQLQuery}\nSQLResult: {SQLResult}\n?Answer: {Answer}",) few_shot_prompt = FewShotPromptTemplate( example_selector=example_selector, # Hey LLM, if you dont know refer the examples giving in vector DB example_prompt=example_prompt, # This is the Prompt template we have created prefix=_mysql_prompt, # This is prefix of the prompt suffix=PROMPT_SUFFIX, # This is suffix of the prompt input_variables=["input", "table_info", "top_k"], # variables used in forming the prompt to LLM ) chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, prompt=few_shot_prompt) response = chain.invoke(question) return response # Call the LLM with the question and the fewshotprompt # write_query = create_sql_query_chain(llm=llm,db=db, prompt=few_shot_prompt) #print(write_query) # Execute the Query using QuerySQLDataBaseTool #execute_query = QuerySQLDataBaseTool(db=db) # Chain to combine write SQL and Execute SQL #chain = write_query | execute_query | llm #response = chain.invoke("Question") def get_store_address(store): url_object = URL.create( "mysql", username="root", password="root", # plain (unescaped) text host="localhost", database="retail", ) engine = create_engine(url_object) #connect to engine connection = engine.connect() sql_query = "SELECT STORE_NUMBER, STORE_ADDRESS FROM STORES WHERE STORE_NUMBER = " + store df = pd.read_sql(sql_query, con=engine) response = df.to_string() return response def outreach_sms_message(outreach_input): # create LLM llm = ChatOpenAI(temperature=0.5, model="gpt-3.5-turbo", verbose=True) prompt = ChatPromptTemplate.from_template("You are a expert in writing a text message for appointment setup with less than 35 words." "With {outreach_input}, generate a text message for appointment to be sent to customer") output_parser = StrOutputParser() chain = prompt | llm | output_parser response = chain.invoke({"outreach_input": outreach_input}) return response #if __name__ == "__main__": # chain = get_db_chain() # print(chain.run("List of all sales transactions for Trevor Nelson in June 2020")) # Setting up URL parameter to connect to MySQL Database