# 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:
```python
pip install gs-sql
```
Or from Github:
```python
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.
```python
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.
```python
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.
```python
query = gs.execute("CREATE TABLE Users (id, name)")
```
Final code.
```python
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
```python
gs = SheetsQL()
gs.authorization("files//credentials.json")
new_base = gs.execute("""CREATE DATABASE NewBase""")
sql.connect(new_base)
```
#### CREATE TABLE
```python
query = gs.execute("CREATE TABLE Users (id, name)")
```
```python
query = gs.execute("CREATE TABLE IF NOT EXISTS Users (id, name)")
```
#### ALTER TABLE
```python
query = gs.execute("ALTER TABLE Users DROP COLUMN id;")
```
```python
query = gs.execute("ALTER TABLE Users RENAME COLUMN name TO userName;")
```
```python
query = gs.execute("ALTER TABLE Users ALTER COLUMN NewId, NewName;")
```
#### DROP TABLE
```python
query = gs.execute("DROP TABLE Users;")
```
#### SELECT
```python
query = gs.execute("SELECT * FROM tableName;")
```
```python
query = gs.execute("SELECT col1, col2, ...colN FROM tableName;")
```
```python
query = gs.execute("SELECT DISTINCT col1, col2, ...colN FROM tableName;")
```
```python
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition;""")
```
```python
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND|OR condition2;""")
```
```python
query = gs.execute("""SELECT col2, col2, ...colN
FROM tableName
WHERE colName IN (val1, val2, ...valN);""")
```
```python
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE colName BETWEEN val1 AND val2;""")
```
```python
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE colName LIKE pattern;""")
```
```python
query = gs.execute("""SELECT col1, col2, ...colN
FROM tableName
WHERE condition
ORDER BY colName;""")
```
```python
query = gs.execute("""SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName;""")
```
```python
query = gs.execute("""SELECT COUNT(colName)
FROM tableName
WHERE condition;""")
```
#### INNER JOIN
```python
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
```python
query = gs.execute("""INSERT INTO table_name
VALUES (value1, value2, value3, ...);""")
```
#### UPDATE
```python
query = gs.execute("""UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;""")
```
#### DELETE
```python
query = gs.execute("""DELETE FROM table_name WHERE condition;""")
```
---
## Configuration
```python
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
Raw data
{
"_id": null,
"home_page": "https://github.com/EvgeniBondarev/PyGoogleSheetAPI",
"name": "gs-sql",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "EvgeniBondarev",
"author_email": "bondareff7@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/dc/05/3be591328254b62638eeb8c80f51b6344f15b1c5541df9416537dab31e44/gs_sql-0.0.4.tar.gz",
"platform": null,
"description": "# gs-sql\r\n\r\n    \r\n\r\n**gs-sql** - \r\nthis module is a Python client library for the GoogleSheetAPI data project management platform using SQL\r\n\r\n## Installation\r\n\r\nInstall the current version with PyPI:\r\n\r\n```python\r\npip install gs-sql\r\n```\r\n\r\nOr from Github:\r\n```python\r\nhttps://github.com/EvgeniBondarev/gs-sql/archive/refs/heads/main.zip\r\n```\r\n\r\n## Usage\r\n\r\nAt 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.\r\n\r\n```python\r\nfrom gs_sql.sheetsql import SheetsQL\r\n\r\ngs = SheetsQL()\r\n\r\ngs.authorization(\"files//credentials.json\")\r\n```\r\nAfterwards, you can create a table/database in Google Sheets either using standard methods or through SQL queries.\r\n```python\r\nfrom gs_sql.dataclasses import GsDataBase\r\n\r\nnew_base = gs.execute(\"\"\"CREATE DATABASE NewBase\"\"\")\r\n\r\ngs.connect(new_base) # or gs.connect(GsDataBase(id=\"1fB_1uqU8FklXAQdn9XG4QK3HG4l5U0_vvM3abQ3aiuE\", name=\"NewBase\"))\r\n```\r\nFinally, you can now create your first table.\r\n```python\r\nquery = gs.execute(\"CREATE TABLE Users (id, name)\")\r\n```\r\nFinal code.\r\n```python\r\nfrom gs_sql.sheetsql import SheetsQL\r\n\r\n\r\ngs = SheetsQL()\r\n\r\ngs.authorization(\"files//credentials.json\")\r\n\r\nnew_base = gs.execute(\"\"\"CREATE DATABASE NewBase\"\"\")\r\n\r\ngs.connect(new_base)\r\n\r\nquery = gs.execute(\"CREATE TABLE Users (id, name)\")\r\nprint(query)\r\n```\r\n## Available types of sql queries\r\n### DDL\r\n\r\n| No. | Command | Description |\r\n|----|--------|------------------------------------------------------------------------------------|\r\n| 1 | CREATE | Creates a new table, view, or other object in the database |\r\n| 2 | ALTER | Modifies an existing object in the database, such as a table |\r\n| 3 | DROP | Deletes an existing table, view, or other object in the database |\r\n\r\n### DML\r\n\r\n| N | Command | Description |\r\n|----|---------|---------------------------------------------------|\r\n| 1 | SELECT | Extracts records from one or more tables |\r\n| 2 | INSERT | Creates records |\r\n| 3 | UPDATE | Modifies records |\r\n| 4 | DELETE | Deletes records |\r\n\r\n## Examples\r\n#### CREATE DATABASE\r\n```python\r\ngs = SheetsQL()\r\n\r\ngs.authorization(\"files//credentials.json\")\r\n\r\nnew_base = gs.execute(\"\"\"CREATE DATABASE NewBase\"\"\")\r\n\r\nsql.connect(new_base)\r\n```\r\n#### CREATE TABLE\r\n```python\r\n query = gs.execute(\"CREATE TABLE Users (id, name)\")\r\n```\r\n```python\r\n query = gs.execute(\"CREATE TABLE IF NOT EXISTS Users (id, name)\")\r\n```\r\n#### ALTER TABLE\r\n```python\r\nquery = gs.execute(\"ALTER TABLE Users DROP COLUMN id;\")\r\n```\r\n```python\r\nquery = gs.execute(\"ALTER TABLE Users RENAME COLUMN name TO userName;\")\r\n```\r\n```python\r\nquery = gs.execute(\"ALTER TABLE Users ALTER COLUMN NewId, NewName;\")\r\n```\r\n#### DROP TABLE\r\n```python\r\nquery = gs.execute(\"DROP TABLE Users;\")\r\n```\r\n#### SELECT\r\n```python\r\nquery = gs.execute(\"SELECT * FROM tableName;\")\r\n```\r\n```python\r\nquery = gs.execute(\"SELECT col1, col2, ...colN FROM tableName;\")\r\n```\r\n```python\r\nquery = gs.execute(\"SELECT DISTINCT col1, col2, ...colN FROM tableName;\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col1, col2, ...colN\r\n FROM tableName\r\n WHERE condition;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col1, col2, ...colN\r\n FROM tableName\r\n WHERE condition1 AND|OR condition2;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col2, col2, ...colN\r\n FROM tableName\r\n WHERE colName IN (val1, val2, ...valN);\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col1, col2, ...colN\r\n FROM tableName\r\n WHERE colName BETWEEN val1 AND val2;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col1, col2, ...colN\r\n FROM tableName\r\n WHERE colName LIKE pattern;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT col1, col2, ...colN\r\n FROM tableName\r\n WHERE condition\r\n ORDER BY colName;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT SUM(colName)\r\n FROM tableName\r\n WHERE condition\r\n GROUP BY colName;\"\"\")\r\n```\r\n```python\r\nquery = gs.execute(\"\"\"SELECT COUNT(colName)\r\n FROM tableName\r\n WHERE condition;\"\"\")\r\n```\r\n#### INNER JOIN\r\n```python\r\nquery = gs.execute(\"\"\"SELECT Orders.order_id, Orders.order_date, Customers. customer_name\r\n FROM Orders\r\n INNER JOIN Customers ON Orders.customer_id = customer_id\"\"\")\r\n```\r\n#### INSERT INTO\r\n```python\r\nquery = gs.execute(\"\"\"INSERT INTO table_name\r\n VALUES (value1, value2, value3, ...);\"\"\")\r\n```\r\n#### UPDATE\r\n```python\r\nquery = gs.execute(\"\"\"UPDATE table_name\r\n SET column1 = value1, column2 = value2, ...\r\n WHERE condition;\"\"\")\r\n```\r\n#### DELETE \r\n```python\r\nquery = gs.execute(\"\"\"DELETE FROM table_name WHERE condition;\"\"\")\r\n```\r\n---\r\n## Configuration\r\n```python\r\nfrom gs_api.sheetsql import SheetsQL\r\nfrom gs_api.dataclasses import ResponseType\r\n\r\n\r\ngs = SheetsQL()\r\n\r\ngs.authorization(\"files//credentials.json\")\r\n\r\nsql.set_configuration(colum_color=[(0.85, 0.85, 0.85)], # Color in RGB format\r\n response_type=ResponseType.List) # Standard List or Pandas DataFrame\r\n```s\r\n",
"bugtrack_url": null,
"license": "Apache License, Version 2.0, see LICENSE file",
"summary": "Ability to use SQL when working with GoogleSheetAPI",
"version": "0.0.4",
"project_urls": {
"Download": "https://github.com/EvgeniBondarev/PyGoogleSheetAPI/archive/refs/heads/main.zip",
"Homepage": "https://github.com/EvgeniBondarev/PyGoogleSheetAPI"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "dc053be591328254b62638eeb8c80f51b6344f15b1c5541df9416537dab31e44",
"md5": "1e92b8b4be72c96a0eb6e85d6228bfba",
"sha256": "64ae31dd175338432964bc36df510c514bf3e1fe9578d4a6b96163532db7886f"
},
"downloads": -1,
"filename": "gs_sql-0.0.4.tar.gz",
"has_sig": false,
"md5_digest": "1e92b8b4be72c96a0eb6e85d6228bfba",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 11301,
"upload_time": "2023-07-30T19:21:20",
"upload_time_iso_8601": "2023-07-30T19:21:20.223982Z",
"url": "https://files.pythonhosted.org/packages/dc/05/3be591328254b62638eeb8c80f51b6344f15b1c5541df9416537dab31e44/gs_sql-0.0.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-07-30 19:21:20",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "EvgeniBondarev",
"github_project": "PyGoogleSheetAPI",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "gs-sql"
}
JSON
