sql generation
**Text-to-SQL Generation**
**What is Text-to-SQL?**
Converting natural language questions into SQL queries that can be executed against databases.
**Basic Approach**
```python
def text_to_sql(question: str, schema: str) -> str:
return llm.generate(f"""
Given this database schema:
{schema}
Convert this question to SQL:
{question}
SQL query (only output the query):
""")
```
**Schema Representation**
```python
schema = """
Tables:
- users (id, name, email, created_at)
- orders (id, user_id, total, status, created_at)
- products (id, name, price, category)
- order_items (order_id, product_id, quantity)
Relationships:
- orders.user_id -> users.id
- order_items.order_id -> orders.id
- order_items.product_id -> products.id
"""
```
**Advanced Text-to-SQL**
**With Examples**
```python
def text_to_sql_few_shot(question: str, schema: str, examples: list) -> str:
examples_text = "
".join([
f"Q: {e['question']}
SQL: {e['sql']}"
for e in examples
])
return llm.generate(f"""
Schema: {schema}
Examples:
{examples_text}
Q: {question}
SQL:
""")
```
**Multi-Step for Complex Queries**
```python
def complex_text_to_sql(question: str, schema: str) -> str:
# Step 1: Decompose
steps = llm.generate(f"Break down: {question}")
# Step 2: Generate sub-queries
sub_queries = [text_to_sql(step, schema) for step in steps]
# Step 3: Combine
return llm.generate(f"Combine these queries: {sub_queries}")
```
**SQL Validation**
```python
def validate_and_fix(sql: str, schema: str, error: str) -> str:
return llm.generate(f"""
This SQL query has an error:
{sql}
Error: {error}
Schema: {schema}
Fixed query:
""")
```
**Security Considerations**
| Risk | Mitigation |
|------|------------|
| SQL injection | Parameterized queries |
| Data exposure | Limit schema to allowed tables |
| Destructive queries | Read-only permissions |
| Resource abuse | Query timeout/limits |
**Use Cases**
| Use Case | Example |
|----------|---------|
| Analytics | "Show sales by region last quarter" |
| Reporting | "Top 10 customers by revenue" |
| Data exploration | "How many orders per category?" |
**Tools and Frameworks**
| Tool | Features |
|------|----------|
| LangChain SQL Agent | Multi-step, error correction |
| Vanna.ai | RAG-based, learns from examples |
| Defog | Fine-tuned models |