tools/paris_trees_sql.py

import gradio as gr
import json
from PIL import Image
import numpy as np

# --- User Defined Logic ---
import duckdb
import pandas as pd
import requests
import tempfile
import os

def paris_trees_sql(sql_query: str) -> str:
    """
    Execute a SQL query on the Paris trees dataset using DuckDB.
    
    Downloads the official Paris open data file (les-arbres) as a Parquet file,
    loads it into an in-memory DuckDB database as table 'paris_trees', and
    executes the provided SQL query against it. Results are limited to 1000 rows.
    
    Args:
        sql_query (str): A valid SQL query to run against the paris_trees table.
    
    Returns:
        str: A formatted table string with column headers and up to 1000 rows of results.
             If an error occurs, returns a descriptive error message.
    """
    url = "https://opendata.paris.fr/api/explore/v2.1/catalog/datasets/les-arbres/exports/parquet?lang=fr&timezone=Europe%2FBerlin"
    
    try:
        # Download the Parquet file
        response = requests.get(url, timeout=60)
        response.raise_for_status()
        
        # Save to a temporary file
        with tempfile.NamedTemporaryFile(delete=False, suffix=".parquet") as tmp:
            tmp.write(response.content)
            tmp_path = tmp.name
        
        # Connect to DuckDB in-memory and load the data
        conn = duckdb.connect(":memory:")
        conn.execute(f"CREATE TABLE paris_trees AS SELECT * FROM read_parquet('{tmp_path}')")
        
        # Execute user query with a limit
        limited_query = f"{sql_query.rstrip(';')} LIMIT 1000"
        result_df = conn.execute(limited_query).df()
        
        # Format output as a table string
        if result_df.empty:
            return "Query executed successfully but returned no results."
        
        output = result_df.to_string(index=False)
        return output
    
    except requests.exceptions.RequestException as e:
        return f"Error downloading dataset: {e}"
    except duckdb.CatalogException as e:
        return f"SQL error (possibly invalid table/column names): {e}"
    except duckdb.ParserException as e:
        return f"SQL syntax error: {e}"
    except Exception as e:
        return f"Unexpected error: {e}"
    finally:
        # Clean up temporary file
        if 'tmp_path' in locals() and os.path.exists(tmp_path):
            os.remove(tmp_path)

# --- Interface Factory ---
def create_interface():
    return gr.Interface(
        fn=paris_trees_sql,
        inputs=[gr.Textbox(label=k) for k in ['sql_query']],
        outputs=gr.Textbox(label="Query results formatted as a table (max 1000 rows) or an error message"),
        title="paris_trees_sql",
        description="Auto-generated tool: paris_trees_sql"
    )