intelligentsql 1.0.0
High-level MySQL library - Simple to use, highly advanced, incredibly productive
License: MIT
Requires Python: >=3.8
Keywords: mysql, database, sql, orm, export, import, csv, excel, json, intelligentsql, productivity

# IntelligentSQL - Advanced MySQL Database Management Library

[![Python 3.8+](https://img.shields.io/badge/python-3.8+-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Made by TechEd's Lab](https://img.shields.io/badge/Made%20by-TechEd's%20Lab-orange.svg)](https://github.com/TechEd-SI)

**IntelligentSQL** is a high-level, production-ready Python library that makes MySQL database management incredibly simple yet powerfully advanced. Write database operations in plain English syntax while leveraging enterprise-grade features under the hood.

**Created with ❤️ by TechEd's Lab (TechEd-SI)**

## 🎯 Why IntelligentSQL?

IntelligentSQL transforms complex database operations into simple, readable Python code. Whether you're a beginner or an expert, IntelligentSQL adapts to your needs:

- **For Beginners**: English-like syntax that reads like natural language


## 🌟 What Makes IntelligentSQL Special?

IntelligentSQL is designed around three core principles:

### 1. **High-Level Abstraction**
Instead of writing raw SQL strings, use intuitive Python methods:
```python
# Traditional way (verbose, error-prone)
cursor.execute("SELECT * FROM users WHERE age > %s ORDER BY name LIMIT 10", (25,))

# IntelligentSQL way (clear, safe, simple)
users = db.select('users', where='age > 25', order_by='name', limit=10)
```

### 2. **Production-Grade Power**
Simple syntax doesn't mean limited features. IntelligentSQL includes:
- Full transaction management with rollback
- Complex multi-table joins
- Advanced aggregations and analytics
- Batch operations for performance
- Index management
- Database backups

### 3. **Developer Productivity**
Built for speed and efficiency:
- One-line exports to Excel/CSV/JSON
- Automatic Excel styling
- Context managers for safety
- Intelligent error messages
- Type hints for IDE support


- **For Professionals**: Advanced features like transactions, joins, aggregations, and batch operations
- **For Everyone**: One-line data export/import, automatic styling, and built-in safety

**Simple to use. Highly advanced. Incredibly productive.**

## ✨ Features

### 🎯 High-Level Simplicity
- **Plain English Syntax** - Write database code that reads like sentences
- **One-Line Operations** - Complex tasks in a single command
- **Intelligent Defaults** - Smart behavior that just works
- **Context Management** - Automatic connection handling

### 💪 Advanced Functionality
- **Full MySQL Support** - Every SQL command at your fingertips
- **Production-Ready** - Enterprise-grade transaction management
- **High Performance** - Intelligent batch operations for speed
- **Advanced Joins** - Complex multi-table queries made simple
- **Powerful Aggregations** - Built-in analytics functions

### 📊 Data Integration
- **Export Anywhere** - CSV, Excel, JSON in one line
- **Import Everything** - Seamless data import from any format
- **Beautiful Exports** - Auto-styled Excel with professional formatting
- **Pandas Integration** - Direct DataFrame conversion

### 🛡️ Safety & Security
- **Built-in Protection** - Required WHERE clauses for UPDATE/DELETE
- **Parameterized Queries** - Automatic SQL injection prevention
- **Transaction Rollback** - Safe error recovery
- **Backup Utilities** - One-command table backups

### ⚡ Developer Experience
- **Zero Configuration** - Works out of the box
- **Type Hints** - Full IDE autocomplete support
- **Rich Documentation** - Examples for everything
- **Clear Error Messages** - Know exactly what went wrong

## 📦 Installation

```bash
pip install intelligentsql
```

### Dependencies

IntelligentSQL requires the following packages:
- mysql-connector-python
- pandas
- openpyxl

These will be automatically installed with IntelligentSQL.

## 🚀 Quick Start

```python
from intelligentsql import IntelligentSQL

# Connect to MySQL
db = IntelligentSQL(host='localhost', user='root', password='your_password')

# Create and use database
db.create_database('myapp')
db.use_database('myapp')

# Create a table
db.create_table('users', {
    'id': 'INT AUTO_INCREMENT',
    'name': 'VARCHAR(100)',
    'email': 'VARCHAR(255) UNIQUE',
    'age': 'INT',
    'created_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
}, primary_key='id')

# Insert data
db.insert('users', {
    'name': 'John Doe',
    'email': 'john@example.com',
    'age': 30
})

# Query data
users = db.select('users', where='age > 25', order_by='name')

# Export to Excel
db.to_excel('users', 'users.xlsx')

# Close connection
db.close()
```

## 📖 Complete Documentation

### Connection

#### Basic Connection

```python
from intelligentsql import IntelligentSQL

# Connect to MySQL server
db = IntelligentSQL(
    host='localhost',
    user='root',
    password='your_password',
    database='mydb',  # optional
    port=3306,
    autocommit=True
)
```

#### Quick Connect Function

```python
import intelligentsql

db = intelligentsql.connect(host='localhost', user='root', password='pass', database='mydb')
```

#### Context Manager (Recommended)

```python
with IntelligentSQL(host='localhost', user='root', password='pass') as db:
    # Your database operations
    users = db.select('users')
# Connection automatically closed
```

### Database Operations

#### Create Database

```python
db.create_database('myapp')
db.create_database('myapp', if_not_exists=True)
```

#### Drop Database

```python
db.drop_database('old_db')
db.drop_database('old_db', if_exists=True)
```

#### Show Databases

```python
databases = db.show_databases()
print(databases)  # ['mysql', 'myapp', 'information_schema', ...]
```

#### Use Database

```python
db.use_database('myapp')
```

#### Check if Database Exists

```python
if db.database_exists('myapp'):
    print("Database exists")
```

### Table Operations

#### Create Table

```python
# Simple table
db.create_table('users', {
    'id': 'INT AUTO_INCREMENT',
    'name': 'VARCHAR(100)',
    'email': 'VARCHAR(255)'
}, primary_key='id')

# Table with foreign keys
db.create_table('orders', {
    'id': 'INT AUTO_INCREMENT',
    'user_id': 'INT',
    'amount': 'DECIMAL(10,2)',
    'created_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
}, 
primary_key='id',
foreign_keys=[{
    'column': 'user_id',
    'ref_table': 'users',
    'ref_column': 'id',
    'on_delete': 'CASCADE',
    'on_update': 'CASCADE'
}])
```

#### Drop Table

```python
db.drop_table('old_table')
db.drop_table('old_table', if_exists=True)
```

#### Truncate Table

```python
db.truncate_table('users')  # Removes all rows
```

#### Show Tables

```python
tables = db.show_tables()
print(tables)  # ['users', 'orders', 'products', ...]
```

#### Describe Table

```python
structure = db.describe_table('users')
for column in structure:
    print(f"{column['Field']}: {column['Type']}")
```

#### Alter Table Operations

```python
# Add column
db.alter_table_add_column('users', 'phone', 'VARCHAR(20)')

# Drop column
db.alter_table_drop_column('users', 'phone')

# Modify column
db.alter_table_modify_column('users', 'name', 'VARCHAR(200) NOT NULL')

# Rename column
db.alter_table_rename_column('users', 'name', 'full_name', 'VARCHAR(200)')

# Rename table
db.rename_table('old_users', 'users')
```

#### Check if Table Exists

```python
if db.table_exists('users'):
    print("Table exists")
```

### CRUD Operations

#### Insert Data

```python
# Insert single row
user_id = db.insert('users', {
    'name': 'John Doe',
    'email': 'john@example.com',
    'age': 30
})

# Insert multiple rows
db.insert('users', [
    {'name': 'Jane Smith', 'email': 'jane@example.com', 'age': 25},
    {'name': 'Bob Johnson', 'email': 'bob@example.com', 'age': 35}
])

# Insert with IGNORE
db.insert('users', {'email': 'john@example.com'}, ignore=True)

# Insert with ON DUPLICATE KEY UPDATE
db.insert('users', {'email': 'john@example.com', 'age': 31}, on_duplicate_update=True)
```

#### Select Data

```python
# Select all
users = db.select('users')

# Select specific columns
users = db.select('users', columns=['name', 'email'])

# Select with WHERE clause
users = db.select('users', where='age > 25')

# Select with ORDER BY
users = db.select('users', order_by='name ASC')

# Select with LIMIT and OFFSET
users = db.select('users', limit=10, offset=20)

# Select with GROUP BY and HAVING
users = db.select('users', 
                 columns=['age', 'COUNT(*) as count'],
                 group_by='age',
                 having='COUNT(*) > 1')

# Complex query
users = db.select('users',
                 columns=['name', 'email', 'age'],
                 where='age > 18 AND status = "active"',
                 order_by='created_at DESC',
                 limit=50)
```

#### Update Data

```python
# Update single row
affected = db.update('users', 
                    {'age': 31, 'updated_at': 'NOW()'},
                    where='id = 1')

# Update multiple rows
db.update('users', {'status': 'inactive'}, where='age > 65')

# Update all rows (use with caution)
db.update('users', {'verified': True}, where='1=1')
```

#### Delete Data

```python
# Delete specific rows
deleted = db.delete('users', where='id = 1')

# Delete with conditions
db.delete('users', where='age < 18')

# Delete all rows (use with caution)
db.delete('users', where='1=1')
```

### Advanced Queries

#### Join Operations

```python
# INNER JOIN
results = db.join(
    tables=['users', 'orders'],
    columns=['users.name', 'users.email', 'orders.total', 'orders.created_at'],
    join_conditions=['users.id = orders.user_id'],
    join_type='INNER'
)

# LEFT JOIN with WHERE
results = db.join(
    tables=['users', 'orders'],
    columns=['users.name', 'SUM(orders.total) as total_spent'],
    join_conditions=['users.id = orders.user_id'],
    join_type='LEFT',
    where='orders.status = "completed"'
)

# Multiple joins
results = db.join(
    tables=['users', 'orders', 'products'],
    columns=['users.name', 'products.title', 'orders.quantity'],
    join_conditions=[
        'users.id = orders.user_id',
        'products.id = orders.product_id'
    ],
    join_type='INNER'
)
```

#### Aggregate Functions

```python
# Single aggregation
stats = db.aggregate('orders', {
    'total_sales': 'SUM(amount)',
    'avg_order': 'AVG(amount)',
    'max_order': 'MAX(amount)',
    'min_order': 'MIN(amount)',
    'order_count': 'COUNT(*)'
})

# Group by aggregation
user_stats = db.aggregate('orders',
    aggregations={
        'total_spent': 'SUM(amount)',
        'order_count': 'COUNT(*)',
        'avg_order': 'AVG(amount)'
    },
    group_by='user_id',
    having='SUM(amount) > 1000'
)

# With WHERE clause
stats = db.aggregate('orders',
    aggregations={'revenue': 'SUM(amount)'},
    where='status = "completed"',
    group_by='DATE(created_at)'
)
```

### Index Management

#### Create Index

```python
# Simple index
db.create_index('users', 'idx_email', 'email')

# Unique index
db.create_index('users', 'idx_username', 'username', unique=True)

# Composite index
db.create_index('users', 'idx_name_age', ['name', 'age'])
```

#### Drop Index

```python
db.drop_index('users', 'idx_email')
```

#### Show Indexes

```python
indexes = db.show_indexes('users')
for idx in indexes:
    print(f"{idx['Key_name']}: {idx['Column_name']}")
```

### Transaction Management

```python
# Manual transaction control
db.begin_transaction()

try:
    db.insert('accounts', {'user_id': 1, 'balance': 1000})
    db.update('users', {'account_created': True}, where='id = 1')
    db.commit()
except Exception as e:
    db.rollback()
    print(f"Transaction failed: {e}")

# Or use autocommit=False in connection
db = intelligentsql(host='localhost', user='root', password='pass', autocommit=False)
# Now all operations are in transactions until you call commit()
```

### Export Operations

#### Export to CSV

```python
# Export entire table
db.to_csv('users', 'users.csv')

# Export query results
db.to_csv('SELECT * FROM users WHERE age > 25', 'adult_users.csv', is_query=True)

# Custom delimiter
db.to_csv('users', 'users.tsv', delimiter='\t')
```

#### Export to Excel

```python
# Basic export
db.to_excel('users', 'users.xlsx')

# With custom sheet name
db.to_excel('users', 'report.xlsx', sheet_name='Active Users')

# Export query results with styling
db.to_excel('SELECT * FROM users ORDER BY created_at DESC', 
           'latest_users.xlsx', 
           is_query=True,
           styled=True)

# Without styling
db.to_excel('users', 'users.xlsx', styled=False)
```

#### Export to JSON

```python
# Export table
db.to_json('users', 'users.json')

# Export query
db.to_json('SELECT name, email FROM users', 'users_minimal.json', is_query=True)

# Compact JSON (no indentation)
db.to_json('users', 'users_compact.json', indent=None)
```

#### Export to DataFrame

```python
import pandas as pd

# Get pandas DataFrame
df = db.to_dataframe('users')

# From query
df = db.to_dataframe('SELECT * FROM users WHERE age > 25', is_query=True)

# Now you can use all pandas features
print(df.describe())
df.plot()
```

### Import Operations

#### Import from CSV

```python
# Basic import
db.from_csv('users', 'import_users.csv')

# Custom delimiter and batch size
db.from_csv('users', 'data.tsv', delimiter='\t', batch_size=500)
```

#### Import from Excel

```python
# Import from first sheet
db.from_excel('users', 'users.xlsx')

# Import from specific sheet
db.from_excel('users', 'data.xlsx', sheet_name='Sheet2')

# Import from sheet by index
db.from_excel('users', 'data.xlsx', sheet_name=1)
```

#### Import from JSON

```python
# Import JSON array
db.from_json('users', 'users.json')

# With custom batch size
db.from_json('users', 'large_data.json', batch_size=500)
```

### Utility Operations

#### Execute Raw SQL

```python
# Execute any SQL query
results = db.execute("SELECT * FROM users WHERE email LIKE '%@gmail.com'")

# Execute with parameters (prevents SQL injection)
results = db.execute("SELECT * FROM users WHERE age > %s AND status = %s", (25, 'active'))

# Execute non-SELECT queries
db.execute("UPDATE users SET last_login = NOW() WHERE id = 1")
```

#### Execute Batch Queries

```python
# Insert multiple rows efficiently
query = "INSERT INTO users (name, email) VALUES (%s, %s)"
data = [
    ('User 1', 'user1@example.com'),
    ('User 2', 'user2@example.com'),
    ('User 3', 'user3@example.com')
]
affected = db.executemany(query, data)
```

#### Get Row Count

```python
# Total rows
count = db.get_row_count('users')

# With condition
active_count = db.get_row_count('users', where='status = "active"')
```

#### Backup Table

```python
# Auto-generated backup name (table_backup_timestamp)
db.backup_table('users')

# Custom backup name
db.backup_table('users', 'users_backup_2024')
```

#### Reconnect

```python
# Reconnect if connection is lost
db.reconnect()
```

## 🎯 Real-World Examples

### User Management System

```python
from intelligentsql import IntelligentSQL

with IntelligentSQL(host='localhost', user='root', password='pass') as db:
    # Setup
    db.create_database('user_system')
    db.use_database('user_system')

    # Create users table
    db.create_table('users', {
        'id': 'INT AUTO_INCREMENT',
        'username': 'VARCHAR(50) UNIQUE NOT NULL',
        'email': 'VARCHAR(255) UNIQUE NOT NULL',
        'password_hash': 'VARCHAR(255) NOT NULL',
        'is_active': 'BOOLEAN DEFAULT TRUE',
        'created_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
        'updated_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
    }, primary_key='id')

    # Create index for faster lookups
    db.create_index('users', 'idx_email', 'email')
    db.create_index('users', 'idx_username', 'username', unique=True)

    # Register users
    db.insert('users', [
        {'username': 'john_doe', 'email': 'john@example.com', 'password_hash': 'hash1'},
        {'username': 'jane_smith', 'email': 'jane@example.com', 'password_hash': 'hash2'}
    ])

    # Get active users
    active_users = db.select('users', where='is_active = TRUE', order_by='created_at DESC')

    # Export user list
    db.to_excel('users', 'user_report.xlsx')
```

### E-commerce Order System

```python
from intelligentsql import IntelligentSQL

with IntelligentSQL(host='localhost', user='root', password='pass', database='shop') as db:

    # Create orders table
    db.create_table('orders', {
        'id': 'INT AUTO_INCREMENT',
        'user_id': 'INT NOT NULL',
        'product_id': 'INT NOT NULL',
        'quantity': 'INT NOT NULL',
        'price': 'DECIMAL(10,2) NOT NULL',
        'status': 'ENUM("pending", "processing", "shipped", "delivered") DEFAULT "pending"',
        'created_at': 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
    }, 
    primary_key='id',
    foreign_keys=[
        {'column': 'user_id', 'ref_table': 'users', 'ref_column': 'id'},
        {'column': 'product_id', 'ref_table': 'products', 'ref_column': 'id'}
    ])

    # Get sales report
    sales_report = db.aggregate('orders',
        aggregations={
            'total_revenue': 'SUM(price * quantity)',
            'total_orders': 'COUNT(*)',
            'avg_order_value': 'AVG(price * quantity)'
        },
        where='status = "delivered"',
        group_by='DATE(created_at)'
    )

    # Export to Excel
    db.to_excel('SELECT * FROM orders WHERE status = "delivered"',
                'delivered_orders.xlsx',
                is_query=True,
                styled=True)

    # Get user order history with join
    user_orders = db.join(
        tables=['users', 'orders', 'products'],
        columns=['users.username', 'products.name', 'orders.quantity', 'orders.price', 'orders.status'],
        join_conditions=[
            'users.id = orders.user_id',
            'products.id = orders.product_id'
        ],
        where='users.id = 1',
        order_by='orders.created_at DESC'
    )
```

### Data Migration

```python
from intelligentsql import IntelligentSQL

# Source database
source = IntelligentSQL(host='old-server.com', user='root', password='pass', database='old_db')

# Destination database
dest = IntelligentSQL(host='new-server.com', user='root', password='pass', database='new_db')

# Export from source
source.to_csv('users', 'migration_users.csv')
source.to_csv('orders', 'migration_orders.csv')

# Import to destination
dest.from_csv('users', 'migration_users.csv')
dest.from_csv('orders', 'migration_orders.csv')

source.close()
dest.close()
```

## 🔒 Best Practices

### 1. Always Use Context Managers

```python
# Good
with IntelligentSQL(host='localhost', user='root', password='pass') as db:
    users = db.select('users')

# Avoid
db = IntelligentSQL(host='localhost', user='root', password='pass')
users = db.select('users')
db.close()  # Easy to forget
```

### 2. Use Parameterized Queries

```python
# Good - prevents SQL injection
db.execute("SELECT * FROM users WHERE email = %s", (user_email,))

# Avoid - vulnerable to SQL injection
db.execute(f"SELECT * FROM users WHERE email = '{user_email}'")
```

### 3. Always Use WHERE Clause for UPDATE/DELETE

```python
# Library requires WHERE clause for safety
db.update('users', {'status': 'inactive'}, where='last_login < "2020-01-01"')

# To update all rows, explicitly use:
db.update('users', {'migrated': True}, where='1=1')
```

### 4. Use Transactions for Critical Operations

```python
db = IntelligentSQL(host='localhost', user='root', password='pass', autocommit=False)

try:
    db.insert('accounts', {'user_id': 1, 'balance': 1000})
    db.update('users', {'has_account': True}, where='id = 1')
    db.commit()
except Exception as e:
    db.rollback()
    raise e
```

### 5. Create Indexes for Performance

```python
# Index frequently queried columns
db.create_index('users', 'idx_email', 'email')
db.create_index('orders', 'idx_user_created', ['user_id', 'created_at'])
```

## 🐛 Error Handling

```python
from intelligentsql import IntelligentSQL

try:
    with IntelligentSQL(host='localhost', user='root', password='wrong') as db:
        users = db.select('users')
except Exception as e:
    print(f"Database error: {e}")
```

## 📝 License

MIT License - feel free to use in your projects!

## 🤝 Contributing

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

## 📧 Support

For issues and questions, please open an issue on GitHub.

## 🚀 Coming Soon

- PostgreSQL support
- SQLite support
- MongoDB support
- Migration tools
- Advanced query builder
- ORM features
- Real-time data sync
- Cloud database integration

---

**Made with ❤️ by TechEd's Lab (TechEd-SI)**

*Empowering developers to build better applications faster*

### About TechEd's Lab

TechEd's Lab (TechEd-SI) creates developer tools that combine simplicity with power. Our mission is to make complex technologies accessible to everyone while maintaining professional-grade capabilities.

**Connect with us:**
- 🌐 **Website**: [intelligentsql.onrender.com](https://intelligentsql.onrender.com)
- 💬 **Discord**: [Join our community](https://discord.gg/HcxKyW7qCq)
- 🐦 **Twitter/X**: [@techedsi](https://x.com/techedsi)
- 💻 **GitHub**: [@teched-si-official](https://github.com/teched-si-official/intelligentsql)

### Support the Project

If IntelligentSQL makes your life easier, consider:
- ⭐ Starring the repository on [GitHub](https://github.com/teched-si-official/intelligentsql)
- 💬 Joining our [Discord community](https://discord.gg/HcxKyW7qCq)
- 🐛 Reporting bugs on [GitHub Issues](https://github.com/teched-si-official/intelligentsql/issues)
- 💡 Suggesting features
- 🤝 Contributing code

---

*IntelligentSQL - Making Database Management Simple, Powerful, and Fun*