Ability to use SQL when working with GoogleSheetAPI
Project description
gs-sql
gs-sql - this module is a Python client library for the GoogleSheetAPI data project management platform using SQL
Installation
Install the current version with PyPI:
pip install gs-sql
Or from Github:
https://github.com/EvgeniBondarev/gs-sql/archive/refs/heads/main.zip
Usage
At the first login, a browser window will open for confirmation, and a token.pickle file will be created in the directory where your credentials.json file is located to interact with the API.
from gs_sql.sheetsql import SheetsQL
gs = SheetsQL()
gs.authorization("files//credentials.json")
Afterwards, you can create a table/database in Google Sheets either using standard methods or through SQL queries.
from gs_sql.dataclasses import GsDataBase
new_base = gs.execute("""CREATE DATABASE NewBase""")
gs.connect(new_base) # or gs.connect(GsDataBase(id="1fB_1uqU8FklXAQdn9XG4QK3HG4l5U0_vvM3abQ3aiuE", name="NewBase"))
Finally, you can now create your first table.
query = gs.execute("CREATE TABLE Users (id, name)")
Final code.
from gs_sql.sheetsql import SheetsQL
gs = SheetsQL()
gs.authorization("files//credentials.json")
new_base = gs.execute("""CREATE DATABASE NewBase""")
gs.connect(new_base)
query = gs.execute("CREATE TABLE Users (id, name)")
print(query)
Available types of sql queries
DDL
No. | Command | Description |
---|---|---|
1 | CREATE | Creates a new table, view, or other object in the database |
2 | ALTER | Modifies an existing object in the database, such as a table |
3 | DROP | Deletes an existing table, view, or other object in the database |
DML
N | Command | Description |
---|---|---|
1 | SELECT | Extracts records from one or more tables |
2 | INSERT | Creates records |
3 | UPDATE | Modifies records |
4 | DELETE | Deletes records |
Examples
CREATE DATABASE
gs = SheetsQL()
gs.authorization("files//credentials.json")
new_base = gs.execute("""CREATE DATABASE NewBase""")
sql.connect(new_base)
CREATE TABLE
query = gs.execute("CREATE TABLE Users (id, name)")
query = gs.execute("CREATE TABLE IF NOT EXISTS Users (id, name)")
ALTER TABLE
query = gs.execute("ALTER TABLE Users DROP COLUMN id;")
query = gs.execute("ALTER TABLE Users RENAME COLUMN name TO userName;")
query = gs.execute("ALTER TABLE Users ALTER COLUMN NewId, NewName;")
DROP TABLE
query = gs.execute("DROP TABLE Users;")
SELECT
query = gs.execute("SELECT * FROM tableName;")
query = gs.execute("SELECT col1, col2, ...colN FROM tableName;")
query = gs.execute("SELECT DISTINCT col1, col2, ...colN FROM tableName;")
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition;""")
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND|OR condition2;""")
query = gs.execute("""SELECT col2, col2, ...colN
FROM tableName
WHERE colName IN (val1, val2, ...valN);""")
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE colName BETWEEN val1 AND val2;""")
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE colName LIKE pattern;""")
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition
ORDER BY colName;""")
query = gs.execute("""SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName;""")
query = gs.execute("""SELECT COUNT(colName)
FROM tableName
WHERE condition;""")
INNER JOIN
query = gs.execute("""SELECT Orders.order_id, Orders.order_date, Customers. customer_name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = customer_id""")
INSERT INTO
query = gs.execute("""INSERT INTO table_name
VALUES (value1, value2, value3, ...);""")
UPDATE
query = gs.execute("""UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;""")
DELETE
query = gs.execute("""DELETE FROM table_name WHERE condition;""")
Configuration
from gs_api.sheetsql import SheetsQL
from gs_api.dataclasses import ResponseType
gs = SheetsQL()
gs.authorization("files//credentials.json")
sql.set_configuration(colum_color=[(0.85, 0.85, 0.85)], # Color in RGB format
response_type=ResponseType.List) # Standard List or Pandas DataFrame
```s
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
gs_sql-0.0.4.tar.gz
(11.3 kB
view hashes)