Display query results as plots

[eeriksp]

The proposal

It would be really useful to see the query results as a plot/graph, especially if the dataset is a little larger and it is hard to make sense out of a long table.

I would be interested in submitting a pull request for implementing that feature.

What would it look like

Right now the query result display has two tabs: "Preview" and "Pivot". I would propose adding new tabs with various plot types (e.g. pie chart, line chart). These plots would only work if the data table has a compatible format. If the table does not have the required format the tab displays a message explaining the expected format.

I illustrate my idea with examples using pie charts and line charts, however, a larger variety of plot types could be considered in the future.

Pie plot

The pie chart would require exactly two columns in the result table, the first containing labels and the other one containing numeric values.

For example

fruit	count
apple	9
orange	5
banana	3
grape	7

would be rendered as

Line chart

The line chart would require the first column to contain labels for the x-axis (e.g. dates or years) and one or more numeric columns each representing a line.

For example

year	apple	orange	grape
2018	9	12	10
2019	10	13	10
2020	12	14	11
2021	15	15	11
2021	18	17	12

would be rendered as

Other chart types can have different table formats. In the future, there might be some more advanced options to choose dynamically what columns to use for plotting, but that seems a little more difficult to implement.

Implementation ideas

I propose to use matplotlib to draw the plots.
And example of using matplotlib with Django can be found here.

Problems to consider

The only major issues I see with this new feature would be:

1. new dependencies
2. performance considerations as drawing the graphs can take a little time (this only becomes an issue if the dataset is really massive)

Both concerns can be addressed by introducing a setting (e.g. EXPLORER_PLOTTING) for turning this feature off or on according to the project's needs.

Code

Just for the record, here is the code used to generate the pie chart and the line chart:

import matplotlib.pyplot as plt
import seaborn as sns

# Pie chart
plt.pie(data=[9, 5, 3, 7], labels=["apple", "orange", "banana", "grape"])
plt.show()

# Line chart
sns.lineplot(data=data, x=[2018, 2019, 2020, 2021, 2022], y=[9, 10, 12, 15, 18], label="apple")
sns.lineplot(data=data, x=[2018, 2019, 2020, 2021, 2022], y= [12, 13, 14, 15, 17], label="orange")
plt.show()

What are your thoughts on that?

[marksweb]

@eeriksp This sounds like a great feature.

You've got a fair point about dependencies - something like this would be best as an optional dependency, like how you can do django-anymail[amazon_ses] to specify the optional extra packages. Then either load the functionality via import with a try/except on ImportError or control things through settings.

[marksweb]

@eeriksp Could you please install this beta build and verify everything is working as expected;

https://pypi.org/project/django-sql-explorer/2.5b0/

[lawson89]

@marksweb we can close this one right?

[marksweb]

@lawson89 Yeah, we've got charts now so I suppose we can.