__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()
@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)
return f"""
{html}
{decoded}
"""
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"""