# 1. Populate database with sample data
python populate_kinto_db.py

# 2. Run example client
python src/text2sql_example.py

# 3. Try questions like:
# "How many users do I have?"
# "Show me top 2 users aged above 70"
# "What is the average product price?"

./setup_mcp.sh
# Restart Kiro

"How many users do I have?"
"Show me top 2 users aged above 70"
"What is the average product price?"
"Show me products in Electronics category"

./setup_cline.sh
# Restart VSCode

{"question": "How many users do I have?"}

{"sql": "SELECT * FROM users WHERE age > 70 LIMIT 5"}

from text2sql import Text2SQL

text2sql = Text2SQL(db_path="kinto.db")
result = text2sql.query("How many users do I have?")

if result.success:
    print(f"SQL: {result.query}")
    print(f"Answer: {text2sql.format_result(result)}")

pip install -r requirements.txt

docker-compose up -d
# or
make docker-build
make docker-run

make install    # Install dependencies
make run        # Run servers
make example    # Run example client
make test       # Run tests
make help       # Show all commands

# Run the example client with pre-defined queries
python src/text2sql_example.py

# Run all unit tests (9 test cases)
make test-text2sql

python -m unittest src.test_text2sql -v

./setup_mcp.sh

"How many users do I have?"
"Show me top 2 users aged above 70"
"What is the average product price?"

./setup_cline.sh

from text2sql import Text2SQL

text2sql = Text2SQL(db_path="kinto.db")

# Test a query
result = text2sql.query("How many users do I have?")
print(f"Success: {result.success}")
print(f"SQL: {result.query}")
print(f"Answer: {text2sql.format_result(result)}")

# Verify database exists and has data
python -c "import sqlite3; conn = sqlite3.connect('kinto.db'); print('Users:', conn.execute('SELECT COUNT(*) FROM users').fetchone()[0])"

src/
├── text2sql.py              # Core module (400+ lines)
├── text2sql_server.py       # MCP server
├── text2sql_example.py      # Example client
└── test_text2sql.py         # Unit tests (9 tests)

mcp_text2sql_config.json     # MCP configuration
kinto.db                     # SQLite database

Answer: 50

id | name  | email             | age
───────────────────────────────────
1  | Alice | alice@example.com | 75
2  | Bob   | bob@example.com   | 72

User Question
    ↓
Parse Query Type (count/select/aggregate/limit)
    ↓
Extract Conditions (age, price, category, etc.)
    ↓
Generate SQL Query
    ↓
Execute Against SQLite
    ↓
Format Results (string or table)
    ↓
Return Answer

text2sql_server.py (MCP Server)
├── query_database tool
├── get_database_schema tool
└── execute_sql tool
    ↓
text2sql.py (Core Module)
├── Query Parsing
├── Condition Extraction
├── SQL Generation
└── Result Formatting
    ↓
SQLite Database (kinto.db)
├── users (50 rows)
├── products (30 rows)
├── orders (100 rows)
├── reviews (75 rows)
└── inventory (30 rows)

1. How many users do I have?
2. Show me top 5 products with price above 100
3. What is the average rating for all reviews?
4. Show me users aged above 70
5. How many orders have been placed in Electronics category?
6. What is the average product price?
7. Show me all products in Electronics category
8. How many reviews have rating above 4?
9. Show me top 10 users
10. What is the total stock available?
11. Show me products with stock below 20
12. How many products are in Books category?
13. What is the average age of users?
14. Show me orders with quantity above 5
15. How many inventory records do we have?
16. Show me top 3 products with highest price
17. What is the sum of all product prices?
18. Show me users aged below 30
19. How many products have price above 200?
20. Show me reviews with rating below 3
21. What is the average order quantity?
22. Show me products in Warehouse A location
23. How many users were created this year?
24. Show me top 5 most expensive products
25. What is the total quantity available in inventory?

Q: "How many users do I have?"
SQL: SELECT COUNT(*) as count FROM users
A: 50

Q: "How many products are in stock?"
SQL: SELECT COUNT(*) as count FROM products
A: 30

Q: "Show me all users"
SQL: SELECT * FROM users
A: [Table with all 50 users]

Q: "Get products"
SQL: SELECT * FROM products
A: [Table with all 30 products]

Q: "Show me users aged above 70"
SQL: SELECT * FROM users WHERE age > 70
A: [Table with users over 70]

Q: "Products with price below 50"
SQL: SELECT * FROM products WHERE price < 50
A: [Table with affordable products]

Q: "Show me products in Electronics category"
SQL: SELECT * FROM products WHERE category = 'Electronics'
A: [Table with Electronics products]

Q: "Show me top 2 users"
SQL: SELECT * FROM users LIMIT 2
A: [Table with first 2 users]

Q: "Top 5 products"
SQL: SELECT * FROM products LIMIT 5
A: [Table with first 5 products]

Q: "Show me top 2 users aged above 70"
SQL: SELECT * FROM users WHERE age > 70 LIMIT 2
A: [Table with 2 users over 70]

Q: "Top 10 products in Electronics with stock above 20"
SQL: SELECT * FROM products WHERE category = 'Electronics' AND stock > 20 LIMIT 10
A: [Table with matching products]

