moltres 1.1.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

---

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. See why Moltres and the comparison guides for how it differs from Pandas, Ibis, and PySpark.

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

✨ Key Features

• 🚀 PySpark-Style DataFrame API - High compatibility for core operations; see migration footguns
• 🗄️ 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

# Common optional extras
pip install moltres[pandas,polars]     # Pandas/Polars result formats
pip install moltres[async-postgresql]  # Async PostgreSQL
pip install moltres[parquet,duckdb,fastapi]  # File I/O, DuckDB, FastAPI helpers

# Full extras table: docs/PUBLIC_API.md#optional-extras

moltres-core and pydantable

SQL execution lives in the companion moltres-core package. You can use MoltresPydantableEngine with pydantable for a typed, plan-driven API backed by SQL for supported operations. See docs/PYDANTABLE_ENGINE.md. From source, install moltres-core before moltres:

pip install -e ./moltres-core
pip install -e .

1.1.0 ships this split on PyPI: pip install moltres pulls in moltres-core automatically. For breaking changes and upgrade notes, see CHANGELOG.md.

Prerequisites

• Python 3.10+ (see Runtime support)
• SQLAlchemy 2.0+ (installed automatically with moltres)
• Database driver for your backend (e.g. psycopg2-binary for PostgreSQL, pymysql for MySQL; SQLite needs no extra driver)
• Optional extras: full list in Public API — Optional extras

🚀 Quick Start

New here? Start with the 5-minute quick start, then the complete tutorial when you want more depth.

from moltres import col, connect
from moltres.expressions import functions as F
from moltres.io.records import Records
from moltres.table.schema import column

with connect("sqlite:///:memory:") as db:
    db.create_table("orders", [
        column("id", "INTEGER"),
        column("country", "TEXT"),
        column("amount", "REAL"),
    ]).collect()
    Records.from_list([
        {"id": 1, "country": "US", "amount": 100.0},
        {"id": 2, "country": "UK", "amount": 200.0},
    ], database=db).insert_into("orders")

    df = (
        db.table("orders").select()
        .where(col("country") == "US")
        .group_by("country")
        .agg(F.sum(col("amount")).alias("total_amount"))
    )
    print(df.collect())  # [{'country': 'US', 'total_amount': 100.0}]

    # CRUD: update and delete rows
    db.update("orders", where=col("country") == "US", set={"amount": 150.0})
    db.delete("orders", where=col("amount") < 50)

For a fuller CRUD walkthrough (separate tables, Records, merge), see the complete tutorial.

Reading Data: Tables vs Files

Goal	API	Returns
Query a SQL table lazily	db.table("orders").select()	DataFrame (SQL pushdown)
Load a file as a lazy DataFrame	db.load.csv("data.csv")	DataFrame (materialized via temp table)
Load a file as in-memory rows	db.read.records.csv("data.csv")	Records (eager, for inserts)

See Public API guide for stable import paths.

📖 Documentation

• Roadmap - Future 1.x release phases and competitive priorities

• Public API - Stable imports and I/O patterns

• Getting Started Guide - 5-minute quick start (start here)

• Complete Tutorial - Full step-by-step introduction

• Examples - Runnable example scripts

• User Guides - Complete guides for all features

• API Reference - Complete API documentation

Framework Integrations

• FastAPI Integration - See docs/examples/22_fastapi_integration.py
• Django Integration
• Streamlit Integration
• 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, and DuckDB are CI-tested; other SQLAlchemy-supported databases are best-effort (see Runtime support)

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

From a git checkout, install moltres-core before moltres (the monorepo ships two packages):

pip install -e ./moltres-core
pip install -e ".[dev]"

# Run lint/type/doc-example checks (does NOT run the test suite)
make ci-check

# Run tests (matches CI main matrix)
PYTEST_DISABLE_PLUGIN_AUTOLOAD=1 pytest -p pytest_asyncio.plugin -p xdist.plugin \
  -m "not postgres and not mysql and not multidb and not tier2_integration and not tier3_integration" \
  -n auto --dist loadgroup

🤝 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