A Python DB API and SQLAlchemy dialect for querying non-SQL resources like APIs

shillelagh

Shillelagh is a Python DB API and SQLAlchemy dialect for querying non-SQL resources like APIs and files. You can use it to write queries like this:

INSERT INTO "csv:///path/to/file.csv"
    SELECT time, chance_of_rain
    FROM "https://api.weatherapi.com/v1/history.json?key=XXX&q=London"
    WHERE strftime('%Y-%m-%d', time) IN (
      SELECT day
      FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=2064361835"
    )

The query above reads holidays from a Google Sheet, uses the days to get weather data from WeatherAPI, and writes the change of rain at each hour of the holidays into a (pre-existing) CSV file.

Each of these resources is implemented via an adapter, and writing adapters is relatively straightforward.

Writing a new adapter

Let’s say we want to fetch data from WeatherAPI using SQL. Their API is pretty straightforward — to fetch data for a given day in a

 

 

 

To finish reading, please visit source site