pythonadvanced

Text-to-SQL with Validation Safety

Convert natural language to SQL with LLM and validate queries before execution for safety.

pythonPress ⌘/Ctrl + Shift + C to copy

import re
from openai import OpenAI
import sqlite3

client = OpenAI()

SAFE_OPERATIONS = {'select', 'with'}

def is_safe_query(sql: str) -> bool:
    first_token = sql.strip().split()[0].lower()
    has_drop    = bool(re.search(r'\b(drop|delete|truncate|alter|create|insert|update)\b', sql, re.I))
    return first_token in SAFE_OPERATIONS and not has_drop

def nl_to_sql(question: str, schema: str, model: str = 'gpt-4o-mini') -> str:
    prompt = f'Schema:\n{schema}\n\nWrite a SQL SELECT query to answer: {question}\nReturn only the SQL query.'
    resp   = client.chat.completions.create(model=model, messages=[{'role':'user','content':prompt}], temperature=0)
    sql    = resp.choices[0].message.content.strip().strip('```sql').strip('```').strip()
    return sql

schema = 'users(id, name, age, email); orders(id, user_id, amount, created_at)'
query  = nl_to_sql('How many orders has each user placed?', schema)
print('Generated SQL:', query)
print('Is safe:', is_safe_query(query))