Moltres

The Missing DataFrame Layer for SQL in Python

MOLTRES: Modern Operations Layer for Transformations, Relational Execution, and SQL

---

Moltres combines a DataFrame API (like Pandas/Polars), SQL pushdown execution (no data loading into memory), and real SQL CRUD operations (INSERT, UPDATE, DELETE) in one unified interface.

Transform millions of rows using familiar DataFrame operations—all executed directly in SQL without materializing data.

✨ Key Features

• 🚀 PySpark-Style DataFrame API - Primary API with 98% PySpark compatibility
• 🗄️ SQL Pushdown Execution - All operations compile to SQL and run on your database
• ✏️ Real SQL CRUD - INSERT, UPDATE, DELETE with DataFrame-style syntax
• 🐼 Pandas & Polars Interfaces - Optional pandas/polars-style APIs
• ⚡ Async Support - Full async/await support for all operations
• 🔒 Security First - Built-in SQL injection prevention
• 🎯 Framework Integrations - FastAPI, Django, Streamlit, SQLModel, Pydantic

📦 Installation

pip install moltres

# Optional extras
pip install moltres[async-postgresql]  # Async PostgreSQL
pip install moltres[pandas,polars]     # Pandas/Polars result formats
pip install moltres[sqlmodel]          # SQLModel/Pydantic integration
pip install moltres[streamlit]        # Streamlit integration

🚀 Quick Start

from moltres import col, connect
from moltres.expressions import functions as F

# Connect to your database
db = connect("sqlite:///example.db")

# DataFrame operations with SQL pushdown (no data loading into memory)
df = (
    db.table("orders")
    .select()
    .join(db.table("customers").select(), on=[col("orders.customer_id") == col("customers.id")])
    .where(col("active") == True)
    .group_by("country")
    .agg(F.sum(col("amount")).alias("total_amount"))
)

# Execute and get results
results = df.collect()  # Returns list of dicts by default

CRUD Operations

from moltres.io.records import Records

# Insert rows
Records.from_list([
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"},
], database=db).insert_into("users")

# Update rows
db.update("users", where=col("active") == 0, set={"active": 1})

# Delete rows
db.delete("users", where=col("email").is_null())

📖 Documentation

• Getting Started Guide - Step-by-step introduction
• Examples Directory - 29 comprehensive examples
• User Guides - Complete guides for all features
• API Reference - Complete API documentation

Framework Integrations

• FastAPI Integration - Error handling, dependency injection
• Django Integration - Middleware, template tags, management commands
• Streamlit Integration - Components, caching, query visualization
• SQLModel & Pydantic - Type-safe models

🛠️ Supported Operations

DataFrame Operations: select(), where(), join(), group_by(), agg(), order_by(), limit(), distinct(), pivot(), and more

130+ Functions: Mathematical, string, date/time, aggregate, window, array, JSON, and utility functions

SQL Dialects: SQLite, PostgreSQL, MySQL, DuckDB, and any SQLAlchemy-supported database

UX Features: Enhanced SQL display (show_sql(), sql property), query plan visualization (plan_summary(), visualize_plan()), schema discovery (db.schema(), db.tables()), query validation (validate()), performance hints (performance_hints()), and interactive help (help(), suggest_next())

🧪 Development

# Install in development mode
pip install -e ".[dev]"

# Run tests
pytest

# Code quality
ruff check . && ruff format . && mypy src

🤝 Contributing

Contributions are welcome! See CONTRIBUTING.md for guidelines.

📄 License

MIT License - see LICENSE file for details.

---

Made with ❤️ for the Python data community

⬆ Back to Top