docs/notebooks/demo_v1_0_5.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Demo Notebook"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<a target=\"_blank\" href=\"https://colab.research.google.com/drive/19hMHayokPtpJkLgCsWXZLV3FsGF7gvU6?usp=sharing\">\n",
" <img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/>\n",
"</a>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This is a Google Colab notebook that showcases various features of the `gspread-models` package.\n",
"\n",
"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)."
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "dVY-MEQMfBKx"
},
"source": [
"## Package Installation"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "iRNmtlPzg4-i"
},
"source": [
"Package installation:"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"id": "4AHQ9Raue_bn"
},
"outputs": [],
"source": [
"%%capture\n",
"#!pip uninstall gspread_models\n",
"!pip install gspread_models==1.0.5 # --upgrade"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "sAo-H0FafPVy",
"outputId": "2cfedd60-437a-40c5-be57-c0eee26b24e3"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"gspread_models 1.0.5\n"
]
}
],
"source": [
"!pip list | grep gspread_models"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "e1i24s_Ugx4o"
},
"source": [
"## Google Credentials"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"id": "HlOn7AgKFh1R"
},
"outputs": [],
"source": [
"from google.colab import auth\n",
"\n",
"auth.authenticate_user()"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 204
},
"id": "Ib7gP85SG3CG",
"outputId": "54745399-dc95-493b-a93e-018ff34b058c"
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-width:800px; border: 1px solid var(--colab-border-color);\"><style>\n",
" pre.function-repr-contents {\n",
" overflow-x: auto;\n",
" padding: 8px 12px;\n",
" max-height: 500px;\n",
" }\n",
"\n",
" pre.function-repr-contents.function-repr-contents-collapsed {\n",
" cursor: pointer;\n",
" max-height: 100px;\n",
" }\n",
" </style>\n",
" <pre style=\"white-space: initial; background:\n",
" var(--colab-secondary-surface-color); padding: 8px 12px;\n",
" border-bottom: 1px solid var(--colab-border-color);\"><b>google.auth.compute_engine.credentials.Credentials</b><br/>def __init__(service_account_email='default', quota_project_id=None, scopes=None, default_scopes=None, universe_domain=None)</pre><pre class=\"function-repr-contents function-repr-contents-collapsed\" style=\"\"><a class=\"filepath\" style=\"display:none\" href=\"#\">/usr/local/lib/python3.10/dist-packages/google/auth/compute_engine/credentials.py</a>Compute Engine Credentials.\n",
"\n",
"These credentials use the Google Compute Engine metadata server to obtain\n",
"OAuth 2.0 access tokens associated with the instance's service account,\n",
"and are also used for Cloud Run, Flex and App Engine (except for the Python\n",
"2.7 runtime, which is supported only on older versions of this library).\n",
"\n",
"For more information about Compute Engine authentication, including how\n",
"to configure scopes, see the `Compute Engine authentication\n",
"documentation`_.\n",
"\n",
".. note:: On Compute Engine the metadata server ignores requested scopes.\n",
" On Cloud Run, Flex and App Engine the server honours requested scopes.\n",
"\n",
".. _Compute Engine authentication documentation:\n",
" https://cloud.google.com/compute/docs/authentication#using</pre>\n",
" <script>\n",
" if (google.colab.kernel.accessAllowed && google.colab.files && google.colab.files.view) {\n",
" for (const element of document.querySelectorAll('.filepath')) {\n",
" element.style.display = 'block'\n",
" element.onclick = (event) => {\n",
" event.preventDefault();\n",
" event.stopPropagation();\n",
" google.colab.files.view(element.textContent, 35);\n",
" };\n",
" }\n",
" }\n",
" for (const element of document.querySelectorAll('.function-repr-contents')) {\n",
" element.onclick = (event) => {\n",
" event.preventDefault();\n",
" event.stopPropagation();\n",
" element.classList.toggle('function-repr-contents-collapsed');\n",
" };\n",
" }\n",
" </script>\n",
" </div>"
],
"text/plain": [
"google.auth.compute_engine.credentials.Credentials"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from google.auth import default\n",
"\n",
"creds, _ = default()\n",
"type(creds)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "OG5YBx0CdSYx"
},
"source": [
"## Spreadsheet Service"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "OGjaDr9Vez0e"
},
"source": [
"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."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"id": "J4eXoNQHgawN"
},
"outputs": [],
"source": [
"from google.colab import userdata\n",
"\n",
"GOOGLE_SHEETS_DOCUMENT_ID = userdata.get(\"GSPREAD_MODELS_DOCUMENT_ID\")"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "pSQ8SNOkj6h9"
},
"source": [
"Build a new service instance:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "6mqMJycrgpZC",
"outputId": "c9552f68-ddde-4f7c-8b35-3ca1df4e7343"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"SPREADSHEET SERVICE...\n",
"DOCUMENT ID: 1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs\n"
]
}
],
"source": [
"from gspread_models.service import SpreadsheetService\n",
"\n",
"service = SpreadsheetService(creds=creds, document_id=GOOGLE_SHEETS_DOCUMENT_ID)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "MsySNzYWkBak",
"outputId": "b9388781-2993-4e4e-ac24-f4ca7d601d07"
},
"outputs": [
{
"data": {
"text/plain": [
"<Spreadsheet 'GSpread Models (Test Database)' id:1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs>"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"service.doc"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "FXBBG3dwj95e",
"outputId": "da1e6fa9-aee6-433d-fe7a-52df755284ab"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Info\n",
"products\n",
"orders\n",
"books\n"
]
}
],
"source": [
"for sheet in service.sheets:\n",
" print(sheet.title)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "zGcO45SgdWy6"
},
"source": [
"## Binding the Base Model"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "wM3DWDLwjJz2"
},
"source": [
"Bind the base model with access to your account credentials and your document:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"id": "TZNcgKtjgIvH"
},
"outputs": [],
"source": [
"from gspread_models.base import BaseModel\n",
"\n",
"BaseModel.service = service"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "ZAMpwfwZdb-U"
},
"source": [
"## Defining Child Model Classes"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "1094P6NBjTNq"
},
"source": [
"Now, defining custom class that inherits from the base model:"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"id": "GYTgcrjMfIrA"
},
"outputs": [],
"source": [
"class Book(BaseModel):\n",
"\n",
" SHEET_NAME = \"books\"\n",
"\n",
" COLUMNS = [\"title\", \"author\", \"year\"]\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "9L_sHhcsdfzX"
},
"source": [
"## Queries"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "hAGNABrldiRb"
},
"source": [
"Destroy all records:"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "o3mSWe0Wroy8",
"outputId": "f076607f-d2a6-4f7d-e1cf-029dff5d4fa4"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"GET SHEET ('books')...\n"
]
},
{
"data": {
"text/plain": [
"{'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',\n",
" 'replies': [{}]}"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"Book.destroy_all()"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "kYRFnqBGdkXA"
},
"source": [
"Populate sheet with default records:"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "meLRpTPtcubm",
"outputId": "030d4514-d23d-48ef-dc10-ff4568b7b5a0"
},
"outputs": [
{
"data": {
"text/plain": [
"{'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',\n",
" 'tableRange': 'books!A1:F1',\n",
" 'updates': {'spreadsheetId': '1Nk-UBd-3TyWZqbWSvKIIF-S_KKdrkVFRVeNrFy_F1gs',\n",
" 'updatedRange': 'books!A2:E13',\n",
" 'updatedRows': 12,\n",
" 'updatedColumns': 5,\n",
" 'updatedCells': 60}}"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"SEEDS = [\n",
" {\"title\": \"To Kill a Mockingbird\", \"author\": \"Harper Lee\", \"year\": 1960},\n",
" {\"title\": \"1984\", \"author\": \"George Orwell\", \"year\": 1949},\n",
" {\"title\": \"The Great Gatsby\", \"author\": \"F. Scott Fitzgerald\", \"year\": 1925},\n",
" {\"title\": \"The Catcher in the Rye\", \"author\": \"J.D. Salinger\", \"year\": 1951},\n",
" {\"title\": \"Pride and Prejudice\", \"author\": \"Jane Austen\", \"year\": 1813},\n",
" {\"title\": \"To the Lighthouse\", \"author\": \"Virginia Woolf\", \"year\": 1927},\n",
" {\"title\": \"The Hobbit\", \"author\": \"J.R.R. Tolkien\", \"year\": 1937},\n",
" {\"title\": \"Moby-Dick\", \"author\": \"Herman Melville\", \"year\": 1851},\n",
" {\"title\": \"Brave New World\", \"author\": \"Aldous Huxley\", \"year\": 1932},\n",
" {\"title\": \"Alice's Adventures in Wonderland\", \"author\": \"Lewis Carroll\", \"year\": 1865},\n",
" {\"title\": \"Harry Potter and the Philosopher's Stone\", \"author\": \"J.K. Rowling\", \"year\": 1997},\n",
" {\"title\": \"Harry Potter and the Chamber of Secrets\", \"author\": \"J.K. Rowling\", \"year\": 1998},\n",
"]\n",
"Book.create_all(SEEDS)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "w3fb_9_Xdmhi"
},
"source": [
"Fetch all records from the sheet:"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "0ilfQfXkcwmn",
"outputId": "9449ba7d-a021-4cdd-8f3d-84dab152ad42"
},
"outputs": [
{
"data": {
"text/plain": [
"12"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"books = Book.all()\n",
"len(books)"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "q0mnDu7Ic2-Q",
"outputId": "a6418b61-684a-47d5-819d-5591942d7a45"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1 | To Kill a Mockingbird | Harper Lee\n",
"2 | 1984 | George Orwell\n",
"3 | The Great Gatsby | F. Scott Fitzgerald\n",
"4 | The Catcher in the Rye | J.D. Salinger\n",
"5 | Pride and Prejudice | Jane Austen\n",
"6 | To the Lighthouse | Virginia Woolf\n",
"7 | The Hobbit | J.R.R. Tolkien\n",
"8 | Moby-Dick | Herman Melville\n",
"9 | Brave New World | Aldous Huxley\n",
"10 | Alice's Adventures in Wonderland | Lewis Carroll\n",
"11 | Harry Potter and the Philosopher's Stone | J.K. Rowling\n",
"12 | Harry Potter and the Chamber of Secrets | J.K. Rowling\n"
]
}
],
"source": [
"for book in books:\n",
" print(book.id, book.title, book.author, sep=\" | \")\n",
" # book.created_at.strftime(\"%Y-%m-%d\")"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "beMrbSlsdvAI"
},
"source": [
"Find record by id:"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "ynt7cK9-drjw",
"outputId": "520617be-d6a8-43e6-814e-55072cc89c88"
},
"outputs": [
{
"data": {
"text/plain": [
"{'id': 3,\n",
" 'title': 'The Great Gatsby',\n",
" 'author': 'F. Scott Fitzgerald',\n",
" 'year': 1925,\n",
" 'created_at': datetime.datetime(2024, 5, 20, 21, 52, 9, 143924, tzinfo=datetime.timezone.utc)}"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"book = Book.find(3)\n",
"dict(book)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "zXbOGHfGdo3_"
},
"source": [
"Filter records on matching conditions:\n"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "Ym0-l6_vd8yi",
"outputId": "31d624f9-d17e-4d61-b17a-08218a7f65e5"
},
"outputs": [
{
"data": {
"text/plain": [
"{'id': 3,\n",
" 'title': 'The Great Gatsby',\n",
" 'author': 'F. Scott Fitzgerald',\n",
" 'year': 1925,\n",
" 'created_at': datetime.datetime(2024, 5, 20, 21, 52, 9, 143924, tzinfo=datetime.timezone.utc)}"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"book = Book.where(author=\"F. Scott Fitzgerald\")[0]\n",
"dict(book)"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "2KsqY75aeIJ-",
"outputId": "75879595-8448-4aaa-acdb-1b79963ba7c1"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"11 | Harry Potter and the Philosopher's Stone | J.K. Rowling\n",
"12 | Harry Potter and the Chamber of Secrets | J.K. Rowling\n"
]
}
],
"source": [
"hp_books = Book.where(author=\"J.K. Rowling\")\n",
"for book in hp_books:\n",
" print(book.id, book.title, book.author, sep=\" | \")"
]
}
],
"metadata": {
"colab": {
"provenance": []
},
"kernelspec": {
"display_name": "Python 3",
"name": "python3"
},
"language_info": {
"name": "python"
}
},
"nbformat": 4,
"nbformat_minor": 0
}