CLI and Streamlit applications to create APIs from Excel data files within seconds, using FastAPI

Overview

FastAPI-Wrapper CLI & APIness Streamlit App

Arvindra Sehmi, Oxford Economics Ltd. | Website | LinkedIn (Updated: 21 April, 2021)

fastapi-wrapper is motivated by the work of jrieke on fastapi-csv.

Create APIs from data files within seconds, using FastAPI

This is a Python package to create APIs from data files (Excel XLSX and CSV only), using a lightweight & fully customizable wrapper around FastAPI. Endpoints and query parameters are auto-generated based on the column names and data types in the data file. Its contents is written to either a temporary in-memory or persistent SQLite database, so the API can be blazing fast, even for huge files, especially for the in-memory case.

TOC

  • Why did I implement this?
  • Mods
  • Streamlit App Demo
  • CLI Demo
  • How to use fastapi-wrapper from the command line (CSV Example)
  • How to use fastapi-wrapper from Python (XLSX Example)
  • Example using the API in Power BI
  • My thoughts on additional requirements
  • The APINESS Factor
  • Resources
  • Epilogue

Why did I implement this?

  • To enhance and extend some internal Oxford Economics (OE) tools, e.g., Modelit project [OE clients only, sorry!] so that MDL-generated data exports can enjoy some apiness :-)
  • An easy way to expose Excel-Files-as-Databases with a REST API so they can be queried in Streamlit, and other apps such as Power BI and Jupyter Notebooks
  • It can be useful for mocking data sources in CI/CD testing pipelines
  • To experiment with FastAPI, which has garnered a lot of attention and enterprise adoption (e.g. Microsoft)

Value of SQL Model DBs to OE?

  • Command line tooling lends itself to pipeline batch automation working with model bases
  • Use a standard, performant and scaleable data format and query language
  • Portable across multiple device form factors and operating systems
  • Easily shareable locally, in the cloud and edge data networks
  • Much better for data analytics and vizualization, e.g. direct connectors in Power BI & Tableau
  • Ability to store multiple model bases in a single SQL database, e.g. multiple scenarios or model vintages
  • Easy to export data to multiple target formats from a SQL DB using commonly available tools and programming languages
  • Can be used to augment model data exports from OE's Online Global Economic Model (SkyMod) API

Mods

The changes I made to jrieke's original implementation are:

  • Built a Streamlit application to:
    • Interactively upload one or more Excel data files
    • Configure each file's API endpoint
    • Generate a SQLite database, for all or each file
    • Enable downloading of generated SQLite databases, and
    • Launch FastAPI to serve the APIs
  • Simplified naming of auto-generated query params added to the API
  • Added cols, where and cmd query params for richer SQL queries of the endpoint (including defense against destructive SQL injections)
  • Error handling
  • Rendering response data as JSON and HTML
  • Restructured return json results to add some metadata useful for debugging, for example:
{   "metadata": {
        "database": "macro",
        "table": "custommacromodel_l_a",
        "sql_query": "SELECT * FROM custommacromodel_l_a WHERE (Location IN (\"United Kingdom\")) LIMIT 2",
        "full_count": 1278,
        "results_count": 1278
    },
    "data": [
        {
        "id": 457027,
        "Location": "United Kingdom",
        "Indicator": "GDP, agriculture, real, LCU",
            .
            .
            .
        "Year": 1980,
        "Value": 8210.16
        },
            .
            .
            .
    ]
}

Streamlit App Demo

In this demo:

  1. The user successively uploads one or more XLSX/CSV files
  2. The app displays a row of edit fields for each uploaded file allowing the user to configure the database name, table name, and update mode
  3. The configuration and files are submitted for processing, that is, databases are generated and populated with their file data and (Fast)API endpoints are created
  4. After the user has uploaded and processed all their files, the FastAPI server is started making the API live
  5. The user interacts with the live API via a browser

st_demo

IMPORTANT: The API is launched via uvicorn on its own thread and can't be killed. The only way currently to stop uvicorn and Streamlit is to kill the Python host process.


