<<<<<<< HEAD

woo-db-agent

An A.I Agent responsible for Generating and executing SQL queries for WooCommerce using Google Gemini API as LLM

🛍️ WooCommerce Gemini Query Generator

Generate and execute SQL queries for WooCommerce stores using natural language, powered by Google Gemini AI.

✨ Features

• 🤖 Natural Language to SQL - Convert plain English queries to SQL
• 🛍️ WooCommerce-Specific - Understands WooCommerce database schema
• 📊 Beautiful Output - Results displayed in formatted tables
• ✏️ Full CRUD Support - SELECT, UPDATE, INSERT, DELETE queries
• 🔒 Safe Transactions - Automatic commit/rollback handling
• 🎯 CLI & Library - Use as command-line tool or Python library

🚀 Installation

pip install woo-db-agent

📋 Requirements

• Python 3.8 or higher
• MySQL database with WooCommerce
• Google Gemini API key (Get one here)

🎯 Quick Start

As a Command-Line Tool

# Set environment variables (recommended)
export GEMINI_API_KEY="your-gemini-api-key"
export DB_HOST="localhost"
export DB_NAME="your_wordpress_db"
export DB_USER="your_db_user"
export DB_PASSWORD="your_db_password"

# Run the CLI
woo-gemini-query

As a Python Library

from woodbagent import WooCommerceQueryGenerator, execute_query

# Initialize the generator
generator = WooCommerceQueryGenerator(api_key="your-gemini-api-key")

# Generate a query from natural language
sql = generator.generate_query("Show all products under $50")
print(sql)

# Execute the query
result = execute_query(
    sql_query=sql,
    host="localhost",
    database="wordpress_db",
    user="db_user",
    password="db_password"
)

# Display results
if result['success']:
    print(f"Found {result['row_count']} products")
    for row in result['data']:
        print(row)

💡 Usage Examples

Example Queries

from woo-db-agent import WooCommerceQueryGenerator, execute_query, display_results

generator = WooCommerceQueryGenerator(api_key="your-api-key")

# Query 1: Find products by price
query = generator.generate_query("Show all products priced between $20 and $100")
result = execute_query(query, host="localhost", database="mydb", user="root", password="pass")
display_results(result)

# Query 2: Check inventory
query = generator.generate_query("List all out of stock products")
result = execute_query(query, host="localhost", database="mydb", user="root", password="pass")
display_results(result)

# Query 3: Update prices
query = generator.generate_query("Increase price by 10% for products in Electronics category")
result = execute_query(query, host="localhost", database="mydb", user="root", password="pass")
display_results(result)

Interactive CLI Session

🛍️  What would you like to know about your products?
➤  Show me the top 10 best selling products

⏳ Generating SQL query...

📝 Generated SQL Query:
----------------------------------------------------------------------
SELECT p.ID, p.post_title, SUM(oi.order_item_qty) as total_sold
FROM wp_posts p
INNER JOIN wp_order_itemmeta oim ON p.ID = oim.meta_value
...
----------------------------------------------------------------------

✅ Query executed successfully!

📊 Results (10 rows):
╔════╦═══════════════════╦════════════╗
║ ID ║ post_title        ║ total_sold ║
╠════╬═══════════════════╬════════════╣
║ 23 ║ Wireless Mouse    ║ 156        ║
║ 45 ║ USB Cable         ║ 142        ║
...

🔧 API Reference

WooCommerceQueryGenerator

generator = WooCommerceQueryGenerator(api_key, model="gemini-2.5-flash")

Methods:

• generate_query(user_prompt: str) -> str - Generate SQL from natural language
• set_custom_schema(schema_context: str) - Override default schema

execute_query

result = execute_query(
    sql_query: str,
    host: str,
    database: str,
    user: str,
    password: str,
    port: int = 3306
)

Returns: Dictionary with:

• success: Boolean
• data: List of tuples (for SELECT queries)
• columns: List of column names
• row_count: Number of rows
• query_type: SELECT, UPDATE, INSERT, DELETE
• error: Error message (if any)

display_results

display_results(result: Dict)

Formats and prints query results in a table.

🔐 Environment Variables

Variable	Description	Required
GEMINI_API_KEY	Your Google Gemini API key	Yes
DB_HOST	Database host	No (default: localhost)
DB_NAME	Database name	Yes
DB_USER	Database username	Yes
DB_PASSWORD	Database password	Yes
DB_PORT	Database port	No (default: 3306)

🛡️ Security Best Practices

1. Never hardcode credentials - Use environment variables
2. Use .env files for local development
3. Restrict database permissions - Use read-only user for SELECT queries
4. Review generated queries - Always check before executing modifications
5. Backup your database - Before running UPDATE/DELETE queries

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

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

📝 License

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

🙏 Acknowledgments

• Powered by Google Gemini AI

4f3d100 (Initial commit)