| Name | rest-db-api JSON |
| Version |
0.0.3
JSON |
| download |
| home_page | |
| Summary | A rest db api designed to be integrated with apache superset |
| upload_time | 2023-08-20 07:15:32 |
| maintainer | |
| docs_url | None |
| author | |
| requires_python | >=3.7 |
| license | MIT License Copyright (c) 2023 Satvik Nema Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| keywords |
rest
dbapi
sqlalchemy
adapter
superset
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
|
# REST-DB-API
This is a plugin to be used with apache-superset, for importing data via REST APIs.
It builds on top of [shillelagh's](https://github.com/betodealmeida/shillelagh) generic json adapter.
To get started, just do:
```bash
pip install rest-db-api
```
Motivation
1. Generic json adapter did not support request bodies and headers.
2. Independence to specify http/https
4. `rest` dialect enables this adapter to be used with apache superset.
5. The dialect enables us to set a base URL, and query multiple endpoints with the same 'connection'.
# Examples
#### GET requests
Querying [weather api](https://www.weatherapi.com/)
Lets assume I am querying for 3 days weather forecast for Bangalore. The response gives the results by hour.
```curl
https://api.weatherapi.com/v1/forecast.json?key={{your_key}}&q=Bangalore&days=3&aqi=no&alerts=no
```
You can get a free key by creating an account there.
You can refer [this file](https://github.com/SatvikNema/rest-db-api/blob/main/examples/sample-weather-response.json) to check the response structure.
We can query this with `rest-db-api`:
```python
from sqlalchemy import create_engine
from rest_db_api.utils import get_virtual_table
engine = create_engine("rest://api.weatherapi.com?ishttps=1")
endpoint = '/v1/forecast.json'
params = {
'key': 'your_key',
'q': 'Bangalore',
'days': 5
}
jsonpath = "$.forecast.forecastday[*]"
virtual_table = get_virtual_table(endpoint=endpoint,
params=params,
jsonpath=jsonpath)
connection = engine.connect()
for i in connection.execute(f'SELECT * FROM "{virtual_table}"'):
print(i)
```
The response should return an array of objects/primitives. If not, we need to specify where in the response the array is (using `jsonpath`). In this case that is at `$.forecast.forecastday[*]`
As Shillelagh's `Adapter` class uses in memory storage - `sqllite` , we can query the data using `sqllite` syntax.
```python
query = f"""
SELECT
date,
json_extract(day, "$.maxtemp_c") as max_temp_celsius,
json_extract(hour, "$[6].temp_c") as six_am_celsius,
json_extract(hour, "$[6].will_it_rain") as will_it_rain
FROM
"{virtual_table}"
"""
for i in connection.execute(query):
print(i)
```
#### POST request with headers and request body
Consider this sample request
```javascript
curl --location -g --request POST 'https://some.api.com/some/api/path?a=60&c=someQuery&b=-50#$[*]' \
--header 'Content-Type: application/json' \
--header 'IAM_ID: satvik' \
--header 'ENVIRONMENT: staging:1.5.3' \
--header 'NAME: MY-REST-SERVICE' \
--data-raw '{
"name": "satvik",
"interests": [
{
"name": "badminton",
"category": "sports",
"stats": {
"racket": "intermediate",
"shuttle": "yonex mavis 500"
}
},
{
"name": "programming",
"category": "computers",
"stats": {
"laptop": "yw",
"mouse": "5D ergonomic",
"keyboard": "broken"
}
}
]
}'
```
To query this with db-api, follow the snippet:
```python
from sqlalchemy import create_engine
from rest_db_api.utils import get_virtual_table
engine = create_engine("rest://some.api.com?ishttps=1")
endpoint = '/some/api/path'
params = {
"a": 60,
"b": -50,
"c": "someQuery"
}
headers = {
'Content-Type': 'application/json',
'IAM_ID': 'satvik',
'ENVIRONMENT': 'staging:1.5.3',
'NAME': 'MY-REST-SERVICE',
}
body = {
"name": "satvik",
"interests": [
{
"name": "badminton",
"category": "sports",
"stats": { "racket": "intermediate", "shuttle": "yonex mavis 500" }
},
{
"name": "programming",
"category": "computers",
"stats": {
"laptop": "mac book pro",
"mouse": "5D ergonomic",
"keyboard": "broken"
}
}
]
}
jsonpath = "#$[*]" # set this according to your response
virtual_table = get_virtual_table(endpoint=endpoint,
params=params,
headers=headers,
body=body,
jsonpath=jsonpath)
for i in connection.execute(f'SELECT * FROM "{virtual_table}"'):
print(i)
```
### Usage with apache-superset
1. Go to Connect database and add database
2. Select Shillelagh
3. add the connection string with `rest://` prefix
eg: `rest://api.weatherapi.com?ishttps=1`
4. Gice your connection a name: eg `rest-weather-api`
5. Click test connection and then add
6. Go to SQL lab and select `rest-weather-api` from database.
7. You can leave schema empty and query directly!!
Query is:
```SQL
SELECT date,
json_extract(day, "$.maxtemp_c") as max_temp_celsius,
json_extract(hour, "$[6].temp_c") as six_am_celsius,
json_extract(hour, "$[6].will_it_rain") as will_it_rain
FROM "/v1/forecast.json?key={your_key}&q=Bangalore&days=5#$.forecast.forecastday[*]";
```
Tables and schema is empty, because there's no concept for tables in REST APIs.
It returns a default message. That message is configured in `rest_api_dialect.py`
### Getting the virtual table
In superset's SQL lab, we're directly using
```python
/v1/forecast.json?key={your_key}&q=Bangalore&days=5#$.forecast.forecastday[*]
```
To get the similar virtual table address for your endpoint (it may have headers or even body), use the utility `rest_db_api.utils.get_virtual_table` and pass in your configs.
- [x] POST requests (request body)
- [x] headers
- [ ] adding write support to adapter (for PUT/DELETE requests)
Raw data
{
"_id": null,
"home_page": "",
"name": "rest-db-api",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "",
"keywords": "rest,dbapi,sqlalchemy,adapter,superset",
"author": "",
"author_email": "Satvik Nema <saviknema@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/98/45/8ebb54c7a0491766702bc1e06a43b827ddc87cbea0dd737dddf313481d9e/rest-db-api-0.0.3.tar.gz",
"platform": null,
"description": "\n# REST-DB-API \n \nThis is a plugin to be used with apache-superset, for importing data via REST APIs.\n\nIt builds on top of [shillelagh's](https://github.com/betodealmeida/shillelagh) generic json adapter.\n\nTo get started, just do:\n```bash\npip install rest-db-api\n```\n\nMotivation\n1. Generic json adapter did not support request bodies and headers.\n2. Independence to specify http/https\n4. `rest` dialect enables this adapter to be used with apache superset. \n5. The dialect enables us to set a base URL, and query multiple endpoints with the same 'connection'.\n\n\n# Examples\n\n#### GET requests\nQuerying [weather api](https://www.weatherapi.com/)\n\nLets assume I am querying for 3 days weather forecast for Bangalore. The response gives the results by hour.\n```curl\nhttps://api.weatherapi.com/v1/forecast.json?key={{your_key}}&q=Bangalore&days=3&aqi=no&alerts=no\n```\nYou can get a free key by creating an account there.\n\nYou can refer [this file](https://github.com/SatvikNema/rest-db-api/blob/main/examples/sample-weather-response.json) to check the response structure.\nWe can query this with `rest-db-api`:\n```python\nfrom sqlalchemy import create_engine \nfrom rest_db_api.utils import get_virtual_table \n \nengine = create_engine(\"rest://api.weatherapi.com?ishttps=1\") \n \nendpoint = '/v1/forecast.json' \nparams = { \n 'key': 'your_key', \n 'q': 'Bangalore', \n 'days': 5 \n} \njsonpath = \"$.forecast.forecastday[*]\" \nvirtual_table = get_virtual_table(endpoint=endpoint, \n params=params, \n jsonpath=jsonpath) \nconnection = engine.connect() \nfor i in connection.execute(f'SELECT * FROM \"{virtual_table}\"'): \n print(i)\n```\n\nThe response should return an array of objects/primitives. If not, we need to specify where in the response the array is (using `jsonpath`). In this case that is at `$.forecast.forecastday[*]`\n\nAs Shillelagh's `Adapter` class uses in memory storage - `sqllite` , we can query the data using `sqllite` syntax.\n```python\nquery = f\"\"\" \nSELECT \n date,\n json_extract(day, \"$.maxtemp_c\") as max_temp_celsius, \n json_extract(hour, \"$[6].temp_c\") as six_am_celsius, \n json_extract(hour, \"$[6].will_it_rain\") as will_it_rain\nFROM \n \"{virtual_table}\" \n\"\"\" \nfor i in connection.execute(query): \n print(i)\n```\n\n#### POST request with headers and request body\n\nConsider this sample request\n```javascript\ncurl --location -g --request POST 'https://some.api.com/some/api/path?a=60&c=someQuery&b=-50#$[*]' \\\n--header 'Content-Type: application/json' \\\n--header 'IAM_ID: satvik' \\\n--header 'ENVIRONMENT: staging:1.5.3' \\\n--header 'NAME: MY-REST-SERVICE' \\\n--data-raw '{\n \"name\": \"satvik\",\n \"interests\": [\n {\n \"name\": \"badminton\",\n \"category\": \"sports\",\n \"stats\": {\n \"racket\": \"intermediate\",\n \"shuttle\": \"yonex mavis 500\"\n }\n },\n {\n \"name\": \"programming\",\n \"category\": \"computers\",\n \"stats\": {\n \"laptop\": \"yw\",\n \"mouse\": \"5D ergonomic\",\n \"keyboard\": \"broken\"\n }\n }\n ]\n}'\n```\n\nTo query this with db-api, follow the snippet:\n```python\nfrom sqlalchemy import create_engine \nfrom rest_db_api.utils import get_virtual_table\n \nengine = create_engine(\"rest://some.api.com?ishttps=1\")\n\nendpoint = '/some/api/path'\n\nparams = {\n\t\"a\": 60,\n\t\"b\": -50,\n\t\"c\": \"someQuery\"\n}\n\nheaders = { \n 'Content-Type': 'application/json', \n 'IAM_ID': 'satvik', \n 'ENVIRONMENT': 'staging:1.5.3', \n 'NAME': 'MY-REST-SERVICE', \n} \n \nbody = { \n \"name\": \"satvik\", \n \"interests\": [ \n { \n \"name\": \"badminton\", \n \"category\": \"sports\", \n \"stats\": { \"racket\": \"intermediate\", \"shuttle\": \"yonex mavis 500\" } \n }, \n { \n \"name\": \"programming\", \n \"category\": \"computers\", \n \"stats\": { \n \"laptop\": \"mac book pro\", \n \"mouse\": \"5D ergonomic\", \n \"keyboard\": \"broken\" \n } \n } \n ] \n} \n\njsonpath = \"#$[*]\" # set this according to your response \n\nvirtual_table = get_virtual_table(endpoint=endpoint, \n\t\t\t\t\t\t\t\t params=params,\n headers=headers, \n body=body, \n jsonpath=jsonpath) \n \nfor i in connection.execute(f'SELECT * FROM \"{virtual_table}\"'): \n print(i)\n```\n\n### Usage with apache-superset\n1. Go to Connect database and add database\n\n \n\n \n2. Select Shillelagh\n \n \n3. add the connection string with `rest://` prefix\n eg: `rest://api.weatherapi.com?ishttps=1`\n \n \n4. Gice your connection a name: eg `rest-weather-api`\n5. Click test connection and then add\n6. Go to SQL lab and select `rest-weather-api` from database.\n\n \n7. You can leave schema empty and query directly!!\n\n \n\nQuery is:\n```SQL\nSELECT date, \n json_extract(day, \"$.maxtemp_c\") as max_temp_celsius,\n json_extract(hour, \"$[6].temp_c\") as six_am_celsius,\n json_extract(hour, \"$[6].will_it_rain\") as will_it_rain\nFROM \"/v1/forecast.json?key={your_key}&q=Bangalore&days=5#$.forecast.forecastday[*]\";\n```\nTables and schema is empty, because there's no concept for tables in REST APIs. \nIt returns a default message. That message is configured in `rest_api_dialect.py`\n\n\n### Getting the virtual table \nIn superset's SQL lab, we're directly using \n```python\n/v1/forecast.json?key={your_key}&q=Bangalore&days=5#$.forecast.forecastday[*]\n```\nTo get the similar virtual table address for your endpoint (it may have headers or even body), use the utility `rest_db_api.utils.get_virtual_table` and pass in your configs. \n\n - [x] POST requests (request body) \n - [x] headers \n - [ ] adding write support to adapter (for PUT/DELETE requests)\n",
"bugtrack_url": null,
"license": "MIT License Copyright (c) 2023 Satvik Nema Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the \"Software\"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. ",
"summary": "A rest db api designed to be integrated with apache superset",
"version": "0.0.3",
"project_urls": {
"Bug Tracker": "https://github.com/SatvikNema/rest-db-api/issues",
"Homepage": "https://github.com/SatvikNema/rest-db-api"
},
"split_keywords": [
"rest",
"dbapi",
"sqlalchemy",
"adapter",
"superset"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "707dff3da16cefbf5c09732ca84f65596493a31bff3566fdbeb27a8c52d75537",
"md5": "5795d1e6e8b149dc47c7319f77a8d4ef",
"sha256": "73e84807b5a9d3fc2bbd386af3b83939c75efab36004df75105939f2f1bc9d6d"
},
"downloads": -1,
"filename": "rest_db_api-0.0.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "5795d1e6e8b149dc47c7319f77a8d4ef",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 9313,
"upload_time": "2023-08-20T07:15:30",
"upload_time_iso_8601": "2023-08-20T07:15:30.365562Z",
"url": "https://files.pythonhosted.org/packages/70/7d/ff3da16cefbf5c09732ca84f65596493a31bff3566fdbeb27a8c52d75537/rest_db_api-0.0.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "98458ebb54c7a0491766702bc1e06a43b827ddc87cbea0dd737dddf313481d9e",
"md5": "16f3d78e6d0ce28d4dd3cdb6ae791d74",
"sha256": "e29c435491ad186362c81fe6021f57fbc1399d56ab04ae6fd4b45ea734523242"
},
"downloads": -1,
"filename": "rest-db-api-0.0.3.tar.gz",
"has_sig": false,
"md5_digest": "16f3d78e6d0ce28d4dd3cdb6ae791d74",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 11952,
"upload_time": "2023-08-20T07:15:32",
"upload_time_iso_8601": "2023-08-20T07:15:32.692493Z",
"url": "https://files.pythonhosted.org/packages/98/45/8ebb54c7a0491766702bc1e06a43b827ddc87cbea0dd737dddf313481d9e/rest-db-api-0.0.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-20 07:15:32",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "SatvikNema",
"github_project": "rest-db-api",
"travis_ci": false,
"coveralls": true,
"github_actions": false,
"requirements": [],
"lcname": "rest-db-api"
}
