Spaces:
Running
Running
| __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" | |
| 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() | |
| 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) | |
| return f""" | |
| <head> | |
| <link rel="stylesheet" href="https://cdn.datatables.net/2.0.5/css/dataTables.dataTables.min.css" /> | |
| </head> | |
| <div>{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: | |
| # modify URL to access endpoints that aren't available on default app landing page | |
| 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): | |
| 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=2): | |
| 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") | |
| def redirect_to_example(): | |
| return RedirectResponse("/sql/?q=example") |