py-sheet-db 0.2.0

A unified database interface for Google Sheets, Excel, and CSV.

py-db-sheet: Local SQL Database with Bidirectional Sheet Sync

A Python library that gives you a fast local SQLite database with on-demand bidirectional sync to Google Sheets, Excel, or CSV.

Work entirely offline with a real SQL database. When you're ready, push your full DB state to a sheet — or pull from a sheet to repopulate your DB from scratch.

How it works

db      →  one file / spreadsheet / folder  (the remote)
table   →  one sheet / tab inside that file
schema  →  __schema__ sheet in the same file (enables reconstruction)

1. Local: All CRUD hits SQLite — instant and offline-capable.
2. Push: db.push_to_remote() dumps every table + schema to the linked remote.
3. Pull: db.pull_from_remote() wipes local and repopulates from the remote, reconstructing typed Schema objects automatically.

Installation

pip install py-sheet-db
# or from source:
pip install -e .

Dependencies: gspread, google-auth, pandas, openpyxl

---

Quick Start

from py_sheet_db import PySheetDB, Schema

# 1. Initialize local SQLite database
db = PySheetDB("my_db")

# 2. Link a remote (CSV folder, Excel file, or Google Sheets)
db.link_remote("my_csv_backup", driver_type="csv")
# db.link_remote("my_backup.xlsx", driver_type="excel")
# db.link_remote("credentials.json", driver_type="gsheets", spreadsheet_id="YOUR_ID")

# 3. Define tables with optional typed schemas
schema = Schema({"id": int, "name": str, "score": float})
players = db.table("Players", schema=schema)

# 4. Normal SQL-style CRUD — all local, all fast
players.insert({"id": 1, "name": "Alice", "score": 95.5})
players.insert({"id": 2, "name": "Bob",   "score": 88.0})

alice = players.find_one(id=1)
all_players = players.find()

players.update({"id": 1}, {"score": 99.0})
players.delete(id=2)

# 5. Push local state to the remote whenever you want
db.push_to_remote()

# 6. Pull from remote to repopulate local DB from scratch
db.pull_from_remote()   # also restores Schema types automatically

# 7. Release connections when done
db.close()

---

Google Sheets

Setup

# Helper constructor for GSheets
db = PySheetDB.connect_gsheets(
    db_path="my_local_db",
    credentials_path="credentials.json",
    spreadsheet_id="YOUR_SPREADSHEET_ID",
)

db.push_to_remote()    # save to sheet
db.pull_from_remote()  # restore from sheet

Getting credentials

1. Go to the Google Cloud Console.
2. Enable Google Sheets API and Google Drive API.
3. Create a Service Account under APIs & Services → Credentials.
4. Download the JSON key file and save it as credentials.json.
5. Share your spreadsheet with the service account email (Editor access).

---

Schema Validation & Foreign Keys

Schemas enforce types on insert, and they can also define Primary Keys and Foreign Keys (enforced by the underlying SQLite engine).

from py_sheet_db import Schema

# Users table
users_schema = Schema({
    "id":      int,
    "name":    str,
    "email":   str,
}, primary_key="id")
users = db.table("Users", schema=users_schema)
users.insert({"id": 1, "name": "Alice", "email": "[e-mail]"})

# Orders table referencing Users
orders_schema = Schema({
    "id":      int,
    "user_id": int,
    "total":   float,
}, primary_key="id", foreign_keys={"user_id": "Users.id"})
orders = db.table("Orders", schema=orders_schema)

# This succeeds:
orders.insert({"id": 1, "user_id": 1, "total": 50.5})

# This raises sqlite3.IntegrityError (foreign key violation):
# orders.insert({"id": 2, "user_id": 99, "total": 10.0})

Supported types: int, float, str, bool

---

Remote layout

After a push_to_remote(), the remote file/spreadsheet will contain:

Sheet / File	Contents
Players	All rows from the Players table
Users	All rows from the Users table
__schema__	table, column, type — one row per field

A subsequent pull_from_remote() reads __schema__ first, then repopulates each table with the correct Schema attached.

---

CRUD Reference

table = db.table("MyTable")

# Insert
table.insert({"col1": "val1", "col2": 42})

# Find all
rows = table.find()

# Find with filter
rows = table.find(col1="val1")

# Find one
row = table.find_one(col2=42)

# Update (query dict, updates dict)
table.update({"col2": 42}, {"col1": "new_val"})

# Delete
table.delete(col2=42)

# Get all rows
rows = table.get_all()

---

Relational Views (Joins & Filters)

orders   = db.table("Orders")
products = db.table("Products")

order_details = db.view("OrderDetails", [orders, products]) \
    .join(orders, products, on="product_id", how="left") \
    .filter(lambda df: df[df["status"] == "completed"])

results = order_details.get_data()