|
__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 options as itoptions, to_html_datatable |
|
|
|
from sql import Q |
|
|
|
itoptions.classes = "display compact cell-border" |
|
itoptions.column_filters = "footer" |
|
|
|
HEAD = """ |
|
""" |
|
|
|
|
|
EXAMPLE1 = """ |
|
SELECT |
|
Symbol, |
|
Number, |
|
Mass, |
|
Abundance |
|
FROM 'https://raw.githubusercontent.com/ekwan/cctk/master/cctk/data/isotopes.csv' |
|
""" |
|
|
|
EXAMPLE2 = """ |
|
SELECT |
|
42 AS answer, |
|
'Life, Universe & Everything' AS question |
|
""" |
|
|
|
app = FastAPI() |
|
|
|
@app.get("/q/{base64query}", response_class=HTMLResponse) |
|
def query_db(base64query: str|None = None): |
|
"""Endpoint for running b64-encoded SQL queries.""" |
|
decoded = Q.from_base64(base64query) |
|
df = decoded.df() |
|
html = to_html_datatable(df, buttons=["copyHtml5"]) |
|
return f""" |
|
<head> |
|
{HEAD} |
|
</head> |
|
<div style="width:95vh;">{html}</div> |
|
<hr><pre>{decoded}</pre> |
|
""" |
|
|
|
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(EXAMPLE2) |
|
base64query = decoded.base64 |
|
else: |
|
decoded = Q.from_base64(base64query) |
|
|
|
_host = request.headers.get("Host") |
|
if "huggingface.co/spaces" in _host: |
|
|
|
split_url = _host.rsplit("/", maxsplit=2) |
|
hf_user, hf_space = split_url[1], split_url[2] |
|
host = f"https://{hf_user}-{hf_space}.hf.space" |
|
else: |
|
host = _host |
|
editor_url = f"{host}/sql/?q={base64query}" |
|
query_url = f"{host}/q/{base64query}" |
|
result = f""" |
|
<div id="resultContainer"> |
|
<iframe src="/q/{base64query}" width="90%" height="90%"></iframe> |
|
</div>""" |
|
return (decoded, editor_url, query_url, result) |
|
|
|
with gr.Blocks( |
|
title="Gradio DuckDB Editor", |
|
css="#resultContainer {height: 75vh;}" |
|
) as gradio_sql_interface: |
|
with gr.Row(): |
|
with gr.Column(scale=1, min_width=420): |
|
header = gr.Markdown("# SQL Editor") |
|
sql_code = gr.Code(language="sql", label="SQL Query", lines=32, interactive=True) |
|
button = gr.Button("run") |
|
editor_url = gr.Code(label="Share Editor URL", lines=1) |
|
query_url = gr.Code(label="Share Query Results URL", lines=1) |
|
with gr.Column(scale=1, min_width=540): |
|
markdown = gr.Markdown("# RESULTS") |
|
results = gr.HTML() |
|
button.click(query_from_request, [sql_code], [sql_code, editor_url, query_url, results]) |
|
gradio_sql_interface.load(query_from_request, [sql_code], [sql_code, editor_url, query_url, results]) |
|
|
|
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") |
