pcybox-sqlbuddy 0.1.0

AI-powered SQL query generator using OpenAI GPT or Anthropic Claude. Automatic schema extraction, query validation, and optimization for MySQL and PostgreSQL.

SQL Buddy 🤖

AI-powered SQL query generator by MISTER IKS

Generate accurate SQL queries from natural language using OpenAI GPT or Anthropic Claude. Supports MySQL & PostgreSQL with automatic schema analysis, query validation, and optimization.

✨ Key Features

• Multi-Database: MySQL & PostgreSQL support
• Dual AI Support: OpenAI GPT-4 & Anthropic Claude
• Built-in Safety: SQL injection detection & query validation
• Query Tools: Generation, explanation, optimization, variations
• CLI & Library: Use as Python package or command-line tool
• Safe by Default: Validates queries before execution

📦 Installation

pip install pcybox-sqlbuddy

Requirements

• Python 3.8+
• MySQL or PostgreSQL database
• OpenAI API key or Anthropic API key

🚀 Quick Start

Python Library

from sqlbuddy import SQLBuddy

# Using context manager (recommended)
with SQLBuddy(
    db_type="mysql",
    host="localhost",
    user="root",
    password="your_password",
    database="your_db",
    llm_provider="openai",  # or "claude"
    api_key="your_api_key"
) as buddy:
    
    # Generate query from natural language
    result = buddy.generate_query("Show users registered in last 30 days")
    print(result["query"])
    # Output: SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    
    # Generate and execute in one call
    data = buddy.generate_and_execute("Top 10 customers by order value")
    print(data["execution"]["data"])

CLI Usage

# Test database connection
sqlbuddy test --host localhost --user root --database mydb

# Generate query
sqlbuddy generate "Show all active users" \
  --host localhost --database mydb

# Generate and execute
sqlbuddy generate "Count orders by status" --execute \
  --host localhost --database mydb

# Show database schema
sqlbuddy schema --host localhost --database mydb

# Explain query
sqlbuddy explain "SELECT * FROM users WHERE created_at > NOW() - INTERVAL 30 DAY"

# Optimize query
sqlbuddy optimize "SELECT * FROM users WHERE email LIKE '[e-mail]'"

🔧 Configuration

Using Environment Variables

Create a .env file in your project:

# Database Configuration
SQLBUDDY_DB_TYPE=mysql
SQLBUDDY_DB_HOST=localhost
SQLBUDDY_DB_PORT=3306
SQLBUDDY_DB_USER=root
SQLBUDDY_DB_PASSWORD=your_password
SQLBUDDY_DB_NAME=your_database

# LLM Configuration
OPENAI_API_KEY=sk-...
ANTHROPIC_API_KEY=sk-ant-...

Then use it:

from sqlbuddy import SQLBuddy
from dotenv import load_dotenv

load_dotenv()

# Environment variables are automatically loaded
buddy = SQLBuddy(llm_provider="openai")

📖 Usage Examples

1. Generate Multiple Query Variations

with SQLBuddy(database="sales_db") as buddy:
    variations = buddy.generate_multiple_queries(
        "Show sales performance by region",
        num_variations=3
    )
    
    for i, var in enumerate(variations, 1):
        print(f"\nApproach {i}:")
        print(var["query"])

2. Query Optimization

with SQLBuddy(database="analytics") as buddy:
    slow_query = "SELECT * FROM orders WHERE YEAR(created_at) = 2024"
    
    optimization = buddy.optimize_query(slow_query)
    
    print("Original:", optimization["original_query"])
    print("Optimized:", optimization["optimized_query"])
    print("\nDetails:", optimization["optimization_details"])

3. Query Explanation

with SQLBuddy(database="app_db") as buddy:
    query = """
    SELECT c.name, COUNT(o.id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
    HAVING order_count > 5
    """
    
    explanation = buddy.explain_query(query)
    print(explanation["analysis"])

4. Schema Exploration

with SQLBuddy(database="ecommerce") as buddy:
    # Get schema summary
    summary = buddy.get_schema_summary()
    print(f"Total Tables: {summary['total_tables']}")
    print(f"Total Relationships: {summary['total_relationships']}")
    
    # Get specific table info
    table_info = buddy.get_table_info("users")
    for col in table_info['columns']:
        print(f"  {col['name']}: {col['type']}")

5. Safe Query Execution

with SQLBuddy(database="app") as buddy:
    # Generate query
    result = buddy.generate_query("Delete inactive users")
    
    # Check if destructive
    if result["validation"]["is_destructive"]:
        print("⚠️ This is a destructive operation!")
        confirm = input("Continue? (yes/no): ")
        
        if confirm.lower() == "yes":
            exec_result = buddy.execute_query(
                result["query"],
                allow_destructive=True
            )
            print(f"Affected rows: {exec_result['row_count']}")

🛡️ Security Features

SQL Buddy includes multiple layers of security:

Automatic Validation

# All queries are validated by default
result = buddy.generate_query("Your description")

# Check validation results
if not result["validation"]["is_valid"]:
    print("Errors:", result["validation"]["errors"])
if result["validation"]["warnings"]:
    print("Warnings:", result["validation"]["warnings"])

Protected Operations

• SQL injection pattern detection
• Destructive operation blocking (DROP, TRUNCATE, DELETE without WHERE)
• Unbalanced parentheses/quotes detection
• Suspicious pattern alerts

Safe Execution

# Safe by default
buddy.execute_query(query)  # Validated automatically

# Destructive operations need explicit permission
buddy.execute_query(
    "DELETE FROM old_logs",
    allow_destructive=True  # Required for destructive ops
)

🖥️ CLI Reference

Commands

Command	Description	Example
generate	Generate SQL from natural language	sqlbuddy generate "Show active users"
execute	Execute SQL query	sqlbuddy execute "SELECT * FROM users"
schema	Show database schema	sqlbuddy schema --summary
explain	Explain SQL query	sqlbuddy explain "SELECT ..."
optimize	Optimize SQL query	sqlbuddy optimize "SELECT ..."
test	Test database connection	sqlbuddy test --host localhost

Global Options

--db-type [mysql|postgresql]   Database type (default: mysql)
--host HOST                    Database host
--port PORT                    Database port
--user USER                    Database user
--password PASSWORD            Database password
--database DATABASE            Database name
--llm-provider [openai|claude] LLM provider
--verbose, -v                  Verbose output

Use sqlbuddy COMMAND --help for command-specific options.

🤝 Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📄 License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Copyright 2025 Ibrahima Khalilou lahi SAMB

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

---

💼 Commercial Support

Need help integrating SQL Buddy into your project? Looking for custom features or enterprise support?

I offer:

• 🎯 Custom development and integration
• 🔒 Security audits and consulting
• 📚 Training and workshops
• ⚡ Priority support
• 🏢 Enterprise licensing

Contact us: [e-mail]

📞 Support & Community

• 🐛 Report a Bug
• 💡 Request a Feature
• 💬 Discussions
• 📧 Email: [e-mail]
• 🌐 Website: [Coming soon]

🙏 Acknowledgments

• Built with ❤️ by pcybox
• Inspired by the need for faster SQL development

---

⭐ Star History

If you find SQL Buddy helpful, please consider giving it a star on GitHub!

---

Made with ❤️ by IKS - Backend Development & Cybersecurity Solutions