Simply use Google Spreadsheet as DB in Python.
Project description
Spreadsheet DB
This package helps you use Spreadsheet as DB in python.
📝 Table of Contents
🧐 About
This package helps you use Spreadsheet as DB for developers.
🏁 Getting Started
Installing
pip install spreadsheet_db
Prerequisites
🌱 (Required) Download Spreadsheet Authentication JSON
-
From Google Cloud Platform Console,
create project
orselect an existing project
. -
Enable Google Drive API, if is not already enabled.
-
Enable Google Sheet API, if is not already enabled.
-
From Google Credential choose
Create credentials
>Service Account
. -
Fill out the form and click
Create Key
-
Click the
account you just created
, from theService Accounts list
. -
click the
ADD KEY
>Create new key
-
Set the
Key type
to JSON and clickCREATE
button -
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.
-
Create new Spreadsheet.
-
Add
Column Names
and rememberSpreadsheet ID
. -
Change
Sheet1
to the name you want to use. in this package, usesSheet
asTable
. if you want, create a new table. -
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)
-
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.DataFrameYou can use pandas dataframe indexing like this (sdb is an instnace of this class.):
sdb.table["name"] == "Park"
-
columns
: listIf you want to select all columns, leave it blank.
-
orient
: strBetween
records
orlist
, You can select the shape of the output value.
Examples
-
example 1
>>> sdb.select(sdb.table["name"].isin(["Park", "Lee"]), ["name", "email"]) [{'name': 'Park', 'email': 'Park@google.com'}, {'name': 'Lee', 'email': 'Lee@google.com'}]
-
example 2
>>> sdb.select(sdb.table["name"] == "Park") [{'index': '34', 'name': 'Park', 'phone_number': '01022223333', 'email': 'Park@google.com'}]
-
example 3
>>> sdb.select(orient="list") {'index': ['34', '35', '36'], 'name': ['Park', 'Lee', 'Han'], 'phone_number': ['01022223333', '01055556666', '01077778888'], 'email': ['Park@google.com', 'Lee@google.com', 'Han@google.com']}
🌱 INSERT
Use this function to insert data into the table.
Parameters
-
data
: dictthe 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": "Park@google.com"})
🌱 UPDATE
Use this function to update table.
Parameters
-
condition
: pandas.core.frame.DataFrameYou can use pandas dataframe indexing like this (sdb is an instnace of this class.):
sdb.table["name"] == "Park"
-
data
: dictthe 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 isPark
and replacename
withLee
.>>> 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 isPark
, replacename
withLee
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.DataFrameYou 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 isPark
and deletes the rows.>>> sdb.delete(sdb.table["name"] == "Park")
🎉 Acknowledgements
-
Title icon made by Freepik.
-
Please help develop this project 😀
-
Thanks for reading 😄
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for spreadsheet_db-1.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | a91c51c112294a3dfa451084b0a690d28f97f90e4ec342fcdc4850e8948ad4ee |
|
MD5 | 3aff45929b9dcf2ad7e65a0a9b3f590a |
|
BLAKE2b-256 | 10f7a9a0eb59f4353ea5f80e2aea2b6b39dd9bde55f9cebcdb671afe4e2f9563 |