FastAPI, SQLAlchemy & Uvicorn

A FastAPI, SQLAlchemy & Uvicorn to fetch a Google Sheet

This PoC is using FastAPI with SQLAlchemy, to:

• Fetch items from a Google Sheets document (range A:C cells), based on the Google spreadsheet ID.
• Store the items into a database (SQLite, Postgres, Mysql, MSSQL,Oracle).
• Serve the items from the DB, via Restful API.

Helpful for a cronjob/task that triggers every 5 minutes the endpoint “/cronjob/{item_id}” It will check if it’s a new Spreadsheet or already exists. In the first case it will create a new RECORD inside the TABLE data, otherwise it will update the existing TABLE items with the new items.

• There are improvements to apply, and they are written in TO-DO list.
• Tested on a Linux machine.

REST API Endpoints Design

REST API access to items resources.

Folders structure

• Deploymeny.yml (Kubernetes)
• Dockerfile (Docker)
• README.md (This File)
• app (Folder with the python APP)
  • clibs (Custom Libs folder)
    • db.py
    • gservices.py
    • models.py
    • repositories.py
    • schemas.py
  • config
    • credentials.json (to download)
    • data.db (autogenerate)
  • main.py (main python APP)
  • requiremets.txt

Prerequisites:

• A basic python and SQLite knowledge
• A basic knowledge of python libraries, classes like Pydantic, Pandas, URLRequests.
• Google Cloud Dev account, to enable the google API and download the credentials.
• A Google spreadsheet, with the above user/credentials allowed in read mode.
• Python 3.11 with Pipenv for local test with ENV.
• Docker to test with containers.
• Kubernetes with HelmChart/FluxCD for a cluster deployment.

SETUP & RUN

There are 3 differents setup to run this project:

• Local Python env
• Docker
• Kubernetes (microk8s)

SETUP A LOCAL PYTHON ENV

1. Clone the reposistory

$ git clone git@github.com:garanet/fastapi_sqlalchemy.git 
$ cd fastapi_sqlalchemy

2. Activate Virtual Environment.

$ pipenv shell --python 3.11

3. Install all the required dependencies using Pipenv.

$ pipenv install google-api-python-client 
$ pipenv install google-auth-httplib2 
$ pipenv install google-auth-oauthlib 
$ pipenv install fastapi 
$ pipenv install uvicorn 
$ pipenv install sqlalchemy 
$ pipenv install pandas

or 
$ pip install -r requiremets.txt

4. Copy the credentials.json file downloaded from google dev-api. Follow this docs to generate the credentials.json file

$ cp ~/Download/credentials.json ./app/config/

5. Run the app

$ python main.py

SETUP A DOCKER ENV

1. Clone the reposistory

$ git clone git@github.com:garanet/fastapi_sqlalchemy.git 
$ cd fastapi_sqlalchemy

2. Copy the credentials.json file downloaded from google dev-api. Follow this docs to generate the credentials.json file

$ cp ~/Download/credentials.json ./app/config/

3. Build the docker Application.

$ docker build -t fastapi:latest .

4. Run the docker Application and expose the port

$ docker run -p 9000:9000 fastapi

SETUP A K8s ENV with microk8s

1. Clone the reposistory

$ git clone git@github.com:garanet/fastapi_sqlalchemy.git 
$ cd fastapi_sqlalchemy

2. Copy the credentials.json file downloaded from google dev-api.

$ cp ~/Download/credentials.json ./app/config/

3. Build the docker Application.

$ docker build -t fastapi:latest .

4. Export the docker image.

$ docker save fastapi:latest > fastapi.tar

5. Import the docker tar file in microk8s

$ microk8s ctr image import fastapi.tar

6. Deploy the deployment.yml file into your helmcharts/orchestra tool

$ flux reconcile ks microservices

Testing

• Swagger UI is used to test it via browser. Visit the swagger at 127.0.0.1:9000
• Use curl from your terminal to:

GET /fetch/{item_id} -> "Get items from Google without store them" 
$ curl -X 'GET' \ 'http://127.0.0.1:9000/fetch/{google_spreadsheet_id}' \latest.tar -H 'accept: application/json'

POST /cronjob/{item_id} -> "Get items and store or update them into the DB" 
$ curl -X 'POST' \ 'http://127.0.0.1:9000/cronjob?item_id=1LAvH4wPOXZSdfVRRHwDGaS5ensyQg2TNm_eoISNcnQg' \ -H 'accept: application/json' \ -d ''

GET /querey/{item_id} -> "Get items from the DB" 
$ curl -X 'GET' \ 'http://127.0.0.1:9000/query?item_id=1LAvH4wPOXZSdfVRRHwDGaS5ensyQg2TNm_eoISNcnQg' \ -H 'accept: application/json'

How it works

• Google
• Database
• Database Models
• Schemas
• Repositories
• Main APP / EntryPoint

Google Cloud Console

Follow these docs to generate the credentials.json file

The full repository and the code explanation, visit my GITHUB

REFERENCES

• FastAPI sql db
• FastAPI errors message
• SQLAlchemy
• Google Quickstart
• Panda Dataframe

TO DO

• Encrypt the Google Credentials file or use a keypass/password manager.
• Enable the https protocol in uvicorn, generating the key and the pem file.
• Improve the async and managing timeout session for big file.