fsspec-db

Database tables and views as fsspec filesystems.

Overview

fsspec-db exposes SQL databases through familiar fsspec operations:

• ls("/") lists schemas.
• ls("/main") lists tables and views.
• info("/main/users/columns/id") returns column metadata.
• cat_file("/main/users.parquet") materializes a table as bytes.
• query("SELECT ...") returns a pyarrow.Table.
• open("/main/users.arrow", "wb") or put_file(..., "/main/users.parquet") inserts rows.

Native backends are registered as db+sqlite, db+postgresql / db+postgres, and db+mysql. Python adapters provide db+duckdb, db+sqlalchemy, and db+odbc.

[!WARNING] Database overwrite writes replace table contents. open(path, "wb"), pipe_file, and default put_file run truncate semantics before inserting incoming rows. Use "ab" or mode="append" to append instead.

Install

pip install fsspec-db

Quick Start

import fsspec
import pyarrow as pa
import pyarrow.ipc as ipc

fs = fsspec.filesystem("db+sqlite", database="app.db")

print(fs.ls("/", detail=False))
print(fs.ls("/main", detail=False))
print(fs.info("/main/users"))

table = fs.query("SELECT id, name FROM users WHERE id > ?", [0])

with fs.open("/main/users.arrow", "ab") as file:
    sink = pa.BufferOutputStream()
    with ipc.new_stream(sink, pa.table({"name": ["ada"]}).schema) as writer:
        writer.write_table(pa.table({"name": ["ada"]}))
    file.write(sink.getvalue().to_pybytes())

Path Shape

/main
/main/users
/main/users/columns/id
/main/users/indexes/idx_users_name
/main/users.arrow
/main/users.parquet
/main/active_users/definition.sql

Documentation

• Browse a SQLite database: guided first use.
• Use SQLite, PostgreSQL or MySQL, or Python database adapters: task-oriented guides.
• Understand the filesystem mapping: architecture and boundaries.
• Look up paths, custom Python backend contracts, and the Python API: factual reference.