SQLXtraLite
Python · DB-API 2.0 · embedded

SQLXtraLite for Python

An embedded SQL database for Python — one file, no server, no setup. A familiar DB-API 2.0 interface that returns native Python values. Here's everything from a 30-second start to full analytical queries.

shell
pip install sqlxtralite
✓ native types ✓ prepared statements ✓ joins · aggregates · CASE · UNION ✓ upserts · transactions ✓ Linux · macOS · Windows
Supported platforms — prebuilt wheels, no compiler needed
PythonCPython 3.8 and newer (one abi3 wheel covers every version)
Linuxx86_64 · aarch64  (manylinux)
macOSApple Silicon (arm64)
Windowsx64

pip install picks the right wheel for your machine automatically. macOS-Intel isn't published yet; on any platform without a wheel, install fails (there's no source build).

01Quick start

Open a file, create a table, insert, query. Values come back as real Python objects.

quickstart.py

02Connect & cursors

A connection is a database file. Use it as a context manager so it always closes.

connect.py

Tip: use one connection per thread — the engine is single-writer.

03Insert & parameters

Bind values with ? placeholders (never string-format SQL). None becomes SQL NULL; executemany loads many rows fast.

insert.py

04Fetching rows

Step row-by-row, in batches, or all at once. description gives the column names.

fetch.py

05Rows as dicts

A tiny helper turns rows into dicts keyed by column name.

dict_rows.py

06Transactions

Each statement auto-commits. For all-or-nothing batches, wrap them in BEGIN / COMMIT (or ROLLBACK).

transactions.py

07Joins

INNER, LEFT (and two-table RIGHT) joins work as you'd expect.

joins.py

08Aggregates, GROUP BY, HAVING

COUNT / SUM / AVG / MIN / MAX, with grouping, filtering, and DISTINCT.

aggregates.py

09CASE expressions

Both the searched and simple forms, including inside aggregates.

case.py

10Subqueries

Scalar subqueries and IN (SELECT …).

subqueries.py

11UNION

UNION de-duplicates; UNION ALL keeps duplicates. A trailing ORDER BY applies to the whole result.

union.py

12Upserts: REPLACE & ON DUPLICATE KEY UPDATE

Insert-or-update on a PRIMARY KEY / UNIQUE conflict. VALUES(col) refers to the would-be-inserted value; you can also reference the existing row.

upsert.py

13INSERT … SELECT

Populate or archive a table from a query in one statement.

insert_select.py

14Indexes, UNIQUE & ALTER TABLE

ddl.py

15Binary data (blobs)

Pass bytes as a parameter; it comes back as bytes.

blobs.py

16Pagination

LIMIT / OFFSET for pages; a separate COUNT for the total.

pagination.py

17Complex example — store analytics

A self-contained mini e-commerce schema, seeded with executemany, then real analytical queries: a three-way join, revenue by category, monthly revenue, and a window-free "top N per group" via a correlated count.

analytics.py

18Use with pandas

Load any query straight into a DataFrame using description for the columns.

with_pandas.py

19A tiny key-value store

Upserts make a durable dict in ~15 lines.

kv.py

20Maintenance & durability

PRAGMAs for journal mode, integrity checks, and index repair.

maintenance.py

21Error handling

Every failure raises sqlxtralite.Error.

errors.py

The package is on PyPI — pip install sqlxtralite.