import gradio as gr from PIL import Image, ImageFilter import os from txt2sql_code3 import SQLPromptModel from qa_bot_chatgpt import QAInfer from gradio import Button import time import google.generativeai as genai image = Image.open(os.path.join(os.path.abspath(''), "house_excel_sheet.png")) def image_display(steps=0): return image query = None rows = None columns = None user_choices = None data = [ [1, "Buckingham Palace", 27, 34, 0.12, "London", "United Kingdom", 601, 920, 105000, "Buckingham_palace.pdf"], [2, "White House", 355, 67, 0.08, "Washington D.C.", "United States", 601, 1527, 80000, "White_House.pdf"], [3, "Taj Mahal Palace", 455, 76, 0.15, "Mumbai", "India", 795, 748, 67000, "Taj_Mahal_palace.pdf"], [4, "Versailles Palace", 455, 45, 0.1, "Versailles", "France", 731, 1800, 145000, "Palace_of_Versailes.pdf"], [5, "Villa Leopolda", 223, 21, 0.05, "Villefranche-sur-Mer", "France", 680, 6886, 65000, "Villa_Leopolda.pdf"], [6, "Antilia", 455, 70, 0.46, "Mumbai", "India", 612, 2520, 179000, "Antilia.pdf"], [7, "The Biltmore Estate", 544, 93, 0.2, "Asheville", "United States", 639, 2040, 50000, "Biltmore_Estate.pdf"], [8, "Hearst Castle", 303, 57, 0.08, "San Simeon", "United States", 731, 1050, 71600, "Hearst_castle.pdf"], [9, "Villa Les Cèdres", 489, 88, 0.09, "Saint-Jean-Cap-Ferrat", "France", 730, 1092, 100000, "Villa_Les_Cedres.pdf"], [10, "Istana Nurul Iman", 350, 34, 7.46, "Bandar Seri Begawan", "Brunei", 670, 5403, 300000, "Istana_Nurul_Iman.pdf"] ] choices = [item[1] for item in data] def execute_sql_query(input_prompt): global query, rows, columns, user_choices model_dir = "multi_table_demo/checkpoint-2600" database = r"sql_pdf.db" sql_model = SQLPromptModel(model_dir, database) user_prompt = "Give complete details of properties in India" for _ in range(3): # Retry logic, try 3 times try: table_schema = sql_model.fetch_table_schema("sql_pdf") if table_schema: if input_prompt.strip(): # query = sql_model.text2sql(table_schema, user_prompt, input_prompt) # query = sql_model.text2sql_chatgpt(table_schema, user_prompt, input_prompt) query = sql_model.text2sql_gemini(table_schema, user_prompt, input_prompt) else: # query = sql_model.text2sql(table_schema, user_prompt, user_prompt) # query = sql_model.text2sql_chatgpt(table_schema, user_prompt, user_prompt) query = sql_model.text2sql_gemini(table_schema, user_prompt, user_prompt) rows, columns = sql_model.execute_query(query) print(rows) user_choices = [] for item in rows: if isinstance(item[0], str): user_choices.append(item[0]) else: for val in item: if isinstance(val, str): user_choices.append(val) break return rows else: print("Table schema not found.") return None except Exception as e: print(f"An error occurred: {e}") print("Retrying...") time.sleep(1) # Wait for 1 second before retrying return None # Return None if all retries fail # def qa_infer_interface(row,query_on_pdf): # qa_infer=QAInfer() # qa_infer.qa_infer(query,rows,columns) user_choices = None def update_choices(nothing): print("callback called") print("choices",choices) print("user_choices",user_choices) if user_choices: examples = [[user_choices[0], "Structure of the property"], [user_choices[0], "Property History"], [user_choices[0], "How many floors does the property have"]] return gr.Dropdown(choices=user_choices, label="Property Choice",info="List of all properties",interactive=True)#,examples else: return gr.Dropdown(choices=[], label="Property Choice",info="List of all properties",interactive=True) def update_examples(nothing): if user_choices: examples = [[user_choices[0], "Structure of the property"], [user_choices[0], "Property History"], [user_choices[0], "How many floors does the property have"]] return examples else : examples=[["","Structure of the property "],[ ""," Property History "] ,["", " How many floors does the property have"]], def qa_infer_interface(property_choice, query_question): qa_infer = QAInfer() if not property_choice and user_choices: property_choice = user_choices[0] print("property_choice",property_choice) print("row",[row for row in data]) property_row = [row for row in data if row[1] == property_choice][0] # Find the row corresponding to the selected property if not query_question: query_question = "area" retries = 3 while retries > 0: try: print(property_row) # answer = qa_infer.qa_infer_interface(property_row, query_question) #-->chatgpt answer = qa_infer.qa_infer_interface_gemini(property_row, query_question) return answer except Exception as e: print(f"Error occurred while inferring QA: {e}") retries -= 1 print("Failed to infer QA after 3 retries.") return None user_dropdown=gr.Dropdown(choices=[], label="Property Choice",info="List of all properties") properties_text=gr.components.Textbox(lines=2,label="User Database Query",placeholder="Click on an query from 'examples' below or write your own query based on the database above. Default : 'Properties in India'") interface_1_output=gr.Json(label="json") stage2_examples=[["","Structure of the property "],[ ""," Property History "] ,["", " How many floors does the property have"]] stage2_text=gr.components.Textbox(lines=2,label="Question on property",placeholder="Enter a question to know more about the properties , you can choose from one of the options below or write a own question Default: 'Area of the property'",) stage2_output="text" with gr.Blocks(title="House Excel Query") as demo: gr.Markdown("# House Excel Query") generated_image = image_display() gr.Image(generated_image) gr.Markdown("""### The database provided contains information about different properties, including their fundamental details. Additional specifics about each property are stored in associated PDF files, which are referenced in the "PDF" column. You have the capability to query this database using various criteria. When a query is initiated, the system generates SQL queries and extracts relevant rows from the database in the backend. \n ### Once the properties are retrieved based on the query, you can utilize the user interface (UI) below to perform question answering (QA). Simply select a property from the list of returned properties and compose a question pertaining to that property. You will receive an answer based on the available information.""") interface_1 = gr.Interface( execute_sql_query, inputs=properties_text, # "textbox", outputs=interface_1_output, cache_examples=False, examples=["Properties in France "," Properties greater than a acre","Properties with more than 400 bedrooms"], ) interface_2 = gr.Interface( qa_infer_interface, inputs=[user_dropdown,stage2_text], # inputs = [gr.Dropdown.change(fn=update_choices),gr.components.Textbox(lines=2,label="Question on property",placeholder="Enter a question to know more about the properties")], outputs=stage2_output, cache_examples=False, ) gr.Examples(["How many floors does the property have "," Total square feet of the property " ," Total area of the property"],inputs=stage2_text,outputs=stage2_output,fn=qa_infer_interface) properties_text.change(update_choices,inputs=[properties_text],outputs=[user_dropdown]) interface_1_output.change(update_choices,inputs=[interface_1_output],outputs=[user_dropdown]) if __name__ == "__main__": demo.launch(share=True) ## download pdf buttons ## upload pdf ## dynamic selection