Metadata-Version: 2.4
Name: py-sheet-db
Version: 0.1.0
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-sheet-db: Unified Tabular Database

A "plug and play" Python library that treats Google Sheets, Excel files, and CSV directories as relational databases. It features unified caching, schema validation, and multi-format support.

## Features

- **Multi-Format Support**: One API for Google Sheets, `.xlsx`, and `.csv`.
- **Fast CRUD**: Uses a local SQLite cache for near-instant operations.
- **No Data Loss**: Changes are tracked locally and synchronized via `flush()`.
- **Relational Views**: Perform Joins and Filters across multiple sheets or formats.
- **Schema Validation**: Enforce data types and required fields across any storage format.

## Installation

```bash
pip install gspread google-auth pandas openpyxl
```

## Quick Start

`PySheetDB` can automatically detect the driver or you can set it explicitly using the `driver_type` parameter.

```python
from py_sheet_db import PySheetDB

# 1. Automatic Detection
db = PySheetDB("data.xlsx")

# 2. Explicit Type Selection ('excel', 'csv', or 'gsheets')
db = PySheetDB("my_data_folder", driver_type='csv')

# 3. Google Sheets (requires credentials.json)
db = PySheetDB("credentials.json", driver_type='gsheets', spreadsheet_id='YOUR_ID')
# Or use the helper:
db = PySheetDB.connect_gsheets("credentials.json")
```

## Schema Validation

Enforce data types and required fields. `py-sheet-db` will attempt to auto-cast types (e.g., string to int) when possible.

```python
from py_sheet_db import Schema

user_schema = Schema({
    'id': int,
    'name': str,
    'email': str,
    'balance': float
})

users = db.table('Users', schema=user_schema)
users.insert({'id': 1, 'name': 'Alice', 'email': 'alice@example.com', 'balance': 50.5})
```

## Relational Views (Joins & Filters)

You can create virtual views that merge data from multiple tables using a SQL-like interface powered by Pandas.

```python
# 1. Define your tables
orders = db.table('Orders')
products = db.table('Products')

# 2. Create a View
# Join Orders and Products on 'product_id'
order_details = db.view('OrderDetails', [orders, products]) \
    .join(orders, products, on='product_id', how='left') \
    .filter(lambda df: df[df['status'] == 'completed'])

# 3. Get results as a list of dicts
results = order_details.get_data()
```

## CRUD Operations

```python
# Access a Table
users = db.table('Users')

# Insert
users.insert({'id': 1, 'name': 'Alice'})

# Find (Returns list of dicts)
admin = users.find(role='admin')

# Find One
bob = users.find_one(id=102)

# Flush to source (GSheets/Excel/CSV)
db.flush()
```

## Google Sheets Setup

To use Google Sheets, you need a **Service Account JSON key file**.

### 1. Get Google Credentials
1.  Go to the [Google Cloud Console](https://console.cloud.google.com/).
2.  Create a new project.
3.  Go to **APIs & Services > Library** and enable:
    - **Google Sheets API**
    - **Google Drive API**
4.  Go to **APIs & Services > Credentials**.
5.  Click **Create Credentials > Service Account**.
6.  Once created, go to the **Keys** tab and click **Add Key > Create New Key (JSON)**.
7.  Download the file and rename it to `credentials.json` in your project folder.

### 2. Share the Spreadsheet
-   Once you have your service account email (e.g., `account-name@project-id.iam.gserviceaccount.com`), you must **Share** your Google Spreadsheet with this email address with "Editor" permissions.
-   Alternatively, if you use `db.create_database("Name")`, the library creates a new spreadsheet owned by the service account. You may still need to share it with your personal email to see it in your browser.

## How it works

1. **Initialization**: Data is synced from the source into a hidden local SQLite DB (`.db_cache/`).
2. **Operations**: All CRUD reflects immediately in the fast SQLite cache.
3. **Synchronization**: `db.flush()` (or automatic exit hook) pushes "dirty" rows back to the primary storage.
