Using Datasette to generate a json/csv API.

Datasette can also be used as an API endpoint. That means that you can fetch data via HTTP calls. Pandas can be run for this act too!

Datasette & Pandas

That means that you can host it and fetch appropriate subsets from pandas.

import pandas as pd
pd.read_csv("http://127.0.0.1:8001/bigmac/bigmac.csv?_size=max")

The previous command will retreive the entire csv file. The command below will run the SQL command before loading the data into pandas.

pd.read_csv("http://127.0.0.1:8001/bigmac.csv?sql=select+rowid%2C+date%2C+currency_code%2C+name%2C+local_price%2C+dollar_ex%2C+dollar_price+from+bigmac+where+%22currency_code%22+%3D+%3Ap0+order+by+date+desc+limit+101&p0=EUR&_size=max")

Fetching Json

Note that you can do the same thing with json. Since json can be served in many shapes it can help to think ahead of time about the shape that you're interested in. You can pass extra arguments to the url though to set the shape.

http://127.0.0.1:8001/bigmac/bigmac.json
http://127.0.0.1:8001/bigmac/bigmac.json?_shape=array
http://127.0.0.1:8001/bigmac/bigmac.json?_shape=array&_nl=on

If you're interested in exploring the results inside of a jupyter notebook we might recommend using clumper. When you're using clumper you should be mindful that there is a distinction between Clumper.read_json and Clumper.read_jsonl. The following lines are appropriate though:

(Clumper
.read_json("http://127.0.0.1:8001/bigmac/bigmac.json?_shape=array")
.show(10))

(Clumper
.read_jsonl("http://127.0.0.1:8001/bigmac/bigmac.json?_shape=array&_nl=on")
.show(10))

Note that you can also attach the SQL statement in the URL as well. Here's an example from the video.

(Clumper
.read_jsonl("http://127.0.0.1:8001/bigmac.json?_shape=array&sql=select+rowid%2C+date%2C+currency_code%2C+name%2C+local_price%2C+dollar_ex%2C+dollar_price+from+bigmac+where+%22currency_code%22+%3D+%3Ap0+order+by+date+desc+limit+101&p0=EUR")
.show(10))