CLI Demo

(This demo gives you a good idea of the command line system. Note, the latest version differs slightly in terms of the API base URL format used.)

demo


How to use fastapi-wrapper from the command line (CSV Example)

Installing the CLI

# Use pip in the root folder
pip install .

# Or, running setup from the root folder
python setup.py install
# To uninstall
pip uninstall fastapi-wrapper

Running the CLI

Use the CSV data file (472718 rows) in this repo for testing (CustomMacroModel_L_A.csv). To start an API, run one of the following commands:

# From file
fastapi-wrapper CustomMacroModel_L_A.csv

# Directly from URL
fastapi-wrapper https://raw.githubusercontent.com/asehmi/fastapi-wrapper/main/CustomMacroModel_L_A.csv

# If you provide an xlsx file, then add XLSX as the second argument on the command line (see Python example below)

Either command should start a fastapi instance in uvicorn (a lightning-fast ASGI web server) on the default host and port.

(base) C:\Dev\apiness>fastapi-wrapper custommacromodel_l_a.csv --host localhost --port 8000 --database macro
fastapi_wrapper v0.4.2
Creating > Database: macro | From file: custommacromodel_l_a.csv | Type: CSV | Update mode: replace
Initializing FastAPI_Wrapper...
Starting API server (uvicorn)...
Check out the API docs at http://localhost:8000/docs | http://localhost:8000/redoc
--------------------------------------------------------------------------------
INFO:     Started server process [18828]
INFO:uvicorn.error:Started server process [18828]
INFO:     Waiting for application startup.
INFO:uvicorn.error:Waiting for application startup.
INFO:     Application startup complete.
INFO:uvicorn.error:Application startup complete.
INFO:     Uvicorn running on http://localhost:8000 (Press CTRL+C to quit)
INFO:uvicorn.error:Uvicorn running on http://localhost:8000 (Press CTRL+C to quit)

installation

Command line switches

fastapi-wrapper --help command line switch prints some useful info:

(base) C:\Dev\apiness>fastapi-wrapper --help
fastapi_wrapper v0.4.2
Usage: fastapi-wrapper [OPTIONS] DATA_PATH [DATA_FORMAT]:[CSV|XLSX]

  Create APIs from CSV or XLSX data files within seconds, using fastapi.

  Just pass along a data file and this command will start a fastapi instance
  with auto-generated endpoints & query parameters to access the data.

Arguments:
  DATA_PATH                 Path to the data file  [required]
  [DATA_FORMAT]:[CSV|XLSX]  Format of data file  [default: CSV]

Options:
  --database TEXT                 Sqlite DB name. Defaults to in-memory DB.
                                  [default: :memory:]

  --if-exists [replace|append|fail]
                                  Defines treatment of database if it exists
                                  [default: replace]

  --start-server / --no-start-server
                                  Start server.  [default: True]
  --host TEXT                     IP to run the API on  [default: 127.0.0.1]
  --port INTEGER                  Port to run the API on  [default: 8000]
  --help                          Show this message and exit.

For example:

fastapi-wrapper custommacromodel_l_a.csv --host localhost --port 8000 --database macro
  • fastapi-wrapper auto-generates endpoints and query parameters based on the CSV file
  • Here, the API will have an endpoint /macro/custommacromodel_l_a
  • The URL fragments are /<database name | :memory:>/<database table name>
  • The table name is the same as the file name, but lowercased
  • The endpoint can be queried using the imported file's column names
  • And finally, a SQLite database named macro.db, will also be created

IMPORTANT: The endpoint fragments and column names used as query parameters are lowercased. Spaces in their names will be replaced with underscore ('_').

API documentation

The auto-generated API documentation is available here:

Querying the API

In a browser window enter the following parameterised URLs:

