gspread-models

gspread-models#

Maintainability continuous integration License: MIT

The gspread-models package is an Object Relational Mapper (ORM) for the Google Sheets API. It provides a straightforward and intuitive model-based query interface, making it easy to interact with Google Sheets as if it were more like a database. This package offers a fast and flexible way to get up and running with a Google Sheets database, for rapid prototyping and development in Python.

Key Features:

  • Read and Write Data: Seamlessly read and write data to and from Google Sheets.

  • Easy Setup: Minimal schema requirements make it simple to get started.

  • Intuitive Query Interface: Familiar object-oriented query methods inspired by ActiveRecord (Ruby) and SQLAlchemy (Python).

  • Auto-incrementing ID: Automatically manages a primary key “id” column.

  • Timestamps: Automatically manages a “created_at” timestamp column.

  • Datetime Handling: Converts datetime columns to Python datetime objects for easier manipulation.

  • Flexible Migrations: Easily update the schema by modifying your Google Sheet and updating the corresponding list of columns.

Installation#

Install the package from PyPI:

pip install gspread_models

Quick Start#

Setup#

Step 1: Bind the base model to your Google Sheets document and your credentials (see Authentication for more details):

from gspread_models.base import BaseModel

BaseModel.bind(
    document_id="your-document-id",
    credentials_filepath="/path/to/google-credentials.json"
)

Step 2: Define your own light-weight class that inherits from the base model:

class Book(BaseModel):

    SHEET_NAME = "books"

    COLUMNS = ["title", "author", "year"]

When defining your class, specify a SHEET_NAME as well as a list of sheet-specific COLUMNS.

Step 3: Setup a corresponding sheet for this model.

To support the example above, create a sheet called “books”, and specify an initial row of column headers: “id”, “title”, “author”, “year”, and “created_at”.

Note

In addition to the sheet-specific attributes (“title”, “author”, and “year”), the base model will manage metadata columns, including a unique identifier (“id”) as well as a timestamp (“created_at”).

Usage#

Once you have your model class setup, you can utilize the Query Interface, to read and write data to the sheet.

Writing / appending records to the sheet:

Book.create_all([
    {"title": "To Kill a Mockingbird", "author": "Harper Lee", "year": 1960},
    {"title": "1984", "author": "George Orwell", "year": 1949},
    {"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "year": 1925},
    {"title": "The Catcher in the Rye", "author": "J.D. Salinger", "year": 1951},
    {"title": "Pride and Prejudice", "author": "Jane Austen", "year": 1813},
])

Fetching all records from the sheet:

books = Book.all()

for book in books:
    print(book.id, "|", book.title, "|", book.author)

#> 1 | To Kill a Mockingbird | Harper Lee
#> 2 | 1984 | George Orwell
#> 3 | The Great Gatsby | F. Scott Fitzgerald
#> 4 | The Catcher in the Rye | J.D. Salinger
#> 5 | Pride and Prejudice | Jane Austen

For more details, see the guides and tutorials below:

Examples#

Here are some examples that demonstrate the usage of gspread-models within a variety of contexts:

If you use the gspread-models package, you are encouraged to add your project to this list, by submitting a pull request or opening an issue.

Contributing#

Contributions welcome! Here are some reference guides to help you get started as a contributor or maintainer of this package:

Acknowlegements#

This package is built on top of the awesome gspread package.

Contents