# from langchain.agents import create_sql_agent from langchain.agents.agent_toolkits import SQLDatabaseToolkit from langchain.sql_database import SQLDatabase from langchain.llms.openai import OpenAI from langchain import OpenAI, SQLDatabase, SQLDatabaseChain from langchain.prompts.prompt import PromptTemplate # from langchain.agents import AgentExecutor import openai import json import gradio as gr import boto3 import psycopg2 import os import gradio import datetime postgres_connection_str = os.environ['POSTGRES_CONNECTION_STR'] access_key = os.environ['AWS_ACCESS_KEY_ID'] secret_key = os.environ['AWS_SECRET_ACCESS_KEY'] openai_api_key = os.environ['OPENAI_API_KEY'] region= 'us-east-1' db = SQLDatabase.from_uri(postgres_connection_str,schema='langchain_testing') s3_client = boto3.client('s3', aws_access_key_id=access_key, aws_secret_access_key=secret_key,region_name=region) llm = OpenAI(temperature=0, verbose=True, openai_api_key=openai_api_key) toolkit = SQLDatabaseToolkit(db=db, llm=llm) # agent_executor = create_sql_agent( # llm=llm, # toolkit=toolkit # ) # Function to insert the question and response into the table def insert_question_response(question, response): # Generate a hashedid (you can use your preferred method here) # hashedid = hash(question + response) conn = psycopg2.connect(postgres_connection_str) cursor = conn.cursor() user_id = None timestamp = datetime.datetime.now() located_page = None bot_name = 'edai_test_bot1' insert_query = "INSERT INTO bubble.llm_longterm_memory.embedded_chatbot (user_id, timestamp, question, answer, located_page, bot_name) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(insert_query, (user_id, timestamp, question, response, located_page, bot_name)) conn.commit() def generate_response(question,question2): _DEFAULT_TEMPLATE = """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer. Use the following format: Question: "Question here" SQLQuery: "SQL Query to run" SQLResult: "Result of the SQLQuery" Answer: "Final answer here" Only use the following tables: {table_info} If the question relates to real estate or available properties, they mean the properties_us_sites_and_buildings table. To filter between a site and a building, use type field. Questions about property size are referring to the size_value and size_unit fields. Question: {input}""" PROMPT = PromptTemplate( input_variables=["input", "table_info", "dialect"], template=_DEFAULT_TEMPLATE ) db = SQLDatabase.from_uri( postgres_connection_str, schema='langchain_testing', # table_info=table_info, include_tables=[ 'properties_us_sites_and_buildings', 'state_incentives_catalog', 'local_incentives_sources', 'local_incentives_catalog', 'incentives_model_math_summary', 'located_states' ], # custom_table_info=custom_table_info, sample_rows_in_table_info=1 ) # db.table_info print(db.table_info) db_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=True,use_query_checker=True)#, return_intermediate_steps=True) # db_chain.run("What is the Neighborhood Credit Fund in New York City?") # db_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=False, use_query_checker=True)#, return_intermediate_steps=True) # prompt_template = """ # Keep in mind that any site, building, or property related question should be routed to the real estate portal. # Any Local or city or city-sector incentives programs are asking about the local incentives program table. # Any State incentives programs are asking about the state incentives program table. # If you can't find the answer, make sure to look up the program field in the local and state incentives catalogs. # If your final answer is "I don't know", then respond with "Please adjust your question and try asking again." # """ # chain_response = agent_executor.run(question + prompt_template) # # Insert the question and response into the PostgreSQL table # insert_question_response(question, chain_response) # bucket_name = 'edai-analytics' # file_name = 'flagged_text.txt' # s3_client.put_object(Body=input_text, Bucket=bucket_name, Key=file_name) db_chain = SQLDatabaseChain.from_llm(llm, db, prompt=PROMPT, verbose=True)#, return_intermediate_steps=True) return db_chain.run(question) # with gr.Blocks(theme='snehilsanyal/scikit-learn',title='EDai Chatbot') as demo: # with gr.Row(): # # gr.Radio(['Select a question', 'Type your own question'], label='Choose input method:'), # with gr.Column(): # open_text = gr.components.Textbox(label='Type your own question or select an example question provided below.') # translate_btn = gr.components.Button(value="Translate",variant='primary') # # dropdown = gr.components.Dropdown( # # preset_questions_list, # # label='Select a question:') # if open_text: # custrom_results = gr.components.Textbox(label='Response..') # translate_btn.click(generate_response,inputs = [open_text],outputs=[custrom_results]) # demo.launch(favicon_path='edai_logo.jpeg') preset_questions_list = [ 'What is the Neighborhood Credit Fund in New York City?', # 'Which state has the highest bachelor education rate?', 'Which state and region has the lowest income relative to education level and commute time and can you provide statistics?', "What does Georgia's quick start employee training accomplish?", "How many sites and buildings are available in Arlington VA by property type, and which incentives program can I use to save on taxes when starting a tech company?" ] inputs = [gr.components.Textbox(label='Type your own question or use an example question provided.')] iface = gr.Interface( fn=generate_response, title='EDai Chatbot', article="Welcome to the EDai Chatbot! Ask any question related to EDai's Located portal, and the AI analyst will support you. Please note that the information provided is based on available data and may not be exhaustive.", description='Ask questions and get answers from the EDai Analyst!', inputs=inputs, outputs=gr.outputs.Textbox(label="EDai Analyst's Response:"), theme='default', # theme='snehilsanyal/scikit-learn' examples=preset_questions_list ) iface.launch(favicon_path='edai_logo.jpeg')