(All URL fragments below are prefixed with http://localhost:8000)

  • /macro/custommacromodel_l_a?location=United Kingdom
  • /macro/custommacromodel_l_a?location=United Kingdom&indicatorcode=GVA

Additionally, fastapi-wrapper creates some convenience query parameters for specific data types, e.g.

  • /macro/custommacromodel_l_a?year_gt=2020 (for int/float)
  • /macro/custommacromodel_l_a?location_in=Kingdom (for string, watch out: this one is case sensitive!)
  • /macro/custommacromodel_l_a?year_lte=2020&Location_in=United Kingdom&indicator_in=GDPAGR

Numerical values can be quoted or not. Strings should not be quoted in query values.

You can explicitly specify SQL "where" read-only clauses using the where parameter. Destructive SQL commands and clauses cause an exception.

  • /macro/custommacromodel_l_a?where=Year>="2029" AND Year<="2031" AND Indicator LIKE "%GDP%" AND LocationCode IN ("JAPAN","HK")

SQL command modifiers can be added to using the cmd parameter.

  • /macro/custommacromodel_l_a?where=Year>=2030 AND Year<=2031 AND Location="United Kingdom" AND Indicator LIKE "%GDP%"&cmd=LIMIT 5

Note, strings in where and cmd values are quoted and must obey SQL syntax rules.

The columns returned by a query can be specified (including aliases) with the cols parameter.

  • /macro/custommacromodel_l_a?where=Year>="2029" AND Year<="2031" AND LocationCode IN ("JAPAN","HK")&cols=Location, Indicator, Year, Value&cmd=LIMIT 10
  • /macro/custommacromodel_l_a?where=LocationCode IN ("JAPAN","HK")&cols=LocationCode as LOCCODE, Indicator as VAR, Year as YR, Value as VAL&cmd=LIMIT 10

IMPORTANT: Where column names are referenced as query parameters (not parameter values), they must be lowercased. When they appear as values for where and cols parameters, case does not matter.

json_data

Downloading generated SQLite database

  • /download/macro.db or /download/macro

Rendering results as HTML

By default results are rendered as JSON where tabluar data is available on the data key. To render the data as an HTML table, simply add a tohtml parameter to the query.

  • /macro/custommacromodel_l_a?where=Year>="2029" AND Year<="2031" AND Indicator LIKE "%GDP%" AND LocationCode IN ("JAPAN","HK")&tohtml

html_table


How to use fastapi-wrapper from Python (XLSX Example)

Ensure the required packages are installed:

pip install -r requirements.txt

Create a file app.py:

from fastapi_wrapper import FastAPI_Wrapper

app = FastAPI_Wrapper().create_database(database='gcfs', data_path='GCFS Countries.xlsx', data_format='XLSX', if_exists='replace')

Start from terminal just like a normal fastapi app:

uvicorn app:app

OR, create a file main.py:

from fastapi_wrapper import FastAPI_Wrapper
import uvicorn

app = FastAPI_Wrapper().create_database(database='gcfs', data_path='GCFS Countries.xlsx', data_format='XLSX', if_exists='replace')
uvicorn.run(app, host='localhost', port='8000')

Start from terminal just like a normal python app:

python main.py

The queries are similar to the CSV case above, except the endpoint is different:

  • /gcfs/gcfs_countries?cmd=LIMIT 1000
  • /gcfs/gcfs_countries?where=Location LIKE "%Kingdom%" OR Location LIKE "%States%"

This shows use of cols query parameter:

  • /gcfs/gcfs_countries?cols=location, indicator, year, value&location_in=United Kingdom

This shows use of _ina and _inz query parameters to match at the beginning and end of values respectively, and column name aliases:

  • /gcfs/gcfs_countries?location_in=United Kingdom&indicator_code_ina=GDP&indicator_code_inz=USC&cols=Location as LOC,Indicator as VAR,Indicator_Code as VARCODE,Value as VAL,Year as YR

Extending the API

The cool thing: FastAPI_Wrapper only extends FastAPI. Therefore, you can do all the stuff you can do with a normal fastapi instance, e.g. add a new endpoint:

# Add a new endpoint, just like in normal fastapi
@app.get("/hello")
def hello(self):
    return {"Hello:", "World"}

This way you can easily modify endpoints previously generated from the CSV file.

Updating data

If your CSV file changes, you can update the API data with:

app.update_database()

Note, this will only update the data, not the API endpoints or query parameters. To do that, you need to create a new FastAPI_Wrapper instance or re-start uvicorn.


Example using the API in Power BI

See the file TestReport.pbix (requires the free PBI Desktop Application on Windows)

M-Language data transformation script

m_lang

Power BI report

pbi_rept


My thoughts on additional requirements

FastAPI_Wrapper is a neat idea, but "What is the real value prop?"

  • Needs a decent SQL DB, Redis, or a cloud DB. SQLite is not slow, apparently, SQLite is faster than nearly every other database at read-heavy use cases, and it's super versatile, but I think we need a streaming query API to make it easier to consume in BI tools.

  • This program relies on the built-in SQLite Python binding, and may come with limits we don't want. Need to investigate:

  • Would it be better supporting GraphQL?

  • Extend to handle TXT files, different table layouts, etc.

  • Have the ability to import from Excel named ranges / data tables.

  • [DONE] Have the ability to download databases.

  • Break tight coupling between the api endpoint and the Excel filename

    • [DONE] User should interact with an "upload & generate api" workflow
    • Provide a namespace to isolate APIs from one another (could be the organisation name, user name, etc.) - details can come from a login profile - useful for online deployments
    • [DONE implicitly] Specify the endpoint name
    • [DONE except for namespace] Upload file and provision API using file contents type information, namespace, and endpoint name
    • Access API via: /namespace/endpoint_name?....
  • During provisioning workflow, allow an API access key to be generated for the endpoint (or for all endpoints in the login account)

    • To access API protected endpoints, the user should send an apikey header variable
    • (Study FastAPI docs re: protecting endpoints)
  • Allow users to clone an API endpoint

  • Provide integrations (which will require system upload and management APIs)

    • Dropbox / OneDrive / GDrive listeners
    • Zapier, IFTTT, etc.
  • Customized client-side error handling hooks (400s, 500s, etc.)

  • The autogenerated API docs should be specific to each namespace + endpoint combination, rather than the host domain

    • http://localhost:8000/namespace/endpoint/docs, not
    • http://localhost:8000/docs

The APINESS Factor

apiness

testimonial


Resources

There are tons of resources on the web. These are some I have looked at:

Datasette

This is an excellent, very complete solution of Excel files >> SQLite with vizualisations.

(Found after I started my project; so is there any need to develop this solution further??)


Epilogue

Looks like some folks are making a living from this sort of thing!

You might also like...
Пример использования GraphQL Ariadne с FastAPI и сравнение его с GraphQL Graphene FastAPI

FastAPI Ariadne Example Пример использования GraphQL Ariadne с FastAPI и сравнение его с GraphQL Graphene FastAPI - GitHub ###Запуск на локальном окру

Flask-vs-FastAPI - Understanding Flask vs FastAPI Web Framework. A comparison of two different RestAPI frameworks.
Flask-vs-FastAPI - Understanding Flask vs FastAPI Web Framework. A comparison of two different RestAPI frameworks.

Flask-vs-FastAPI Understanding Flask vs FastAPI Web Framework. A comparison of two different RestAPI frameworks. IntroductionIn Flask is a popular mic

FastAPI Server Session is a dependency-based extension for FastAPI that adds support for server-sided session management

FastAPI Server-sided Session FastAPI Server Session is a dependency-based extension for FastAPI that adds support for server-sided session management.

Fastapi-ml-template - Fastapi ml template with python

FastAPI ML Template Run Web API Local $ sh run.sh # poetry run uvicorn app.mai

Sample project showing reliable data ingestion application using FastAPI and dramatiq
Sample project showing reliable data ingestion application using FastAPI and dramatiq

Create and deploy a reliable data ingestion service with FastAPI, SQLModel and Dramatiq This is the source code for the data ingestion service explain

Flask + marshmallow for beautiful APIs

Flask-Marshmallow Flask + marshmallow for beautiful APIs Flask-Marshmallow is a thin integration layer for Flask (a Python web framework) and marshmal

🚀   Cookiecutter Template for FastAPI + React Projects.  Using PostgreSQL, SQLAlchemy, and Docker
🚀 Cookiecutter Template for FastAPI + React Projects. Using PostgreSQL, SQLAlchemy, and Docker

FastAPI + React · A cookiecutter template for bootstrapping a FastAPI and React project using a modern stack. Features FastAPI (Python 3.8) JWT authen

Full stack, modern web application generator. Using FastAPI, PostgreSQL as database, Docker, automatic HTTPS and more.
Full stack, modern web application generator. Using FastAPI, PostgreSQL as database, Docker, automatic HTTPS and more.

Full Stack FastAPI and PostgreSQL - Base Project Generator Generate a backend and frontend stack using Python, including interactive API documentation

Owner
Arvindra
Arvindra
A minimal Streamlit app showing how to launch and stop a FastAPI process on demand

Simple Streamlit + FastAPI Integration A minimal Streamlit app showing how to launch and stop a FastAPI process on demand. The FastAPI /run route simu

Arvindra 18 Jan 2, 2023
The template for building scalable web APIs based on FastAPI, Tortoise ORM and other.

FastAPI and Tortoise ORM. Powerful but simple template for web APIs w/ FastAPI (as web framework) and Tortoise-ORM (for working via database without h

prostomarkeloff 95 Jan 8, 2023
Easy and secure implementation of Azure AD for your FastAPI APIs 🔒

FastAPI-Azure-auth Azure AD Authentication for FastAPI apps made easy. ?? Description FastAPI is a modern, fast (high-performance), web framework for

Intility 216 Dec 27, 2022
Simple FastAPI Example : Blog API using FastAPI : Beginner Friendly

fastapi_blog FastAPI : Simple Blog API with CRUD operation Steps to run the project: git clone https://github.com/mrAvi07/fastapi_blog.git cd fastapi-

Avinash Alanjkar 1 Oct 8, 2022
Sample-fastapi - A sample app using Fastapi that you can deploy on App Platform

Getting Started We provide a sample app using Fastapi that you can deploy on App

Erhan BÜTE 2 Jan 17, 2022
FastAPI-Amis-Admin is a high-performance, efficient and easily extensible FastAPI admin framework. Inspired by django-admin, and has as many powerful functions as django-admin.

简体中文 | English 项目介绍 FastAPI-Amis-Admin fastapi-amis-admin是一个拥有高性能,高效率,易拓展的fastapi管理后台框架. 启发自Django-Admin,并且拥有不逊色于Django-Admin的强大功能. 源码 · 在线演示 · 文档 · 文

AmisAdmin 318 Dec 31, 2022
Docker image with Uvicorn managed by Gunicorn for high-performance FastAPI web applications in Python 3.6 and above with performance auto-tuning. Optionally with Alpine Linux.

Supported tags and respective Dockerfile links python3.8, latest (Dockerfile) python3.7, (Dockerfile) python3.6 (Dockerfile) python3.8-slim (Dockerfil

Sebastián Ramírez 2.1k Dec 31, 2022
fastapi-admin2 is an upgraded fastapi-admin, that supports ORM dialects, true Dependency Injection and extendability

FastAPI2 Admin Introduction fastapi-admin2 is an upgraded fastapi-admin, that supports ORM dialects, true Dependency Injection and extendability. Now

Glib 14 Dec 5, 2022
api versioning for fastapi web applications

fastapi-versioning api versioning for fastapi web applications Installation pip install fastapi-versioning Examples from fastapi import FastAPI from f

Dean Way 472 Jan 2, 2023
api versioning for fastapi web applications

fastapi-versioning api versioning for fastapi web applications Installation pip install fastapi-versioning Examples from fastapi import FastAPI from f

Dean Way 127 Feb 17, 2021