Metadata-Version: 2.4
Name: sqlalchemy-excel
Version: 0.4.0
Summary: SQLAlchemy dialect for Excel files — use Excel as a database
Project-URL: Homepage, https://github.com/yeongseon/sqlalchemy-excel
Project-URL: Repository, https://github.com/yeongseon/sqlalchemy-excel
Project-URL: Issues, https://github.com/yeongseon/sqlalchemy-excel/issues
Project-URL: Changelog, https://github.com/yeongseon/sqlalchemy-excel/blob/main/CHANGELOG.md
Author: Yeongseon Choe
License: MIT
License-File: LICENSE
Keywords: database,dialect,excel,openpyxl,sqlalchemy
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Office/Business :: Financial :: Spreadsheet
Classifier: Typing :: Typed
Requires-Python: >=3.10
Requires-Dist: excel-dbapi>=0.2.0
Requires-Dist: sqlalchemy>=2.0
Provides-Extra: dev
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pytest-cov>=4.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.4; extra == 'dev'
Provides-Extra: graph
Requires-Dist: excel-dbapi[graph]>=0.2.0; extra == 'graph'
Description-Content-Type: text/markdown

<p align="left">
  <img src="https://raw.githubusercontent.com/yeongseon/sqlalchemy-excel/main/logo.svg" alt="sqlalchemy-excel" width="48" height="48" align="middle" />
  <strong style="font-size: 2em;">sqlalchemy-excel</strong>
</p>

