excel-dbapi 0.2.0

PEP 249 compliant DB-API driver for Excel files

excel-dbapi

A lightweight, Python DB-API 2.0 compliant connector for Excel files. Use SQL to query, insert, update, and delete rows in .xlsx workbooks — no database server required.

Who is this for?

• Data analysts who want to query Excel files with SQL instead of manual filtering
• Citizen developers automating small workflows with familiar SQL syntax
• Educators teaching SQL concepts without setting up a database
• Prototypers building quick data pipelines before moving to a real database

Who is this NOT for?

• If you need JOINs, GROUP BY, subqueries, or advanced SQL → use SQLite or PostgreSQL
• If you need concurrent writes from multiple processes → use a real database
• If your Excel file has 100k+ rows → use pandas directly or a database

---

Features

• Python DB-API 2.0 compliant interface (PEP 249)
• Query Excel files using SQL syntax
• Supports SELECT, INSERT, UPDATE, DELETE
• Basic DDL support (CREATE TABLE, DROP TABLE)
• WHERE conditions with AND/OR and comparison operators
• ORDER BY and LIMIT for SELECT
• Sheet-to-Table mapping
• Pandas & Openpyxl engine selector
• Formula injection defense (enabled by default)
• Transaction simulation (commit/rollback)

---

Installation

pip install excel-dbapi

See CHANGELOG for release history.

---

Quick Start

from excel_dbapi.connection import ExcelConnection

# Open an Excel file and query it
with ExcelConnection("sample.xlsx") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Sheet1")
    print(cursor.fetchall())

Insert, Update, Delete

with ExcelConnection("sample.xlsx") as conn:
    cursor = conn.cursor()

    # Insert with parameter binding (recommended)
    cursor.execute("INSERT INTO Sheet1 (id, name) VALUES (?, ?)", (1, "Alice"))

    # Update
    cursor.execute("UPDATE Sheet1 SET name = 'Ann' WHERE id = 1")

    # Delete
    cursor.execute("DELETE FROM Sheet1 WHERE id = 2")

Create and Drop Sheets

with ExcelConnection("sample.xlsx") as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE NewSheet (id, name)")
    cursor.execute("DROP TABLE NewSheet")

Engine Options

Engine	Description	Dependency
openpyxl (default)	Fast sheet access	openpyxl
pandas	DataFrame-based operations	pandas, openpyxl

conn = ExcelConnection("sample.xlsx", engine="openpyxl")  # default
conn = ExcelConnection("sample.xlsx", engine="pandas")

---

Safety Defaults

Formula Injection Defense

By default, excel-dbapi sanitizes cell values on write (INSERT/UPDATE) to prevent formula injection attacks. Strings starting with =, +, -, @, \t, or \r are automatically prefixed with a single quote (') so they are stored as plain text, not executed as formulas.

# Default: sanitization ON (recommended)
with ExcelConnection("sample.xlsx") as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Sheet1 (id, name) VALUES (?, ?)",
                   (1, "=SUM(A1:A10)"))
    # Stored as: '=SUM(A1:A10)  (safe, not executed as formula)

# Opt out if you intentionally write formulas
with ExcelConnection("sample.xlsx", sanitize_formulas=False) as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Sheet1 (id, formula) VALUES (?, ?)",
                   (1, "=SUM(A1:A10)"))
    # Stored as: =SUM(A1:A10)  (executed as formula in Excel)

---

Transaction Example

with ExcelConnection("sample.xlsx", autocommit=False) as conn:
    cursor = conn.cursor()
    cursor.execute("UPDATE Sheet1 SET name = 'Ann' WHERE id = 1")
    conn.rollback()

When autocommit is enabled, rollback() is not supported.

Cursor Metadata

with ExcelConnection("sample.xlsx") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT id, name FROM Sheet1")
    print(cursor.description)
    print(cursor.rowcount)

---

Troubleshooting

"Column 'xyz' not found"

The column name in your SQL doesn't match any header in the sheet.

ProgrammingError: Column 'nmae' not found in Sheet1. Available columns: ['id', 'name', 'email']

Fix: Check the spelling. Column names must match the first row (header) of the sheet exactly.

"Table 'SheetX' not found"

The sheet name in your SQL doesn't match any sheet in the workbook.

ProgrammingError: Table 'Shee1' not found. Available sheets: ['Sheet1', 'Sheet2']

Fix: Check the sheet name spelling. Use the exact sheet name (case-sensitive) shown in your Excel file.

PandasEngine drops formatting

PandasEngine reads data into a DataFrame and writes it back. This process drops Excel formatting, charts, images, and formulas.

Fix: Use the default openpyxl engine if you need to preserve formatting.

Integer vs. string comparison (Pandas)

The Pandas engine preserves Python types. If a column contains integers, WHERE id = '2' (string) won't match — use WHERE id = 2 (no quotes).

Fix: Omit quotes around numeric values in WHERE clauses when using the Pandas engine.

---

Limitations and Operational Guidance

• PandasEngine rewrites workbooks and may drop formatting, charts, and formulas.
• OpenpyxlEngine loads with data_only=True, so formulas are evaluated to values when reading.
• Use a single-writer model for writes. Avoid writing to the same file from multiple processes.
• Save is implemented with a temporary file + atomic replace (os.replace) for safer persistence.
• No support for JOIN, GROUP BY, HAVING, or subqueries.

Roadmap

• Remote file connection improvements

See Project Roadmap for details.

---

Related Projects

sqlalchemy-excel

sqlalchemy-excel is a higher-level toolkit that uses excel-dbapi as its core Excel I/O layer. It provides SQLAlchemy model-driven template generation, server-side validation, database import, and export.

• Which package should I use? See the decision guide
• Interface contract: See the compatibility documentation

---

Documentation

• Usage Guide
• Development Guide
• Project Roadmap
• 10-Minute Quickstart
• Operations Notes
• Public Roadmap

Examples

• examples/basic_usage.py
• examples/write_operations.py
• examples/transactions.py
• examples/advanced_query.py
• examples/pandas_engine.py

---

License

MIT License