Demo Notebook

Demo Notebook#

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)

/usr/local/lib/python3.10/dist-packages/google/auth/compute_engine/credentials.pyCompute 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)

for book in books:
    print(book.id, book.title, book.author, sep=" | ")
    # book.created_at.strftime("%Y-%m-%d")

| To Kill a Mockingbird | Harper Lee
| 1984 | George Orwell
| The Great Gatsby | F. Scott Fitzgerald
| The Catcher in the Rye | J.D. Salinger
| Pride and Prejudice | Jane Austen
| To the Lighthouse | Virginia Woolf
| The Hobbit | J.R.R. Tolkien
| Moby-Dick | Herman Melville
| Brave New World | Aldous Huxley
| Alice's Adventures in Wonderland | Lewis Carroll
| Harry Potter and the Philosopher's Stone | J.K. Rowling
| 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