Q: "What is the average product price?"
SQL: SELECT AVG(price) as result FROM products
A: 125.50

Q: "What is the total stock?"
SQL: SELECT SUM(stock) as result FROM products
A: 5000

Q: "Average user age?"
SQL: SELECT AVG(age) as result FROM users
A: 52.3

from text2sql import Text2SQL

# Initialize
text2sql = Text2SQL(db_path="kinto.db")

# Simple query
result = text2sql.query("How many users do I have?")

# Check result
if result.success:
    print(f"Generated SQL: {result.query}")
    print(f"Raw Data: {result.data}")
    print(f"Formatted: {text2sql.format_result(result)}")
else:
    print(f"Error: {result.message}")

# Access result data directly
for row in result.data:
    print(row)

import json
from mcp.client import Client

client = Client()

# Query database
response = client.use_tool(
    "text2sql-mcp-server",
    "query_database",
    {"question": "How many users do I have?"}
)

# Get schema
schema = client.use_tool(
    "text2sql-mcp-server",
    "get_database_schema",
    {}
)

# Execute raw SQL
result = client.use_tool(
    "text2sql-mcp-server",
    "execute_sql",
    {"sql": "SELECT * FROM users WHERE age > 70 LIMIT 5"}
)

from text2sql import Text2SQL

# Point to your database
text2sql = Text2SQL(db_path="path/to/your/database.db")

# Query as normal
result = text2sql.query("Your question here")

{
  "mcpServers": {
    "text2sql-mcp-server": {
      "command": "python",
      "args": ["src/text2sql_server.py"],
      "env": {
        "PYTHONUNBUFFERED": "1"
      },
      "disabled": false,
      "autoApprove": [
        "query_database",
        "get_database_schema",
        "execute_sql"
      ]
    }
  }
}

# Change this line:
text2sql = Text2SQL(db_path="kinto.db")

# To:
text2sql = Text2SQL(db_path="path/to/your/database.db")

logging.basicConfig(
    level=logging.DEBUG,  # Change to DEBUG for verbose logging
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('logs/text2sql_server.log'),
        logging.StreamHandler()
    ]
)

Q: "How many customers are over 70 years old?"
A: 8

Q: "Show me top 5 oldest customers"
A: [Table with oldest customers]

Q: "What is the average age of our customers?"
A: 52.3

Q: "How many products do we have?"
A: 30

Q: "What is the average product price?"
A: 125.50

Q: "Show me products in Electronics category"
A: [Table with Electronics products]

Q: "How many products have stock above 50?"
A: 15

Q: "How many orders have been placed?"
A: 100

Q: "What is the average order quantity?"
A: 4.5

Q: "Show me top 10 orders"
A: [Table with top 10 orders]

Q: "What is the total quantity available?"
A: 8500

Q: "Show me inventory in Warehouse A"
A: [Table with Warehouse A inventory]

Q: "How many inventory records do we have?"
A: 30

pip install -r requirements.txt

python populate_kinto_db.py

Text2SQL/
├── src/
│   ├── __init__.py
│   ├── text2sql.py              # Core module
│   ├── text2sql_server.py       # MCP server
│   ├── text2sql_example.py      # Example client
│   └── test_text2sql.py         # Unit tests
├── logs/
│   └── text2sql_server.log      # Server logs
├── kinto.db                     # SQLite database
├── mcp_text2sql_config.json     # MCP config
├── populate_kinto_db.py         # Database setup
├── README.md                    # This file
├── Makefile                     # Build commands
├── requirements.txt             # Dependencies
└── setup.py                     # Package setup

# Run all tests
make test

# Run specific test
python -m unittest src.test_text2sql.TestText2SQL.test_count_query -v

# Run with coverage
python -m coverage run -m unittest src.test_text2sql
python -m coverage report

def _parse_custom_query(self, query: str) -> Optional[str]:
    """Parse custom query type."""
    # Your logic here
    return sql_query

# In _extract_conditions()
if 'custom_field' in query:
    match = re.search(r'custom_field\s+(?:above|over|>)\s+(\d+)', query)
    if match:
        conditions.append(f"custom_field > {match.group(1)}")

class Text2SQL:
    def __init__(self, db_path: str = "kinto.db")
    def query(self, natural_language: str) -> QueryResult
    def format_result(self, result: QueryResult) -> str

@dataclass
class QueryResult:
    success: bool              # Query executed successfully
    query: str                 # Generated SQL query
    data: List[Dict]          # Query results
    message: str              # Status message
    format_type: str          # 'table' or 'string'

{
  "question": "How many users do I have?"
}

Query: SELECT COUNT(*) as count FROM users

Result:
50

Database Schema:

Table: users
  Columns: id, name, email, age, created_at

Table: products
  Columns: id, name, price, stock, category

...

{
  "sql": "SELECT * FROM users WHERE age > 70 LIMIT 5"
}

SQL: SELECT * FROM users WHERE age > 70 LIMIT 5

Result:
id | name  | email             | age
───────────────────────────────────
1  | Alice | alice@example.com | 75
2  | Bob   | bob@example.com   | 72

# Build image
make docker-build

# Run container
make docker-run

# View logs
make docker-logs

# Stop container
make docker-stop

# Install dependencies
make install

# Run example
make example

# Run tests
make test