main.py

__doc__ = """
This FastAPI app uses gradio components with SQL code input
and HTML table output.  The query is executed using DuckDB.
The query results are shown in an iframe where the table
is styled and made interactive using Datatables.net scripts.

"""

import gradio as gr
import pandas as pd
from fastapi import FastAPI
from fastapi.responses import HTMLResponse, RedirectResponse
from itables import to_html_datatable

from sql import Q

app = FastAPI()

def query_from_request(query, request: gr.Request):
    """Process query from input block or from initial request.

    https://github.com/gradio-app/gradio/issues/7464#issuecomment-1960161591
    """
    if not query:
        query_params = request.query_params
        base64query = dict(query_params).get("q")
    else:
        base64query = Q(query).base64
    if base64query in (None, "example"):
        decoded = Q("""SELECT 42 AS answer, 'LU & E' AS question""")
        base64query = decoded.base64
    else:
        decoded = Q.from_base64(base64query)
    href = format_href(base64query)
    result = f"""<iframe src="/q/{base64query}" width="90%" height="90%"></iframe>"""
    return (decoded, href, result)

def format_href(url: str):
    href=f"localhost:7860/sql/{url}"
    return f"""<a href="{href}">{href}</a>"""

@app.get("/q/{base64query}", response_class=HTMLResponse)
def query_db(base64query: str|None = None):
    decoded = Q.from_base64(base64query)
    df = decoded.df()
    html = to_html_datatable(df)
    return f"""
        <h3>{decoded}</h3>
        <div>{html}</div>
    """

with gr.Blocks() as gradio_sql_interface:
    with gr.Row():
        with gr.Column():
            header = gr.Markdown("# SQL Editor")
            sql_code = gr.Code(language="sql", label="SQL Query", interactive=True)
            copy_button = gr.HTML()
            button = gr.Button("run")
        with gr.Column():
            markdown = gr.Markdown("# RESULTS")
            results = gr.HTML()
    button.click(query_from_request, [sql_code], [sql_code, copy_button, results]) 
    gradio_sql_interface.load(query_from_request, [sql_code], [sql_code, copy_button, results], queue=True)

app = gr.mount_gradio_app(app, gradio_sql_interface, path="/sql")

@app.get("/")
@app.get("/sql")
@app.get("/sql/")
def redirect_to_example():
    return RedirectResponse("/sql/?q=example")

# no need to mount/unmount dynamically
# just change path and app blocks?
# {
#   "api.routes": [
#     "...",
#     "Mount(path='/gradiosql/qq', name='', app=<gradio.routes.App object at 0x71dc7b9d6d10>)"
#   ]
# }
# @app.get("/sql/{query}")
# def render_query(query: str):
#     # unmount any previous gradio apps
#     # for route in app.routes:
#     #     if "gradio" in route.path:
#     #         del route
#     # global app
#     # routes = [route for route in app.routes if 'gradio' not in route.path]
#     # app = Starlette(routes=routes)
#     with gr.Blocks() as gradio_sql_interface:
#         sql_code = gr.Code(value=query, language="sql", label="SQL Query", interactive=True)
#         button = gr.Button("run", link=f"/sql/{sql_code.value}")
#         markdown = gr.Markdown("# RESULTS")
#         html = gr.HTML(value=f"""<iframe src="/q/{query}" width="90%" height="90%"></iframe>""")
#         # button.click(None, [], [], js=f"window.open('/sql/{sql_code.value}', '_top')")

#     gradio_path = f"/gradiosql/{sql_code.value}"
    # gr.mount_gradio_app(app, gradio_sql_interface, path=gradio_path)

    # return RedirectResponse(gradio_path)

#     del app.routes[index]
# @app.get("/redirect")
# async def redirect_example():
#     url = "/q/qqq"
#     return Response(status_code=302, headers={"Location": url})


    
#     # return """hi<hr><iframe src="/" width="80%" height="80%"></iframe>"""

# @app.get("/qq/{apath}")
# async def build_sql_interface(apath: str):
#     with gr.Blocks() as gradio_sql_interface:
#         sql_code = gr.Code(value=apath, language="sql", label="SQL Query", interactive=True)
#         html_code = gr.Code(value=html)
#         html_html = gr.HTML(value=f"""<iframe src="/q/{apath}" width="80%" height="80%"></iframe>""")
#         grdf = gr.DataFrame(value=df)
#         # gradio_sql_interface.queue() 

#     gr.mount_gradio_app(app, gradio_sql_interface, path="/gradio/{apath}")
#     # import time; time.sleep(1)
#     return RedirectResponse("/gradio/{{apath}}")
    # async with httpx.AsyncClient() as client:
    #     try:
    #         response = await client.get("/gradio")
    #         response.raise_for_status()
    #         return response
    #     except httpx.HTTPError as e:
    #         return {"errresponse": e}
    #         pass #raise HTTPException(status_code=exc.status_code, detail=exc.msg)
    # return {"path":path}

# if __name__ == "__main__":
#     import uvicorn
#     uvicorn.run(app, host="0.0.0.0", port=7860)
# appenv/.venv/bin/uvicorn main:app --host 0.0.0.0 --port 7860