Spaces:
Runtime error
Runtime error
| import os | |
| import gradio as gr | |
| from openai import AsyncOpenAI | |
| import pandas as pd | |
| import uuid, asyncio | |
| from sql_tab import run_sql | |
| from logger import log_event | |
| from chat_helpers import build_input_from_history, get_db_sys_prompt | |
| oclient = AsyncOpenAI(api_key=os.getenv("OPENAI_API_KEY")) | |
| max_rows = 100 | |
| async def respond_once(message, history): | |
| text_input = build_input_from_history(message, history) | |
| kwargs = dict( | |
| model="gpt-4.1", | |
| input=text_input, | |
| temperature=0, | |
| instructions=get_db_sys_prompt(), | |
| tools=[{"type": "web_search"}], | |
| tool_choice="auto", | |
| parallel_tool_calls=True, | |
| ) | |
| # MOCK mode to isolate app/DB without burning tokens | |
| if os.getenv("MOCK_OPENAI", "").lower() in {"1", "true", "yes"}: | |
| import random, asyncio | |
| await asyncio.sleep(random.uniform(0.05, 0.25)) | |
| return "MOCK: Here’s a fabricated answer for load testing." | |
| resp = await oclient.responses.create(**kwargs) | |
| return getattr(resp, "output_text", "") | |
| async def respond(message, history): | |
| text_input = build_input_from_history(message, history) | |
| kwargs = dict( | |
| model="gpt-4.1", | |
| input=text_input, | |
| temperature=0, | |
| instructions=get_db_sys_prompt(), | |
| tools=[{"type": "web_search"}], | |
| tool_choice="auto", | |
| parallel_tool_calls=True, | |
| ) | |
| buffer = [] | |
| async with oclient.responses.stream(**kwargs) as stream: | |
| async for event in stream: | |
| if event.type == "response.output_text.delta": | |
| buffer.append(event.delta) | |
| yield "".join(buffer) | |
| final = await stream.get_final_response() | |
| final_text = getattr(final, "output_text", None) | |
| if final_text and (not buffer or final_text != "".join(buffer)): | |
| yield final_text | |
| async def chat_driver(user_message, messages_history, _user_name, _session_id): | |
| messages_history = messages_history or [] | |
| base = messages_history + [{"role": "user", "content": user_message}] | |
| assistant_text = "" | |
| asyncio.create_task(log_event(_user_name, _session_id, "chat_user", {"text": user_message})) | |
| async for chunk in respond(user_message, messages_history): | |
| assistant_text = chunk | |
| # stream to UI | |
| yield base + [{"role": "assistant", "content": assistant_text}], "" | |
| # after stream finished, log the final assistant text | |
| asyncio.create_task(log_event(_user_name, _session_id, "chat_assistant", {"text": assistant_text})) | |
| async def post_completion_code(_user_name, _session_id): | |
| code = "9C1F4B2E" | |
| msg = f"the completion code is {code}" | |
| updated = [{"role": "assistant", "content": msg}] | |
| await log_event(_user_name, _session_id, "completion_code", {"code": code}) | |
| return updated | |
| with gr.Blocks(title="Movie Database", theme="soft") as demo: | |
| # gr.Markdown("## Movie Database Bot and SQL Console") | |
| user_name = gr.State("") | |
| session_id = gr.State("") | |
| with gr.Column(visible=True) as identify_view: | |
| gr.Markdown("### Login") | |
| name_tb = gr.Textbox(label="Student ID (required)", placeholder="Please enter your student ID", autofocus=True) | |
| enter_btn = gr.Button("Enter", variant="primary") | |
| id_msg = gr.Markdown("") | |
| async def do_login(name): | |
| name = (name or "").strip() | |
| if not name: | |
| return (gr.update(visible=True), gr.update(visible=False), "⚠️ Please enter your student ID to continue.", "", "") | |
| sid = uuid.uuid4().hex | |
| await log_event(name, sid, "login", {"meta": {"agent": "gradio_app", "version": 1}}) | |
| return (gr.update(visible=False), gr.update(visible=True), "", name, sid) | |
| with gr.Column(visible=False) as app_view: | |
| welcome_md = gr.Markdown("") | |
| with gr.Tabs(): | |
| with gr.Tab("Assignment"): | |
| gr.Markdown(""" | |
| <h2> Platform Usage and the Assignment </h2> | |
| <br> | |
| <ul> | |
| <li> You can use the "SQL" tab to run your queries and see if you have the correct results.</li> | |
| <li> The "Chatbot" tab provides you a chatbot (that is connected to ChatGPT) to ask questions about PostgreSQL and the database.</li> | |
| <li> The chatbot knows the tables and their columns, and would help with questions.</li> | |
| <li> Even with its knowledge, the chatbot can still make mistakes.</li> | |
| <li> When you are finished with all questions, the survey platform will ask for a completion code. You can find it in the "Chatbot" tab. </li> | |
| <li> <b> Reminder: </b> This assignment is optional and ungraded. It is designed for you to practice. You can be relaxed, it is okay to have errors. Good luck! </li> | |
| </ul> | |
| <h3> Database </h3> | |
| The database has 4 tables, each corresponding to the 4 excel files you have for the project: | |
| <ul> | |
| <li>sales</li> | |
| <li>metadata</li> | |
| <li>user_reviews</li> | |
| <li>expert_reviews</li> | |
| </ul> | |
| <br> | |
| <b> Important Notes: </b> | |
| <br> | |
| <br> | |
| <ul> | |
| <li> A proper ERD or foreign key relationships are not defined for the tables. You can still join them based on the column names, but be careful. </li> | |
| <li> Some movies have the same title but they are different movies. </li> | |
| <li> A column that stores numerical information might have the datatype "text". </li> | |
| <li> Datatypes might not be exactly the same as the excel files. </li> | |
| <li> Some columns might store null values as text, like "n/a" or "null". </li> | |
| <li> Columns with the same names might store different values in different tables. Example: "url" column in metadata and sales.</li> | |
| </ul> | |
| """) | |
| with gr.Tab("Chatbot"): | |
| chatbot = gr.Chatbot(type="messages", label="Conversation", height=450) | |
| with gr.Row(): | |
| chat_input = gr.Textbox( | |
| placeholder="How can I help you with PostgreSQL today?", | |
| scale=8, | |
| autofocus=True, | |
| container=False, | |
| ) | |
| send_btn = gr.Button("Send", variant="primary", scale=1) | |
| code_btn = gr.Button("Completion code", variant="secondary", scale=1) | |
| def _clear_input(): | |
| return "" | |
| ev = send_btn.click(chat_driver, [chat_input, chatbot, user_name, session_id], [chatbot, chat_input]) | |
| ev.then(_clear_input, None, [chat_input]) | |
| ev2 = chat_input.submit(chat_driver, [chat_input, chatbot, user_name, session_id], [chatbot, chat_input]) | |
| ev2.then(_clear_input, None, [chat_input]) | |
| code_btn.click( | |
| post_completion_code, | |
| inputs=[user_name, session_id], | |
| outputs=[chatbot], | |
| ) | |
| with gr.Tab("SQL"): | |
| with gr.Column(): | |
| sql_input = gr.Code( | |
| label="SQL", | |
| language="sql", | |
| value="SELECT * FROM sales;", | |
| lines=10, | |
| ) | |
| with gr.Row(): | |
| run_btn = gr.Button("Run", variant="primary") | |
| clear_btn = gr.Button("Clear") | |
| results = gr.Dataframe( | |
| label="Results", | |
| wrap=True, | |
| interactive=True, | |
| ) | |
| meta = gr.Markdown("") | |
| plan = gr.Markdown("", label="Explain/Plan") | |
| async def on_run(q, _user_name, _session_id): | |
| df, meta_msg, _ = await asyncio.to_thread(run_sql, q, max_rows, False) | |
| await log_event( | |
| _user_name, _session_id, "sql", | |
| { | |
| "query": q, | |
| "row_limit": max_rows, | |
| "row_count": int(getattr(df, "shape", [0])[0]), | |
| "meta": meta_msg, | |
| }, | |
| ) | |
| return df, meta_msg, "" | |
| def on_clear(): | |
| return "", pd.DataFrame(), "Cleared.", "" | |
| run_btn.click(on_run, [sql_input, user_name, session_id], [results, meta, plan]) | |
| clear_btn.click(on_clear, inputs=None, outputs=[sql_input, results, meta, plan]) | |
| outputs = [identify_view, app_view, id_msg, user_name, session_id] | |
| enter_btn.click(do_login, inputs=[name_tb], outputs=outputs) | |
| name_tb.submit(do_login, inputs=[name_tb], outputs=outputs) | |
| def greet(name): | |
| return f"**Hello, {name}!**" | |
| user_name.change(greet, inputs=[user_name], outputs=[welcome_md]) | |
| if __name__ == "__main__": | |
| demo.launch(share=True) | |