SheetsORM

SheetsORM is a simple micro ORM extension for gspread.

Features:

• Create a entity class for each worksheet
• Columns are mapped to attributes
• Repository: Add,Remove,Update operations
• Type checking for attributes

Requirements

Python 3.9+

Installation

pip install sheetsorm

Basic usage

1.Create credentials in Google API Console

2.Creating an instance:

from sheetsorm.orm import SheetsORM

# Create a SheetsORM instance
gs = SheetsORM(scope=['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'],
        credentials_file='./credentials.json', # pass the path of the credentials file, or google drive URL 
        is_url=False, # True if the credentials file is a google drive URL
        spreadsheet_name='DEV' # pass the name of the spreadsheet
        )

# if the spread sheet is not found, it will be created
gs.connect()

# You should share the spreadsheet with your preferred email address
gs.share('your@email.com','user','owner')

3.Create a class for each worksheet:

from sheetsorm.decorators import entity, column
from datetime import datetime

# define the entity
# the worksheet name by default is the class name
@entity(worksheet='Kat')
class Kat:
    # our primary key auto_increment with non-nullable values
    # name defines the column name in the worksheet, by default it is the attribute name
    id = column(int,name='ID',primary_key=True,increment=True, required=True)
    # pass the length of the column as argument to 'str'
    name = column(str(200),name='NAME',required=True)
    # or use as a string 
    color = column('20',name='COLOR')


# the entity decorator add a new constructor that allows you to create an instance
# from key values
meow  = Kat(name='Meow',birth_date=datetime(2019,1,1),color='black')

repo = gs.get_repository(Kat)

# add the object
repo.add(meow)
# and then commit the changes
repo.commit()

# pass lambda function to filter the results
find_meow = repo.find(lambda x: x.name=='Meow')
if len(find_meow) == 1:
    print('I Found Meow')
    print(f'His id: {find_meow[0].id}')
    print(f'His name: {find_meow[0].name}')
    print(f'His birth date: {find_meow[0].birth_date}')
    print(f'His color: {find_meow[0].color}')
else:
    print('I did not find Meow')


meow = find_meow[0]
meow.name = 'Meow2'
# Runtime type checkings
# meow.color  = 1  # TypeError: color must be of type 'str'
repo.update(meow)
repo.commit()

# Remove the row from the worksheet
repo.remove()
repo.commit()

# Other functions to use:
# repo.all(lambda x: x.color=='orange')
# repo.any(lambda x: x.name=='Mr. Potato')