final.py

# -*- coding: utf-8 -*-
"""Final.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1dpJeWTX6Urby-ciMSyjUWqxPjkBotFpI
"""

# Commented out IPython magic to ensure Python compatibility.
# %pip install --upgrade --quiet langchain-text-splitters tiktoken

# Commented out IPython magic to ensure Python compatibility.
# %pip install -qU langchain-text-splitters

!pip install langchainhub

!pip install -qU faiss_cpu pandas

# Commented out IPython magic to ensure Python compatibility.
# %pip install --upgrade --quiet  tavily-python

# %pip install -qU langchain langchain-openai langchain-community langchain-experimental pandas

import getpass
import requests
import os

os.environ["OPENAI_API_KEY"] = getpass.getpass()

from langchain import hub
from langchain.agents import AgentExecutor, create_openai_tools_agent
from langchain_community.tools.tavily_search import TavilySearchResults
from langchain_openai import ChatOpenAI

!wget https://raw.githubusercontent.com/monaejam/DataSource/main/Main.csv

!wget https://raw.githubusercontent.com/monaejam/DataSource/main/combined_extracted.json

import pandas as pd

lf = pd.read_csv("Main.csv")
print(lf.shape)
print(lf.columns.tolist())

from langchain_community.utilities import SQLDatabase
from sqlalchemy import create_engine, MetaData, Table

engine = create_engine("sqlite:///TrustWhole1.db")
lf.to_sql("MainTable", engine, index=True)
metadata = MetaData()
#main_table = Table('MainTable', metadata, autoload_with=engine)

db = SQLDatabase(engine=engine)

#from sqlalchemy import create_engine, MetaData, Table

# Create an engine instance
#engine = create_engine("sqlite:///TrustWhole1.db")

# Instantiate a MetaData object
#metadata = MetaData()

# Reflect the table from the database
#maindata_table = Table('MainData', metadata, autoload_with=engine)

# Drop the table
#maindata_table.drop(engine)

# Alternatively, if you want to drop the table without reflecting it:
#engine.execute("DROP TABLE IF EXISTS MainData")

print(db.dialect)
print(db.get_usable_table_names())

"""To optimize agent performance, we can provide a custom prompt with domain-specific knowledge. In this case we’ll create a few shot prompt with an example selector, that will dynamically build the few shot prompt based on the user input. This will help the model make better queries by inserting relevant queries in the prompt that the model can use as reference.Now we can create an example selector. This will take the actual user input and select some number of examples to add to our few-shot prompt. We’ll use a SemanticSimilarityExampleSelector, which will perform a semantic search using the embeddings and vector store we configure to find the examples most similar to our input:Now we can create our FewShotPromptTemplate, which takes our example selector, an example prompt for formatting each example, and a string prefix and suffix to put before and after our formatted examples:Since our underlying agent is an OpenAI tools agent, which uses OpenAI function calling, our full prompt should be a chat prompt with a human message template and an agent_scratchpad MessagesPlaceholder. The few-shot prompt will be used for our system message:"""

examples = [
    {"input": "List all companies.", "query": "SELECT Distinct companyName FROM MainTable;"},
    {
        "input": "Find Average Rating for Avant Company'.",
        "query": "SELECT AVG(Rating) as Average_rating FROM MainTable WHERE companyName = 'Avant';",
    },
    {
        "input": "find the average rating over the past 3 months for rise credit company.",
        "query": "SELECT AVG(Rating) AS AverageRating FROM MainTable WHERE companyName = 'Risecredit 'AND `Date of Review` >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);",
    },
     {
        "input": "find the average rating for Avant company based on BBB.",
        "query": "SELECT AVG(Rating) AS AverageRating FROM MainTable  WHERE companyName = 'Risecredit 'AND source ='BBB';",
    },
]

#SemanticSimilarityExampleSelector,
#which will perform a semantic search using the embeddings and vector store configured to find the examples most similar to the input
from langchain_community.vectorstores import FAISS
from langchain_core.example_selectors import SemanticSimilarityExampleSelector
from langchain_openai import OpenAIEmbeddings

example_selector = SemanticSimilarityExampleSelector.from_examples(
    examples,
    OpenAIEmbeddings(),
    FAISS,
    k=5,
    input_keys=["input"],
)

#FewShotPromptTemplate
from langchain_core.prompts import (
    ChatPromptTemplate,
    FewShotPromptTemplate,
    MessagesPlaceholder,
    PromptTemplate,
    SystemMessagePromptTemplate,
    MessagesPlaceholder,
)
from langchain.memory import ConversationBufferMemory


