sqlite-gsheet

add-on utility for simple python apps to use sqlite as storage and google sheets as user interface

setup

see below for user data configuration from non-default directory

1. configure service account for google sheets api search online sources for steps to setup a service account example: robocorp article: how to read from and write into Google Sheets for your robots

   take note of your service account client_secret.json and client_email

2. install the module into your python project from the github repository url using pip

   pip install git+https://github.com/taylorhickem/sqlite-gsheet.git

3. upload the client_secret.json to your sqlgsheet package instance. you only need to do this once.

(venv) >python -m sqlgsheet.database load_client_secret

or to specify a path other than the default 'client_secret.json' from the working diectory :

(venv) >python -m sqlgsheet.database load_client_secret <path to client secret file>

4. create your google spreadsheet

   demo sheet myapp

   take note of

   • the workbookId, read from the url ../spreadsheets/d/workbookId
   • the ranges of interest and give them names

5. grant write access to your service account

   select share and add your client_email with write priviledges for your sheet

6. configure your gsheet_config.json file to match your spreadsheet

sample code

For details see the demo project \myapp and sample myapp.py

run from terminal

\myapp>python myapp.py

run from python interpreter

import myapp

myapp.update()

load from gsheet into pandas DataFrame form

from sqlgsheet import database as db

db.load()

form = db.get_sheet('myapp', 'form')

post to gsheet from pandas DataFrame form_responses

from sqlgsheet import database as db

db.load()

form_responses = db.get_sheet('myapp', 'form')

records = db.get_sheet('myapp', 'records')

records = records.append(form_responses)

db.post_to_gsheet(records, 'myapp', 'records', input_option='USER_ENTERED')

##sqlite features to be elaborated in future version of the documentation##

sample files

sample client_secret.json

{ "type": "service_account", "project_id": "helvasheets", "private_key_id": "###", "private_key": "###", "client_email": "[email protected]", "client_id": int, "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets%40helvasheets.iam.gserviceaccount.com" }

sample gsheet_config

{ "myapp": { "wkbid": "1T8JCGdsTAjr8820l-iSHKnPlZFM2C7MKDLQLcoQA-sk", "sheets": { "config": { "data": "config!A2:D", "header": "config!A1:D1" }, "records": { "data": "records!A2:C", "header": "records!A1:C1", "data_types": { "date": "date", "parameter": "str", "value": "float" } }, "form": { "data": "form!A3:C", "header": "form!A2:C2", "data_types": { "date": "date", "parameter": "str", "value": "float" } } } } }

User data from non-default directory

For some use cases, such as with use in AWS lambda, you need to set user data from a custom directory location other than the runtime directory.

before calling any methods from database.py, first run .set_user_data() and specify the full path to the user data files identified with keywords.

db.set_user_data(
    gsheet_config='/path/gsheet_config.json'
    gas_client_secret='/path/.../client_secret.json'
    mysql_credentials='/path/.../mysql_credentials.json'
)

database sync

for details refer to the issue database sync

for two identical dataset schemas,one MASTER and one SLAVE, whose unique columns are identified by some column_subset and contain a last_updated field:

the database sync will

1. compare the two datasets and
2. merges updates from the SLAVE to the MASTER
3. update the SLAVE to match the master
4. with the conditions that
   • rows can only be deleted from the MASTER
   • rows can be added or updated in either the MASTER or the SLAVE

The user specifies the database sync settings with a dbsync_config.json file

dbsync_config.json

{
  "master": {
    "db_type": "sqlite",
    "database": "sqlite:///myapp.db"
  },
  "slave": {
    "db_type": "mysql",
    "database": "hours",
    "host": "127.0.0.1",
    "login": "mysql+pymysql://{user}:{pw}@127.0.0.1/{db}",
    "username": "admin",
    "password": "P@ssword"
  },
  "tables": {
    "last_modified": "last_modified",
    "event": {
      "key": [
        "timestamp"
      ]
    }
  }
}

To run the sync, use the sqlgsheet.sync module

from sqlgsheet import sync

sync.config('dbsync_config.json')
sync.db_connect() # optional, first part of sync.update()
sync.update()

Alternatively, you can use the command line interface

(env) >python -m sqlgsheet.sync update dbsync_config.json