Spaces:
Running
Running
File size: 5,475 Bytes
82815c9 0af2a8e 82815c9 0af2a8e 1873f22 0af2a8e 1873f22 0af2a8e 5a864fe 0af2a8e 242c7da 1873f22 0af2a8e 1873f22 0af2a8e 82815c9 0af2a8e 82815c9 0af2a8e 1873f22 0af2a8e 82815c9 0af2a8e 82815c9 0af2a8e 82815c9 ccc9015 0af2a8e 82815c9 ccc9015 0af2a8e ccc9015 82815c9 0af2a8e ccc9015 0af2a8e 82815c9 520da73 82815c9 0af2a8e |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
__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 fastapi.middleware.cors import CORSMiddleware
from sql import Q
from itables import options as itoptions, to_html_datatable
itoptions.classes = "display compact cell-border"
itoptions.column_filters = "footer"
itoptions.layout = {
"top": {
"buttons": ["copyHtml5", "csvHtml5", "pageLength"],
},
"top2": "search",
"top3": "info",
"topStart": None,
"topEnd": None,
"bottom": "paging",
"bottomStart": None,
"bottomEnd": None,
}
CSS = """
#resultContainer {
height: calc(90vh - 40px);
box-shadow: var(--block-shadow) !important;
border-width: var(--block-border-width) !important;
border-color: var(--block-border-color) !important;
border-radius: var(--block-radius) !important;
background: var(--block-background-fill) !important;
}
"""
HEAD = """
<meta http-equiv="Content-Security-Policy" content="upgrade-insecure-requests">
"""
RESULT_TEMPLATE = f"""
<!doctype html>
<html>
<head>{HEAD}</head>
<body>
<div style="">{{datatable}}</div>
<hr><h3>Formatted query:</h3>
<pre>{{q}}</pre>
<hr>ran at {{ran}}</h3>
</body>
</html>
"""
app = FastAPI()
app.add_middleware(
CORSMiddleware,
allow_origins=["*"],
allow_methods=["GET", "POST"],
)
@app.get("/q")
def retrieve_query(query_id: str|None = None, alias: str|None = None):
"""Endpoint for retrieving saved SQL queries."""
q = Q.from_history(query_id=query_id, alias=alias)
return HTMLResponse(content=run_query(q))
def run_query(q: Q, save=True, html_template=RESULT_TEMPLATE):
try:
df = q.df(save=save, _raise=True)
except Exception as e:
df = pd.DataFrame({"error": [str(e)]})
result_datatable = to_html_datatable(df)
html = html_template.format(
datatable=result_datatable,
q=q,
ran=q.end.datetime.strftime("%F %T")
)
return html
def query_from_request(sql_input, definitions, request: gr.Request):
"""Process query from input block or from initial request."""
host = change_hf_host(request)
if not sql_input and not definitions:
# landing page or saved query
url_query_params = dict(request.query_params)
query_id = url_query_params.get("q")
alias = url_query_params.get("alias")
q = Q.from_history(query_id=query_id, alias=alias)
iframe_src = f"/q?query_id={q.source_id}"
else:
# new query - run button was pressed, register unexecuted query
q = Q.from_template_and_definitions(sql_input, definitions)
q.save()
query_id = q.id
alias = q.alias
iframe_src = f"/q?query_id={query_id}"
result = f"""
<div id="resultContainer">
<iframe src="{iframe_src}" width="99%" height="99%"></iframe>
</div>
"""
sql_input = q.template
definitions = f"{q.definitions}"
editor_url = "".join([
f"http://{host}/sql?",
f"q={query_id}" if query_id else "",
f"&alias={alias}" if alias else "",
])
result_url = f"http://{host}{iframe_src}"
return (sql_input, definitions, result, editor_url, result_url)
with gr.Blocks(
title="Gradio DuckDB Editor",
theme=gr.themes.Soft(),
css=CSS,
) as gradio_sql_interface:
with gr.Row():
with gr.Column(scale=2, min_width=450):
caption1 = gr.Markdown("# SQL Editor\nClick buttons below to see examples")
with gr.Row():
ex1_button = gr.Button("Variables", link="/sql?alias=example1")
ex2_button = gr.Button("URL", link="/sql?alias=example2")
ex3_button = gr.Button("Local File", link="/sql?alias=example3")
definitions = gr.Code(label="Definitions", lines=2, interactive=True)
sql_input = gr.Code(label="SQL Query", language="sql", lines=25, interactive=True)
run_button = gr.Button("run", variant="primary")
editor_url = gr.Code(label="Share Editor URL", lines=1)
result_url = gr.Code(label="Share Query Results URL", lines=1)
with gr.Column(scale=3, min_width=600):
caption2 = gr.Markdown("# RESULTS")
result = gr.HTML(elem_classes="block")
magic = dict(
fn=query_from_request,
inputs=[sql_input, definitions],
outputs=[sql_input, definitions, result, editor_url, result_url]
)
run_button.click(**magic)
gradio_sql_interface.load(**magic)
def change_hf_host(request: gr.Request):
"""Access endpoints that hidden from default HF app landing page."""
_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
return host
app = gr.mount_gradio_app(app, gradio_sql_interface, path="/sql")
@app.get("/")
def redirect_hack():
return RedirectResponse("/redirecting")
@app.get("/redirecting")
def redirect_to_example():
return RedirectResponse("/sql?alias=example1") |