spreadsheet-db 1.0

Simply use Google Spreadsheet as DB in Python.

Spreadsheet DB

---

This package helps you use Spreadsheet as DB in python.

📝 Table of Contents

• About
• Getting Started
• Deployment
• Usage
• Built Using
• TODO
• Contributing
• Authors
• Acknowledgments

🧐 About

This package helps you use Spreadsheet as DB for developers.

🏁 Getting Started

Installing

pip install spreadsheet_db

Prerequisites

🌱 (Required) Download Spreadsheet Authentication JSON

1. From Google Cloud Platform Console, create project or select an existing project.

2. Enable Google Drive API, if is not already enabled.

3. Enable Google Sheet API, if is not already enabled.

4. From Google Credential choose Create credentials > Service Account.

   

5. Fill out the form and click Create Key

   

6. Click the account you just created, from the Service Accounts list.

7. click the ADD KEY > Create new key

   

8. Set the Key type to JSON and click CREATE button

   

9. The JSON file automatically saved, which is used to authenticate the spreadsheet.

   

🌱 (Required) Creating an Instance

Authentication JSON is required to create an instance. If you have not downloaded the Authentication JSON, refer to here.

1. Create new Spreadsheet.

   

2. Add Column Names and remember Spreadsheet ID.

   

3. Change Sheet1 to the name you want to use. in this package, uses Sheet as Table. if you want, create a new table.

   

4. Create an instance using an code below.

   import spreadsheet_db
   
   with open("your_auth_json_file_name.json") as fp:
       auth_json = fp.read()
   
   sheet_id = "Your Spreadsheet ID"
   sheet_name= "Your Sheet Name"
   
   # INPUT a list of column names to give the UNIQUE option. Duplicate values are not entered for the UNIQUE column.
   unique_columns = ["Column Name to use UNIQUE"]
   
   sdb = spreadsheet_db.SpreadSheetDB(
       auth_json, sheet_id, sheet_name, unique_columns=unique_columns)
   

5. From now on, you can use the spreadsheet as a DB 🎉

🎈 Usage

Please check Prerequisites before starting Usage.

🌱 SELECT

Use this function to get data from a table.

Parameters

• condition: pandas.core.frame.DataFrame

  You can use pandas dataframe indexing like this (sdb is an instnace of this class.):

  sdb.table["name"] == "Park"
  

• columns: list

  If you want to select all columns, leave it blank.

• orient: str

  Between records or list, You can select the shape of the output value.

Examples

1. example 1

   >>> sdb.select(sdb.table["name"].isin(["Park", "Lee"]), ["name", "email"])
   [{'name': 'Park', 'email': '[e-mail]'}, {'name': 'Lee', 'email': '[e-mail]'}]
   

2. example 2

   >>> sdb.select(sdb.table["name"] == "Park")
   [{'index': '34', 'name': 'Park', 'phone_number': '01022223333', 'email': '[e-mail]'}]
   

3. example 3

   >>> sdb.select(orient="list")
   {'index': ['34', '35', '36'], 'name': ['Park', 'Lee', 'Han'], 'phone_number': ['01022223333', '01055556666', '01077778888'], 'email': ['[e-mail]', '[e-mail]', '[e-mail]']}
   

🌱 INSERT

Use this function to insert data into the table.

Parameters

• data: dict

  the dict key is column name and the dict value is value. use like this.

  { "name": "Lee" }
  

Examples

• This code insert data to the table.

  >>> sdb.insert({"name": "Park", "phone_number": "01022223333", "email": "[e-mail]"})
  

🌱 UPDATE

Use this function to update table.

Parameters

• condition: pandas.core.frame.DataFrame

  You can use pandas dataframe indexing like this (sdb is an instnace of this class.):

  sdb.table["name"] == "Park"
  

• data: dict

  the dict key is column name and the dict value is value. use like this.

  { "name": "Lee" }
  

Examples

• This code finds the rows where the name column is Park and replace name with Lee.

  >>> sdb.update(sdb.table["name"] == "Park", { "name" : "Lee" })
  

🌱 UPSERT

Use this function to upsert table. Update if condition exists else insert data.

Parameters

• condition: pandas.core.frame.DataFrame You can use pandas dataframe indexing like this (sdb is an instnace of this class.):

  sdb.table["name"] == "Park"
  

• data: dict the dict key is column name and the dict value is value. use like this.

  { "name": "Lee" }        
  

Examples

• This code finds the rows where the name column is Park, replace name with Lee if condition exists else insert data.

  >>> sdb.upsert(sdb.table["name"] == "Park", { "name" : "Lee" })
  

🌱 DELETE

Use this function to delete rows from the table.

Parameters

• condition: pandas.core.frame.DataFrame

  You can use pandas dataframe indexing like this (sdb is an instnace of this class.):

  sdb.table["name"] == "Park"
  

Examples

• This code finds the rows where the name column is Park and deletes the rows.

  >>> sdb.delete(sdb.table["name"] == "Park")
  

🎉 Acknowledgements

• Title icon made by Freepik.

• Please help develop this project 😀

• Thanks for reading 😄