system_prefix = """You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish 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 the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the given tools. Only use the information returned by the tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

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 don't know" as the answer.

Here are some examples of user inputs and their corresponding SQL queries:"""

few_shot_prompt = FewShotPromptTemplate(
    example_selector=example_selector,
    example_prompt=PromptTemplate.from_template(
        "User input: {input}\nSQL query: {query}"
    ),
    input_variables=["input", "dialect", "top_k","history"],
    prefix=system_prefix,
    suffix="",
)
conversational_memory = ConversationBufferMemory(
    memory_key='history',
    return_messages=False
)

#full prompt should be a chat prompt with a human message template and an agent_scratchpad MessagesPlaceholder.
full_prompt = ChatPromptTemplate.from_messages(
    [
        SystemMessagePromptTemplate(prompt=few_shot_prompt),
        ("human", "{input}"),
        MessagesPlaceholder("agent_scratchpad"),

    ]
)

from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import ChatOpenAI
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)


toolkit_1 = SQLDatabaseToolkit(db=db, llm=llm)

agent_executor = create_sql_agent(llm, toolkit=toolkit_1, agent_type="openai-tools", verbose=True,prompt=full_prompt,agent_executor_kwargs={'memory': conversational_memory})

agent_executor.invoke({"input": "find the average rating for Avant company based on BBB.?"})

# Example formatted prompt
#prompt_val = full_prompt.invoke(
    #{
        #"input": "How many companies are there",
        #"top_k": 5,
        #"dialect": "SQLite",
        #"agent_scratchpad": [],
    #}
#)
#print(prompt_val.to_string())

#import json

# Opening JSON file
#g = open('AvantReview.json')

# returns JSON object as
# a dictionary
#data = json.load(g)

from google.colab import drive
drive.mount('/content/drive')

#import json

# Function to extract required fields from a single JSON file
#def extract_required_fields(file_path):
    #with open(file_path, 'r') as file:
        #data = json.load(file)
    #extracted_data = [
        #{'companyName': item['companyName'], 'reviewTitle': item['reviewTitle'], 'reviewDescription': item['reviewDescription']}
        #for item in data
    #]
    #return extracted_data

# Paths to your JSON files
#file_paths = ['/content/drive/My Drive/database/AvantReview.json', '/content/drive/My Drive/database/RiseReview.json', '/content/drive/My Drive/database/OneMainReview.json']

# Extract and combine the data from all files
#combined_data = []
#for path in file_paths:
    #combined_data.extend(extract_required_fields(path))

# Now combined_data contains the merged extracted data
# Write the combined data to a new JSON file
#with open('combined_extracted.json', 'w') as file:
    #json.dump(combined_data, file, indent=4)

# The combined_extracted.json file now contains the merged extracted data from all files

#with open('/content/drive/My Drive/database/combined_extracted.json', 'w') as file:
    #json.dump(combined_data, file, indent=4)

#load json
import requests
import json
!pip install jq

with open('combined_extracted.json', 'r') as file:
    data = json.load(file)

#jq_schema = '.[] | {companyName, reviewTitle, reviewDescription}'

#loader = JSONLoader(
    #file_path='/content/drive/My Drive/database/combined_extracted.json',
    #jq_schema=jq_schema,
    #text_content=False)

#data = loader.load()

#from langchain_community.document_loaders import JSONLoader


#import json
#from pathlib import Path
#from pprint import pprint


#file_path='/content/drive/My Drive/database/combined_extracted.json'
#data = json.loads(Path(file_path).read_text())

data = requests.get("https://raw.githubusercontent.com/monaejam/DataSource/main/combined_extracted.json").json()

#split them
texts_to_split = ""
for item in data:
    texts_to_split += item['companyName'] + "\n" + item['reviewTitle'] + "\n" + item['reviewDescription'] + "\n\n"

texts_to_split

from langchain.text_splitter import CharacterTextSplitter
text_splitter=CharacterTextSplitter(
    separator="\n",
    chunk_size=2500,
    chunk_overlap=100,
)
texts = text_splitter.split_text(texts_to_split)

# how many chain you decide to use QA or conversationalretrievalchain
#from langchain import hub
#from langchain.chains import (
#    StuffDocumentsChain, LLMChain, ConversationalRetrievalChain
#)
from langchain_openai import OpenAIEmbeddings

embeddings = OpenAIEmbeddings(model="text-embedding-ada-002")
vectorstore = FAISS.from_texts(texts, embeddings)

