Add plots to datasette pages using billboard.js
Continuing from a previous post, now it's time to draw the table filled with values.
As a showcase this post is available as a github project: https://github.com/mfa/showcase-datasette-billboardjs.
For plotting I used billboard.js the first time.
First version: use the API directly with the table
Every table in datasette has a direct link to get the table as csv with specified fields.
I used this before, but this time I cannot filter the columns.
So the rowid which is a primary-key is plotted.
I found no solution within billboard.js to remove rowid from the plot.
Second version: use a SQL query
This worked as expected, but only after I found out that I need to use LIMIT instead of a size parameter.
There is even a ticket about it: datasette#1200 - but I agree that LIMIT is good enough - when you know size doesn't work.
On the positive side some calculations are possible here, i.e. multiply a value by 1000 because no fractions are wanted in the y-axis.
An example query looks like this: /demo.csv?sql=SELECT+created%2C+delta_sec*1000+AS+delta+FROM+reports+ORDER+BY+created+DESC+LIMIT+100
This version is clearly the preferred one because no additional Python code is needed.
Third version: use jinja template and a prepared query
The "I have a hammer"-solution.
Here a query is prepared in the plugins/index_vars.py and the data is generated using a jinja-for-loop in the template.
The multiplication needed could be done in the SQL query or in the template (as shown in the showcase project).
This solution works, but needs additional Python code in the plugins folder which could be avoided (see second version).