Spaces:
Sleeping
Sleeping
File size: 6,375 Bytes
82815c9 0af2a8e 0c881ed 82815c9 0af2a8e 1873f22 55a58f8 0af2a8e 1873f22 0af2a8e 89caffe 0af2a8e 5a864fe 0af2a8e 242c7da 1873f22 0af2a8e 55a58f8 0af2a8e 1873f22 0af2a8e 82815c9 0af2a8e 82815c9 0af2a8e 1873f22 0af2a8e 82815c9 0af2a8e f469b00 0af2a8e 82815c9 0af2a8e a0cf33f 0c881ed 82815c9 ccc9015 0af2a8e f469b00 0af2a8e 82815c9 ccc9015 0af2a8e ccc9015 82815c9 55a58f8 0af2a8e 55a58f8 0af2a8e ccc9015 0af2a8e 55a58f8 0af2a8e 82815c9 f469b00 82815c9 f469b00 520da73 f469b00 a0cf33f |
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 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 |
__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 os import getenv
from sql import Q
from itables import options as itoptions, to_html_datatable
itoptions.classes = "display compact cell-border"
itoptions.column_filters = "footer"
itoptions.maxBytes = 2**20
itoptions.layout = {
"top": {
"buttons": ["copyHtml5", "csvHtml5", "pageLength"],
},
"top2": "search",
"top3": "info",
"topStart": None,
"topEnd": None,
"bottom": "paging",
"bottomStart": None,
"bottomEnd": None,
}
CSS = """
#resultContainer {
height: max(880px, 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>
<hr>
<footer>
<p>this demo returns a maximum of 10K rows or 1MB of data</p>
<p><a href="https://www.linkedin.com/in/alekis/">like / comment / get in touch</a></p>
</footer>
</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")
if not query_id and not alias:
alias = "example1"
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
hf_space_host = None #getenv("SPACE_HOST")
if hf_space_host:
iframe_src = f"https://{hf_space_host}/q?query_id={query_id}"
else:
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}/?",
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=480):
caption1 = gr.Markdown("# SQL Editor\nClick buttons below to see examples")
with gr.Row(variant="compact"):
ex1_button = gr.Button("Variables", link="/?alias=example1", min_width=100)
ex2_button = gr.Button("URL", link="/?alias=example2", min_width=100)
ex3_button = gr.Button("Local File", link="/?alias=example3", min_width=100)
ex4_button = gr.Button("Bad Query", link="/?alias=bad_example", min_width=100)
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=540):
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="/")
# @app.get("/")
# # def redirect_hack():
# # return RedirectResponse("/redirecting")
# # @app.get("/redirecting")
# def redirect_to_example():
# hf_space_host = getenv("SPACE_HOST")
# if hf_space_host:
# return RedirectResponse(f"https://{hf_space_host}/sql?alias=example1")
# else:
# return RedirectResponse("/sql?alias=example1")
# return RedirectResponse("/sql?alias=example1") |