📁 File Structure
projects/sme-advisor-alloydb/
├── main.py— FastAPI server + ADK agent + SQL generation
├── load_data.py— CSV to PostgreSQL import script
├── index.html— Chat interface with DB panel + execution log
├── dataset.csv— E-commerce dataset
├── requirements.txt— Python dependencies
└── Dockerfile— Container config for Cloud Run
🔑 Key Code: SQL Generation Tool
The core tool takes a natural language question, uses Gemini to generate a SQL query, executes it against PostgreSQL (AlloyDB), and returns the results. The agent handles the full cycle from question to answer.
def execute_sql_query(question: str) -> dict:
"""Convert natural language to SQL, execute against PostgreSQL,
and return structured results."""
sql_prompt = f"""Given this table schema:
orders(order_id, order_date, category, sub_category,
region, city, sales, quantity, discount,
profit, ship_mode, segment, returned)
Write a PostgreSQL SELECT query for: {question}
Return ONLY the SQL query, no explanation."""
sql_query = gemini_client.generate(sql_prompt).text.strip()
if not validate_sql(sql_query):
return {"error": "Only SELECT queries are allowed"}
conn = pg8000.connect(host=DB_HOST, database=DB_NAME,
user=DB_USER, password=DB_PASS)
cursor = conn.cursor()
cursor.execute(sql_query)
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
return {
"sql": sql_query,
"columns": columns,
"data": rows,
"row_count": len(rows)
}
🔑 Key Code: 4-Step Execution Log
The UI displays a real-time execution log showing each step of the query pipeline. Users can see exactly how their question becomes an answer.
<div class="execution-log">
<h3>Execution Log</h3>
<div class="log-step" id="step1">
<span class="step-icon">1</span>
<span>Question received</span>
</div>
<div class="log-step" id="step2">
<span class="step-icon">2</span>
<span>SQL generated</span>
<pre id="generated-sql"></pre>
</div>
<div class="log-step" id="step3">
<span class="step-icon">3</span>
<span>Database executed (<span id="row-count"></span> rows)</span>
</div>
<div class="log-step" id="step4">
<span class="step-icon">4</span>
<span>Answer returned</span>
</div>
</div>
🔑 Key Code: Safety Check
Before executing any generated SQL, a validation function ensures only SELECT queries run against the database. This prevents accidental or malicious data modification.
def validate_sql(sql: str) -> bool:
"""Only allow SELECT queries — block INSERT, UPDATE, DELETE, DROP, etc."""
cleaned = sql.strip().upper()
blocked = ["INSERT", "UPDATE", "DELETE", "DROP",
"ALTER", "CREATE", "TRUNCATE", "GRANT"]
if not cleaned.startswith("SELECT"):
return False
for keyword in blocked:
if keyword in cleaned:
return False
return True
📦 Dependencies (requirements.txt)
google-adk
google-genai
fastapi
uvicorn
pandas
pg8000