![CI](https://github.com/yeongseon/sqlalchemy-excel/actions/workflows/ci.yml/badge.svg)
[![codecov](https://codecov.io/gh/yeongseon/sqlalchemy-excel/branch/main/graph/badge.svg)](https://codecov.io/gh/yeongseon/sqlalchemy-excel)
[![PyPI](https://img.shields.io/pypi/v/sqlalchemy-excel.svg)](https://pypi.org/project/sqlalchemy-excel/)
[![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)](https://opensource.org/licenses/MIT)
[![Docs](https://img.shields.io/badge/docs-GitHub-blue.svg)](https://github.com/yeongseon/sqlalchemy-excel/tree/main/docs)

SQLAlchemy dialect for Excel files — use Excel worksheets as database tables.
This is a **narrow-scope dialect**: it supports basic CRUD and ORM mapping, but not
relational features like JOINs or aggregations.

## Limitations (Read First)

Before writing any code, understand what this dialect **cannot** do:

| Feature | Supported? |
|---------|-----------|
| SELECT with WHERE, ORDER BY, LIMIT | ✅ |
| INSERT, UPDATE, DELETE | ✅ |
| CREATE TABLE / DROP TABLE | ✅ |
| ORM with DeclarativeBase | ✅ |
| Schema inspection (tables, columns) | ✅ |
| IN, BETWEEN, LIKE operators | ✅ |
| **JOIN** (any variant) | ❌ |
| **GROUP BY / HAVING** | ❌ |
| **DISTINCT** | ❌ |
| **OFFSET** | ❌ |
| **Subqueries / CTEs** | ❌ |
| **Aggregate functions** (COUNT, SUM, ...) | ❌ |
| **ALTER TABLE** | ❌ |
| **Foreign keys / indexes** | ❌ |
| **Concurrent writes** | ❌ |
| **Session.rollback()** | No-op (data persists) |

If you need any of the ❌ features, use SQLite, PostgreSQL, or another full-featured database.

---

## Installation

```bash
pip install sqlalchemy-excel
```

`excel-dbapi` is automatically installed as a dependency.

## Quick Start

```python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import DeclarativeBase, Session, Mapped, mapped_column

engine = create_engine("excel:///data.xlsx")

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "Sheet1"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(id=1, name="Alice"))
    session.commit()

with Session(engine) as session:
    users = session.query(User).all()
```

## URL Format

```python
# Relative path
engine = create_engine("excel:///data.xlsx")

# Absolute path (note four slashes)
engine = create_engine("excel:////home/user/data.xlsx")

# With engine options
engine = create_engine("excel:///data.xlsx", connect_args={"engine": "openpyxl"})
```

## Type Mapping

| SQLAlchemy Type | Excel Storage | Notes |
|---|---|---|
| `String`, `Text`, `VARCHAR`, `CHAR` | TEXT | All string types map to TEXT |
| `Integer`, `SmallInteger`, `BigInteger` | INTEGER | All integer types map to INTEGER |
| `Float`, `Numeric`, `Decimal` | FLOAT | All numeric types map to FLOAT |
| `Boolean` | BOOLEAN | |
| `Date` | DATE | |
| `DateTime`, `TIMESTAMP` | DATETIME | |
| `Time` | TEXT | Stored as text |
| `Uuid` | TEXT | Stored as text |

> BLOB, BINARY, JSON, and ARRAY types are not supported and will raise `CompileError`.

## ORM Examples

### Define a Model

```python
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Session, Mapped, mapped_column

engine = create_engine("excel:///data.xlsx")

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    age: Mapped[int] = mapped_column()

Base.metadata.create_all(engine)
```

### Insert

```python
with Session(engine) as session:
    session.add(User(id=1, name="Alice", age=30))
    session.add(User(id=2, name="Bob", age=25))
    session.commit()
```

### Query with Filters

```python
from sqlalchemy import select

with Session(engine) as session:
    # Basic query
    users = session.query(User).all()

    # WHERE clause
    user = session.query(User).filter(User.name == "Alice").first()

    # IN operator
    stmt = select(User).where(User.name.in_(["Alice", "Bob"]))
    users = session.scalars(stmt).all()

    # BETWEEN operator
    stmt = select(User).where(User.age.between(25, 35))
    users = session.scalars(stmt).all()

    # LIKE operator
    stmt = select(User).where(User.name.like("A%"))
    users = session.scalars(stmt).all()

    # ORDER BY + LIMIT
    stmt = select(User).order_by(User.age.desc()).limit(5)
    users = session.scalars(stmt).all()
```

### Update and Delete

```python
with Session(engine) as session:
    user = session.query(User).filter(User.id == 1).first()
    if user:
        user.name = "Ann"
        session.commit()

with Session(engine) as session:
    user = session.query(User).filter(User.id == 2).first()
    if user:
        session.delete(user)
        session.commit()
```

## Core Usage

```python
from sqlalchemy import create_engine, text

engine = create_engine("excel:///data.xlsx")

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM Sheet1"))
    for row in result:
        print(row)
```

## Schema Inspection

```python
from sqlalchemy import create_engine, inspect

engine = create_engine("excel:///data.xlsx")
inspector = inspect(engine)

# List all sheets (tables)
print(inspector.get_table_names())

# Get column info
print(inspector.get_columns("Sheet1"))

# Check if a sheet exists
print(inspector.has_table("Sheet1"))
```

---

## Experimental: Remote Excel via Microsoft Graph API

> **Status**: Experimental — API may change in future releases.

Access Excel files on OneDrive/SharePoint directly:

```bash
pip install sqlalchemy-excel[graph]
```

```python
from sqlalchemy import create_engine
from azure.identity import DefaultAzureCredential

engine = create_engine(
    "excel+graph:///drive_id/item_id",
    connect_args={"credential": DefaultAzureCredential()},
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM Sheet1"))
    for row in result:
        print(row)
```

URL format: `excel+graph:///drive_id/item_id` where `drive_id` and `item_id` are Microsoft Graph resource identifiers.
Query parameters: `?readonly=false` to enable write operations.

---

## Related Projects

- [excel-dbapi](https://github.com/yeongseon/excel-dbapi) — The underlying PEP 249 DB-API 2.0 driver for Excel files.

## License

MIT
