moltres 0.18.0

DataFrame API with SQL pushdown execution and real SQL CRUD - the missing layer for SQL in Python

Moltres

The Missing DataFrame Layer for SQL in Python

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

Installation • Quick Start • Documentation • Examples

---

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. Update, insert, and delete with column-aware, type-safe operations.

✨ Features

• 🚀 PySpark-Style DataFrame API - Primary API with familiar operations (select, filter, join, groupBy, etc.) for seamless migration from PySpark
• 🐼 Optional Pandas-Style Interface - Comprehensive Pandas-like API with string accessor, query(), dtypes, shape, pivot, sample, concat, and more
• 🦀 Optional Polars-Style Interface - Polars LazyFrame-like API with expression-based operations, set operations, file I/O, CTEs, and more
• 🎯 98% PySpark API Compatibility - Near-complete compatibility for seamless migration
• 🗄️ SQL Pushdown Execution - All operations compile to SQL and run on your database—no data loading into memory
• ✏️ Real SQL CRUD - INSERT, UPDATE, DELETE operations with DataFrame-style syntax
• 📊 Multiple Formats - Read/write CSV, JSON, JSONL, Parquet, and more
• 🐼 Pandas & Polars Integration - Pass pandas/polars DataFrames directly to moltres operations
• 🌊 Streaming Support - Handle datasets larger than memory with chunked processing
• ⚡ Async Support - Full async/await support for all operations
• 🔒 Security First - Built-in SQL injection prevention and validation

📦 Installation

pip install moltres

# Optional: For async support
pip install moltres[async-postgresql]  # PostgreSQL
pip install moltres[async-mysql]       # MySQL
pip install moltres[async-sqlite]     # SQLite

# Optional: For pandas/polars result formats
pip install moltres[pandas,polars]

🚀 Quick Start

Basic DataFrame Operations

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 (SQL is compiled and executed here)
results = df.collect()  # Returns list of dicts by default

Pandas-Style Interface

df = db.table("users").pandas()

# Pandas-style operations
df[['id', 'name']]  # Select columns
df.query('age > 25 and country == "USA"')  # Query with AND/OR
df['name'].str.upper()  # String accessor
df.groupby('country').agg(age='mean')  # GroupBy

📚 See the Pandas Interface Guide →

Polars-Style Interface

df = db.table("users").polars()

# Polars-style operations
df.select("id", "name", (col("age") * 2).alias("double_age"))
df.filter((col("age") > 25) & (col("country") == "USA"))
df.group_by("country").agg(F.sum(col("age")))

📚 See the Polars Interface Guide →

CRUD Operations

from moltres.io.records import Records

# Insert rows
Records.from_list([
    {"id": 1, "name": "Alice", "email": "[e-mail]"},
    {"id": 2, "name": "Bob", "email": "[e-mail]"},
], 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())

📚 See CRUD Operations Guide →

📖 Documentation

Getting Started

• Getting Started Guide - Step-by-step introduction
• Examples Directory - 19 comprehensive example files
• Examples Guide - Common patterns and use cases

Interface Guides

• Pandas Interface - Complete pandas-style API reference
• Polars Interface - Complete Polars-style API reference
• PySpark Migration - Migrating from PySpark

Core Topics

• Reading Data - Tables, SQL, files
• Writing Data - Tables, files, formats
• Table Management - Create, drop, constraints
• Schema Inspection - Reflection and inspection
• Streaming - Large dataset handling
• Async Operations - Async/await support

Advanced Topics

• Performance Optimization - Query optimization and best practices
• Error Handling - Exception handling and debugging
• Best Practices - Production-ready patterns
• Advanced Topics - Window functions, CTEs, transactions

Reference

• Why Moltres? - Understanding the gap Moltres fills
• Security Guide - Security best practices
• Troubleshooting - Common issues and solutions
• API Reference - Complete API documentation

📚 Examples

Comprehensive examples demonstrating all Moltres features:

• 01_connecting.py - Database connections (sync and async)
• 02_dataframe_basics.py - Basic DataFrame operations
• 03_async_dataframe.py - Asynchronous operations
• 04_joins.py - Join operations
• 05_groupby.py - GroupBy and aggregation
• 06_expressions.py - Column expressions and functions
• 07_file_reading.py - Reading files (CSV, JSON, Parquet)
• 08_file_writing.py - Writing DataFrames to files
• 09_table_operations.py - Table operations and mutations
• 10_create_dataframe.py - Creating DataFrames from Python data
• 11_window_functions.py - Window functions
• 12_sql_operations.py - Raw SQL and SQL operations
• 13_transactions.py - Transaction management
• 14_reflection.py - Schema inspection and reflection
• 15_pandas_polars_dataframes.py - Pandas/Polars integration
• 16_ux_features.py - UX improvements
• 17_sqlalchemy_models.py - SQLAlchemy ORM integration
• 18_pandas_interface.py - Pandas-style interface examples
• 19_polars_interface.py - Polars-style interface examples

See the examples directory for all example files.

🛠️ Supported Operations

DataFrame Operations

• select() / selectExpr() - Project columns or SQL expressions
• where() / filter() - Filter rows
• join() - Join with other DataFrames
• group_by() / groupBy() - Group rows
• agg() - Aggregate functions
• order_by() / orderBy() / sort() - Sort rows
• limit() - Limit number of rows
• distinct() - Remove duplicate rows
• withColumn() - Add or rename columns
• pivot() - Pivot operations
• explode() - Explode array/JSON columns

Column Expressions

• Arithmetic: +, -, *, /, %
• Comparisons: ==, !=, <, >, <=, >=
• Boolean: &, |, ~
• Functions: 130+ functions including mathematical, string, date/time, aggregate, window, array, JSON, and utility functions
• Window Functions: over(), partition_by(), order_by() - Full PySpark compatibility

📚 See Expressions Guide →

Supported SQL Dialects

• ✅ SQLite - Full support
• ✅ PostgreSQL - Full support with dialect-specific optimizations
• ✅ MySQL - Full support with dialect-specific optimizations
• ✅ DuckDB - Full support with PostgreSQL-compatible optimizations
• ✅ Other SQLAlchemy-supported databases - ANSI SQL fallback

🧪 Development

Setup

# Clone the repository
git clone https://github.com/eddiethedean/moltres.git
cd moltres

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

# Install pre-commit hooks
pre-commit install

Running Tests

# Run all tests
pytest

# Run tests in parallel
pytest -n 9

# Run with coverage
pytest --cov=src/moltres --cov-report=html

Code Quality

# Linting
ruff check .

# Formatting
ruff format .

# Type checking (strict mode enabled)
mypy src

Pre-Commit CI Checks

# Run all CI checks (linting, type checking, tests)
make ci-check

# Quick linting check only
make ci-check-lint

🤝 Contributing

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

Quick Start:

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

Before submitting:

• Run tests: pytest
• Check code quality: ruff check . && mypy src
• Update documentation if needed

👤 Author

Odos Matthews

• GitHub: @eddiethedean
• Email: [e-mail]

🙏 Acknowledgments

• Inspired by PySpark's DataFrame API style, but focused on SQL feature support rather than PySpark feature parity
• Built on SQLAlchemy for database connectivity and SQL compilation
• Thanks to all contributors and users

📄 License

MIT License - see LICENSE file for details.

---

Made with ❤️ for the Python data community

⬆ Back to Top