Demo Notebook

Open In Colab

This is a Google Colab notebook that showcases various features of the gspread-models package.

In this demo, we take advantage of the simplified Google authentication process within Colab to obtain credentials for the logged in user (instead of using a service account credentials JSON file).

Package Installation

Package installation:

%%capture
#!pip uninstall gspread_models
!pip install git+https://github.com/s2t2/gspread-models-py.git@imports --upgrade
#!pip install gspread_models==1.0.7
!pip list | grep gspread_models
!pip list | grep gspread-models
gspread-models                   1.0.7
#from gspread_models import VERSION
#print(VERSION)

Google Credentials

Asks you to login via google account:

from google.colab import auth

auth.authenticate_user()

Gets credentials for the logged in user:

from google.auth import default

creds, _ = default()
type(creds)
google.auth.compute_engine.credentials.Credentials
def __init__(service_account_email='default', quota_project_id=None, scopes=None, default_scopes=None, universe_domain=None)
Compute Engine Credentials.

These credentials use the Google Compute Engine metadata server to obtain
OAuth 2.0 access tokens associated with the instance's service account,
and are also used for Cloud Run, Flex and App Engine (except for the Python
2.7 runtime, which is supported only on older versions of this library).

For more information about Compute Engine authentication, including how
to configure scopes, see the `Compute Engine authentication
documentation`_.

.. note:: On Compute Engine the metadata server ignores requested scopes.
    On Cloud Run, Flex and App Engine the server honours requested scopes.

.. _Compute Engine authentication documentation:
    https://cloud.google.com/compute/docs/authentication#using

Google Sheets Setup

Create a Google Sheets document with “books” sheet, with columns: id, title, author, year, and created_at.

Note the document identifier, and set it as the GSPREAD_MODELS_DOCUMENT_ID notebook secret.

Use the document identifier from notebook secrets:

from google.colab import userdata

GOOGLE_SHEETS_DOCUMENT_ID = userdata.get("GSPREAD_MODELS_DOCUMENT_ID") or "your-document-id-here"

Spreadsheet Service

The spreadsheet service provides a lower level interface into the configured google sheet document. You can use it if you like.

Build a new service instance:

#from gspread_models.service import SpreadsheetService
from gspread_models import SpreadsheetService

service = SpreadsheetService(creds=creds, document_id=GOOGLE_SHEETS_DOCUMENT_ID)
SPREADSHEET SERVICE...
DOCUMENT ID: 1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs
service.doc
<Spreadsheet 'GSpread Models (Test Database)' id:1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs>
for sheet in service.sheets:
    print(sheet.title)
Info
products
orders
books

Binding the Base Model

Bind the base model with access to your account credentials and your document:

#from gspread_models.base import BaseModel
from gspread_models import BaseModel

# if you already have the service handy, you can set it:
#BaseModel.service = service

# otherwise you can bypass the service altogether:
BaseModel.bind(credentials=creds, document_id=GOOGLE_SHEETS_DOCUMENT_ID)
SPREADSHEET SERVICE...
DOCUMENT ID: 1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs

Defining Child Model Classes

Now, defining custom classes that inherit from the base model:

class Book(BaseModel):

    SHEET_NAME = "books"

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

Queries

Destroy all records:

Book.destroy_all()
GET SHEET ('books')...
{'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',
 'replies': [{}]}

Populate sheet with default records:

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},
    {"title": "To the Lighthouse", "author": "Virginia Woolf", "year": 1927},
    {"title": "The Hobbit", "author": "J.R.R. Tolkien", "year": 1937},
    {"title": "Moby-Dick", "author": "Herman Melville", "year": 1851},
    {"title": "Brave New World", "author": "Aldous Huxley", "year": 1932},
    {"title": "Alice's Adventures in Wonderland", "author": "Lewis Carroll", "year": 1865},
    {"title": "Harry Potter and the Philosopher's Stone", "author": "J.K. Rowling", "year": 1997},
    {"title": "Harry Potter and the Chamber of Secrets", "author": "J.K. Rowling", "year": 1998},
])
{'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',
 'tableRange': 'books!A1:F1',
 'updates': {'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',
  'updatedRange': 'books!A2:E13',
  'updatedRows': 12,
  'updatedColumns': 5,
  'updatedCells': 60}}