#qa_chain = ConversationalRetrievalChain.from_llm(
#    ChatOpenAI(),
#    vectorstore.as_retriever(search_kwargs={'k': 3}),
#    return_source_documents=True
#)

retriever = vectorstore.as_retriever()

from langchain import hub

prompt = hub.pull("hwchase17/openai-tools-agent")
prompt.messages

from langchain.prompts import ChatPromptTemplate

template = """Answer the question based only on the following context. If you cannot answer the question with the context, please respond with 'I don't know':

Context:
{context}

Question:
{question}
"""

prompt = ChatPromptTemplate.from_template(template)

from langchain.chains import RetrievalQA
from langchain.chat_models import ChatOpenAI
qa = RetrievalQA.from_chain_type(llm=ChatOpenAI(), chain_type="stuff", retriever =vectorstore.as_retriever())

query = "can you list the title of the good reviews you have on OneMain Financial?"
qa.run(query)

from langchain.tools.retriever import create_retriever_tool

tool_retiv = create_retriever_tool(
    retriever,
    name="search_reviews",
    description="search  and returns reviews for the companies along with their title.if there is aggregation operation or if question was about numbers you do not need to use this tool",
)



toolkit_1.get_tools()[0].description="search  and returns reviews for the companies along with their title."

toolkit_1.get_tools()[0]



toolkit_1 = SQLDatabaseToolkit(db=db, llm=llm)

#from langchain.agents import Tool

#tools_re = [
    #Tool(
        #name='search_reviews',
        #func=qa.run,
        #description=(
            #'use this tool when answering general knowledge queries to Searche  and returns reviews for the companies along with their title.'
        #)
    #)
#]
#tools=[tools_re]

[tool_retiv] +

"""Integration

"""

toolkit_1 = SQLDatabaseToolkit(db=db, llm=llm)
tools_final = [tool_retiv] +toolkit_1.get_tools()

from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder

prompt = ChatPromptTemplate.from_messages(
    [
        (
            "system",
            "You are very powerful assistant, but don't know current events",
        ),
        ("user", "{input}"),
        MessagesPlaceholder(variable_name="agent_scratchpad"),
    ]
)

#llm_final=llm.bind_tools(tools_final)

from langchain.agents import AgentExecutor, create_openai_tools_agent

agent = create_openai_tools_agent(llm, tools_final, prompt)

agent_executor = AgentExecutor(agent=agent, tools=tools_final, verbose=True)

agent_executor.invoke({"input": "can you give me some bad reviews for Avant company.?"})

agent_executor.invoke({"input": "find the average rating for Avant company based on BBB.?"})

#!pip install reportlab

#from reportlab.lib.pagesizes import letter
#from reportlab.pdfgen import canvas
#from reportlab.lib.styles import getSampleStyleSheet
#from reportlab.platypus import Paragraph
#from reportlab.lib.units import inch

#def json_to_pdf(data, filename):
#    c = canvas.Canvas(filename, pagesize=letter)
#    styles = getSampleStyleSheet()
#    width, height = letter
#    margin = inch * 0.5
#    y_position = height - margin  # Start position on the page

#    def add_page():
#        """Adds a new page and resets y_position."""
#        nonlocal y_position
#        c.showPage()  # Add a new page
#        y_position = height - margin  # Reset y_position for the new page

#    for item in data:
#        for field in ['companyName', 'reviewTitle', 'reviewDescription']:
#            # Each Paragraph represents a section of text
#            text = f"{field.replace('companyName', 'Company Name').replace('reviewTitle', 'Review Title').replace('reviewDescription', 'Review Description')}: {item[field]}"
#            p = Paragraph(text, styles['Normal'])

            # Wrap the text according to the width of the page
#            w, h = p.wrap(width - 2*margin, y_position)

            # Check if the text block fits in the remaining page space
#            if y_position - h < margin:  # If not enough space, add a new page
#                add_page()

#            p.drawOn(c, margin, y_position - h)
#            y_position -= (h + 0.1 * inch)  # Move to the next block position

            # Check space after drawing each field, add page if next block might not fit
#            if y_position < margin + inch:  # Assume next block needs at least 1 inch
#                add_page()

#    c.save()

# Load your JSON data
#import json

#json_file_path = '/content/drive/My Drive/database/combined_extracted.json'
#with open(json_file_path, 'r') as file:
#    data = json.load(file)

# Generate PDF - Ensure the path is where you want to save on Google Drive
#output_pdf_path = '/content/drive/My Drive/database/output.pdf'
#json_to_pdf(data, output_pdf_path)

#from google.colab import files
#files.download('output.pdf')