| | import ast
|
| | import gradio as gr
|
| | from functions import sql_example_question_generator, sql_chatbot_with_fc
|
| | from data_sources import connect_sql_db
|
| | from utils import message_dict
|
| |
|
| | def hide_info():
|
| | return gr.update(visible=False)
|
| |
|
| | with gr.Blocks() as demo:
|
| | description = gr.HTML("""
|
| | <!-- Header -->
|
| | <div class="max-w-4xl mx-auto mb-12 text-center">
|
| | <div class="bg-blue-50 border border-blue-200 rounded-lg max-w-2xl mx-auto">
|
| | <p>This tool allows users to communicate with and query real time data from a SQL DB (postgres for now, others can be added if requested) using natural
|
| | language and the above features.</p>
|
| | <p style="font-weight:bold;">Notice: the way this system is designed, no login information is retained and credentials are passed as session variables until the user leaves or
|
| | refreshes the page in which they disappear. They are never saved to any files. I also make use of the Pandas read_sql_query function to apply SQL
|
| | queries, which can't delete, drop, or add database lines to avoid unhappy accidents or glitches.
|
| | That being said, it's probably not a good idea to connect a production database to a strange AI tool with an unfamiliar author.
|
| | This should be for demonstration purposes.</p>
|
| | <p>Contact me if this is something you would like built in your organization, on your infrastructure, and with the requisite privacy and control a production
|
| | database analytics tool requires.</p>
|
| | </div>
|
| | </div>
|
| | """, elem_classes="description_component")
|
| | sql_url = gr.Textbox(label="URL", value="virtual-data-analyst-pg.cyetm2yjzppu.us-west-1.rds.amazonaws.com")
|
| | with gr.Row():
|
| | sql_port = gr.Textbox(label="Port", value="5432")
|
| | sql_user = gr.Textbox(label="Username", value="postgres")
|
| | sql_pass = gr.Textbox(label="Password", value="Vda-1988", type="password")
|
| | sql_db_name = gr.Textbox(label="Database Name", value="dvdrental")
|
| |
|
| | submit = gr.Button(value="Submit")
|
| | submit.click(fn=hide_info, outputs=description)
|
| |
|
| | @gr.render(inputs=[sql_url,sql_port,sql_user,sql_pass,sql_db_name], triggers=[submit.click])
|
| | def sql_chat(request: gr.Request, url=sql_url.value, sql_port=sql_port.value, sql_user=sql_user.value, sql_pass=sql_pass.value, sql_db_name=sql_db_name.value):
|
| | if request.session_hash not in message_dict:
|
| | message_dict[request.session_hash] = {}
|
| | message_dict[request.session_hash]['sql'] = None
|
| | if url:
|
| | print("SQL APP")
|
| | process_message = process_sql_db(url, sql_user, sql_port, sql_pass, sql_db_name, request.session_hash)
|
| | gr.HTML(value=process_message[1], padding=False)
|
| | if process_message[0] == "success":
|
| | if "virtual-data-analyst-pg.cyetm2yjzppu.us-west-1.rds.amazonaws.com" in url:
|
| | example_questions = [
|
| | ["Describe the dataset"],
|
| | ["What is the total revenue generated by each store?"],
|
| | ["Can you generate and display a bar chart of film category to number of films in that category?"],
|
| | ["Can you generate a pie chart showing the top 10 most rented films by revenue vs all other films?"],
|
| | ["Can you generate a line chart of rental revenue over time?"],
|
| | ["What is the relationship between film length and rental frequency?"]
|
| | ]
|
| | else:
|
| | try:
|
| | generated_examples = ast.literal_eval(sql_example_question_generator(request.session_hash, process_message[2], sql_db_name))
|
| | example_questions = [
|
| | ["Describe the dataset"]
|
| | ]
|
| | for example in generated_examples:
|
| | example_questions.append([example])
|
| | except Exception as e:
|
| | print("SQL QUESTION GENERATION ERROR")
|
| | print(e)
|
| | example_questions = [
|
| | ["Describe the dataset"],
|
| | ["List the columns in the dataset"],
|
| | ["What could this data be used for?"],
|
| | ]
|
| | session_hash = gr.Textbox(visible=False, value=request.session_hash)
|
| | db_url = gr.Textbox(visible=False, value=url)
|
| | db_port = gr.Textbox(visible=False, value=sql_port)
|
| | db_user = gr.Textbox(visible=False, value=sql_user)
|
| | db_pass = gr.Textbox(visible=False, value=sql_pass)
|
| | db_name = gr.Textbox(visible=False, value=sql_db_name)
|
| | db_tables = gr.Textbox(value=process_message[2], interactive=False, label="SQL Tables")
|
| | bot = gr.Chatbot(type='messages', label="CSV Chat Window", render_markdown=True, sanitize_html=False, show_label=True, render=False, visible=True, elem_classes="chatbot")
|
| | chat = gr.ChatInterface(
|
| | fn=sql_chatbot_with_fc,
|
| | type='messages',
|
| | chatbot=bot,
|
| | title="Chat with your Database",
|
| | examples=example_questions,
|
| | concurrency_limit=None,
|
| | additional_inputs=[session_hash, db_url, db_port, db_user, db_pass, db_name, db_tables]
|
| | )
|
| |
|
| | def process_sql_db(url, sql_user, sql_port, sql_pass, sql_db_name, session_hash):
|
| | if url:
|
| | process_message = connect_sql_db(url, sql_user, sql_port, sql_pass, sql_db_name, session_hash)
|
| | return process_message
|
| |
|
| | if __name__ == "__main__":
|
| | demo.launch() |