Fetch all records from the sheet:

books = Book.all()
len(books)
12
for book in books:
    print(book.id, book.title, book.author, sep=" | ")
    # book.created_at.strftime("%Y-%m-%d")
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
6 | To the Lighthouse | Virginia Woolf
7 | The Hobbit | J.R.R. Tolkien
8 | Moby-Dick | Herman Melville
9 | Brave New World | Aldous Huxley
10 | Alice's Adventures in Wonderland | Lewis Carroll
11 | Harry Potter and the Philosopher's Stone | J.K. Rowling
12 | Harry Potter and the Chamber of Secrets | J.K. Rowling

Find record by id:

book = Book.find(3)
dict(book)
{'id': 3,
 'title': 'The Great Gatsby',
 'author': 'F. Scott Fitzgerald',
 'year': 1925,
 'created_at': datetime.datetime(2024, 5, 28, 21, 22, 56, 732122, tzinfo=datetime.timezone.utc)}

Filter records on matching conditions:

book = Book.where(author="F. Scott Fitzgerald")[0]
dict(book)
{'id': 3,
 'title': 'The Great Gatsby',
 'author': 'F. Scott Fitzgerald',
 'year': 1925,
 'created_at': datetime.datetime(2024, 5, 28, 21, 22, 56, 732122, tzinfo=datetime.timezone.utc)}
hp_books = Book.where(author="J.K. Rowling")
for book in hp_books:
    print(book.id, book.title, book.author, sep=" | ")
11 | Harry Potter and the Philosopher's Stone | J.K. Rowling
12 | Harry Potter and the Chamber of Secrets | J.K. Rowling

Pandas Support

Formatting Records as Pandas DataFrame

FYI: It is easy to construct a pandas DataFrame containing the information fetched from the sheet, by converting the records to a list of dictionaries:

from pandas import DataFrame

books_df = DataFrame([dict(book) for book in books])
books_df.head()
id title author year created_at
0 1 To Kill a Mockingbird Harper Lee 1960 2024-05-28 21:22:56.730528+00:00
1 2 1984 George Orwell 1949 2024-05-28 21:22:56.732074+00:00
2 3 The Great Gatsby F. Scott Fitzgerald 1925 2024-05-28 21:22:56.732122+00:00
3 4 The Catcher in the Rye J.D. Salinger 1951 2024-05-28 21:22:56.732157+00:00
4 5 Pride and Prejudice Jane Austen 1813 2024-05-28 21:22:56.732190+00:00

Customizing Base Model Behavior

If you would like to build additional pandas functionality into your child classes, you can leverage inheritence to overwrite methods of the base model:

from pandas import DataFrame

class MyBaseModel(BaseModel):

    @classmethod
    def records_to_df(cls):
        records = cls.all()
        return DataFrame([dict(record) for record in records])

#MyBaseModel.service = service
MyBaseModel.bind(credentials=creds, document_id=GOOGLE_SHEETS_DOCUMENT_ID)
SPREADSHEET SERVICE...
DOCUMENT ID: 1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs

Now all child models will have this capability:

class MyBook(MyBaseModel):

    SHEET_NAME = "books"

    COLUMNS = ["title", "author", "year"]
books_df = MyBook.records_to_df()
books_df.head()
GET SHEET ('books')...
id title author year created_at
0 1 To Kill a Mockingbird Harper Lee 1960 2024-05-28 21:22:56.730528+00:00
1 2 1984 George Orwell 1949 2024-05-28 21:22:56.732074+00:00
2 3 The Great Gatsby F. Scott Fitzgerald 1925 2024-05-28 21:22:56.732122+00:00
3 4 The Catcher in the Rye J.D. Salinger 1951 2024-05-28 21:22:56.732157+00:00
4 5 Pride and Prejudice Jane Austen 1813 2024-05-28 21:22:56.732190+00:00