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