py-sheet-db 0.1.0

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

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

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.

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.

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': '[e-mail]', '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.

# 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

# 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.
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., [e-mail]), 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.