Metadata-Version: 2.4
Name: py-sheet-db
Version: 0.2.2
Summary: A unified database interface for Google Sheets, Excel, and CSV.
Author: Nishant
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.6
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: gspread>=6.0.0
Requires-Dist: google-auth>=2.0.0
Requires-Dist: pandas>=2.0.0
Requires-Dist: openpyxl>=3.1.0
Dynamic: author
Dynamic: classifier
Dynamic: description
Dynamic: description-content-type
Dynamic: license-file
Dynamic: requires-dist
Dynamic: requires-python
Dynamic: summary

# 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

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

Dependencies: `gspread`, `google-auth`, `pandas`, `openpyxl`

---

## Quick Start

```python
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

```python
# 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](https://console.cloud.google.com/).
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).

```python
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": "alice@example.com"})

# 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

```python
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)

```python
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()
```
