sqlalchemy-excel 0.4.0

sqlalchemy-excel

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:

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

Installation

pip install sqlalchemy-excel

excel-dbapi is automatically installed as a dependency.

Quick Start

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

# 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

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

ORM Examples

Define a Model

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

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

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

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

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

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:

pip install sqlalchemy-excel[graph]

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 — The underlying PEP 249 DB-API 2.0 driver for Excel files.

License

MIT