sqlxtralite

An embedded SQL database for Python — a single file, no server, no setup. It speaks the familiar DB-API 2.0 interface and returns native Python types (int, float, str, bytes, None).

Install

pip install sqlxtralite

Quick start

import sqlxtralite

con = sqlxtralite.connect("shop.db")          # opens (or creates) one file
cur = con.cursor()

cur.execute("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
cur.execute("INSERT INTO items VALUES (1, 'basil', 2.95)")

cur.execute("SELECT name, price FROM items")
print(cur.fetchall())        # [('basil', 2.95)]
con.close()

Parameters & bulk insert

# qmark (?) placeholders; None -> NULL
cur.execute("INSERT INTO items VALUES (?, ?, ?)", (2, "mint", None))

# insert many rows at once
cur.executemany(
    "INSERT INTO items VALUES (?, ?, ?)",
    [(3, "sage", 4.20), (4, "thyme", 1.50)],
)

cur.execute("SELECT name FROM items WHERE price > ?", (2.0,))
print([row[0] for row in cur.fetchall()])     # ['basil', 'sage']

Fetching results

cur.execute("SELECT id, name FROM items ORDER BY id")

cur.fetchone()      # (1, 'basil')          — next row, or None at the end
cur.fetchmany(2)    # [(2, 'mint'), (3, 'sage')]
cur.fetchall()      # the rest

print([d[0] for d in cur.description])        # column names: ['id', 'name']
print(cur.rowcount, cur.lastrowid)

Queries it supports

# joins, aggregates, grouping
cur.execute("""
    SELECT name, SUM(price) AS total
    FROM items
    GROUP BY name
    HAVING total > 1
    ORDER BY total DESC
""")

# CASE, UNION, subqueries, REPLACE, INSERT ... ON DUPLICATE KEY UPDATE, …
cur.execute("INSERT INTO items VALUES (1, 'basil', 9.99) "
            "ON DUPLICATE KEY UPDATE price = VALUES(price)")

API

connect(path) → Connection. Module flags: apilevel = "2.0", paramstyle = "qmark", threadsafety = 1. Errors raise sqlxtralite.Error.

Notes

• Use one connection per thread.
• Each statement auto-commits; for multi-statement transactions issue explicit BEGIN / COMMIT with execute.