Recent Releases: Plotly Falcon SQL Client and the Remarkable Datasette SQLite2API Generator

A few years ago, launched an online chart hosting service that allowed users to create  – and host – charts based on their own datasets. This was followed with the release of a open source charting libraries and a Python dashboard framework (dash). Now, they’ve joined the desktop query engine’n’charting party with the Falcon SQL Client, a free electron desktop app for Mac and Windows (code).

The app (once started – my Mac complained it was unsigned when I tried to run it the first time) appears to allow you to connect to a range of databases, query engines (such as Apache Drill) and SQLite.

Unfortunately, I couldn’t find a file suffix that would work when looking for a SQLite file to try it out quickly – and whilst trying to find any docs at all for connecting to SQLite (there are none that I can find at the moment), I got the impression that SQLite is not really a first class endpoint for Plotly:

I did manage to get it running against my ergast MySQL container though:

Providing the client with a database name, it loads in the database tables and allows you to query against them. Expanding a table reveals the column names and data type:

After running a query, there’s an option to generate various charts against the result, albeit in a limited way. (I couldn’t label or size elements in the scatter plot, for example.)

The chart types on offer are… a bit meh…:

The result can be viewed as a table, but there are no sort options on the column headers – you have to do that yourself in the query, I guess?

The export options are as you might expect. CSV is there, but not a CSV data package that is bundled with metadata, for example:

All in all, if this is an entry level competitor to Tableau, it’s a very entry level… I’d probably be more tempted to use the browser based Franchise query engine, not least because that also lets you query over CSV files. (That said, from a quick check of the repo, it doesn’t look like folk are working on it much:-(.

Far more compelling in quick-query land is a beautiful component from Simon Willison (who since he’s started blogging again has, as ever before, just blown me away with the stuff he turns up and tinkers with): datasette.

This python package lets you post a SQLite file as a queryable API. (Along the way Simon also produced a handy command line routine for loading CSV files into a SQLite3 database: simonw/csvs-to-sqlite.) Out of the box, datasette lets you fire up the API as a local service, or as a remotely hosted one on Zeit Now (which I’ve yet to play with).

In part, this also reminded me of creating simple JSON APIs from a Jupyter notebook, and the appmode Jupyter extension that allows you to run a widgetised notebook as an app. In short, it got me wondering about how services/apps created that way could be packaged and distributed more easily, perhaps using something like Binderhub?

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

%d bloggers like this: