Additional export functionality

[grantmcconnaughey]

Hey @chrisclark, what do you think about updating the export functionality to export to CSV, Excel, PDF, and JSON? Maybe a nice little toolbar below the report with export functionality to any of those choices. Then more could be added later if necessary (like an HTML Table export or something).

I would of course help out with this. Just wanted to get your opinion first.

[chrisclark]

Totally up for it -- JSON in particular would be nice. I think we should revisit the URL endpoints if this is to be done -- e.g. currently there are:

/download
/csv
/email_csv

these should probably be changed to:

/download?format=
/stream?format=
/email?format=

with the default format being .csv.

What do you think? I'd also ask that any heavy dependencies (like an Excel integration, PDF, etc) be optional and the code respond according to whether those dependencies are installed. I think one of the appeals of Explorer is the low number of dependencies and I'd like to take the optional route when possible. Happy to help with the code as well if we want to get a branch going with it.

[grantmcconnaughey]

I like it. The change to those URLs would probably constitute a backwards-incompatible change. Do you think perhaps this new feature would be worthy of making django-sql-explorer 1.0.0?

I like the idea of making dependencies optional (Excel and PDF functionality, specifically). CSV and JSON (and XML?) export could use Python stdlib modules.

Another feature to consider could be adding custom exporters via settings, e.g.

EXPLORER_DATA_EXPORTERS = [
    'myapp.exporters.CustomFormatExporter',
    'app2.exporters.CachedJSONExporter',
    'app3.exporters.HTMLTableExporter',
]

Probably getting ahead of myself here. That definitely doesn't need to be in the initial release. Just something to consider. 😄

I think creating a new branch would be wise. Maybe a data_exporters branch or something. I'll start thinking about this some more and have a PR for you soon(ish).

[grantmcconnaughey]

@chrisclark How do you typically develop django-sql-explorer? I'm trying to do the ol' python manage.py runserver, but manage.py is looking for a settings file called test_settings.py. Do you usually create this file and delete it before committing? Or do you have a test project that isn't in git?

[chrisclark]

I run it in a project that isn't in git. I just spun up a sample project in
the app itself and fixed a number of small issues. Grab the latest from
master and do:

bash test_project/start.sh

This will run migrations on sqlite, create a superuser with u/p
admin/admin, create a sample query, and launch runserver. Let me know how
it works for you.

I was also thinking that 'open in google sheets' would be a good export
option. I'm not familiar with their API, but I suspect it's relatively easy.

-Chris

On Mon, Mar 21, 2016 at 7:24 AM, Grant McConnaughey <
notifications@github.com> wrote:

@chrisclark https://github.com/chrisclark How do you typically develop
django-sql-explorer? I'm trying to do the ol' python manage.py runserver,
but manage.py is looking for a settings file called test_settings.py. Do
you usually create this file and delete it before committing? Or do you
have a test project that isn't in git?

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
#186 (comment)

Chris Clark │ Grove Co. │ 617-571-7327

1770 Union St, San Francisco, CA

[grantmcconnaughey]

I see. I created a little test project on my machine and I'm using that to access the explorer. I have JSON, CSV, and Excel export functionality currently. I haven't done anything with streaming (which I honestly wouldn't know how to do) or emailing. Would you like me to push to my fork so you can take a look at my implementation?

Also, open in google sheets could probably work, although that might be different type of exporter than I'm creating. Right now, the "Data Exporters" (as I'm calling them) simply take a query and generate the response that downloads the file: CSV, JSON, or Excel.

[chrisclark]

Yep -- go ahead and give it a push and I will take a look. By 'streaming' i
just mean the browser renders it, instead of triggering a file download:
https://github.com/groveco/django-sql-explorer/blob/master/explorer/utils.py#L122

Super easy. I'll take a look at email as well. Should be able to replicate
the same patterns.

On Tue, Mar 22, 2016 at 12:37 PM, Grant McConnaughey <
notifications@github.com> wrote:

I see. I created a little test project on my machine and I'm using that to
access the explorer. I have JSON, CSV, and Excel export functionality
currently. I haven't done anything with streaming (which I honestly
wouldn't know how to do) or emailing. Would you like me to push to my fork
so you can take a look at my implementation?

Also, open in google sheets could probably work, although that might be
different type of exporter than I'm creating. Right now, the "Data
Exporters" (as I'm calling them) simply take a query and generate the
response that downloads the file: CSV, JSON, or Excel.

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
#186 (comment)

Chris Clark │ Grove Co. │ 617-571-7327

1770 Union St, San Francisco, CA

[grantmcconnaughey]

Okay, @chrisclark, I have a branch on my fork that has the data exporter functionality. It's definitely not complete. I only have the following endpoints:

• /{query_id}/download?format=csv - Which downloads a query to the format
• /download - Which is used to download an SQL query from the playground. sql and format params are POSTed to this endpoint

I don't have stream or email functionality done yet. I am also not finished with the implementation. Check out exporters.py for my implementation so far. Subclasses of BaseExporter need to implement a to_response method that creates a response object, writes the exported data to it, and returns the response.

I'm trying to think of ways to make this a little more generic and less coupled between exporter/response. Maybe responses could be created in a view and exporters can just be responsible for writing their data to that response and returning it? Like so:

def download_view(request, query_id):
    # Get the query, add params, etc.
    exporter = ExporterClass(query)
    response = HttpResponse()
    exporter.write_to_response(response)
    return response

Or perhaps exporters could just write their data to a StringIO or BytesIO object and return that, then the view will write that object to the response? Like this:

def download_view(request, query_id):
    # Get the query, add params, etc.
    exporter = ExporterClass(query)
    output = exporter.output()  # This returns a StringIO or BytesIO in-memory buffer
    return HttpResponse(output, content_type=exporter.content_type)

I'm leaning towards the latter. What do you think?

[grantmcconnaughey]

I went ahead and refactored to the second option. Views now handle creating the response and exporters handle generating the response content. I've also added a /stream?format= endpoint.

[pkaczynski]

Any news on this feature? Need help? I desperately need xlsx export functionality. CSV is giving wrong encoding for excel to parse.

[chrisclark]

@pkaczynski -- check out the data_exporters branch. It'll get merged to master by the end of this week and I'll then do a release. It has XLSX export functionality :)

[pkaczynski]

Thanks for that! Will you issue new release soon?

[grantmcconnaughey]

Hey @chrisclark, did this ever get released? I thought it did but I just checked PyPI and version 0.9.2 is the most recent version. Just checking. Take care!

[chrisclark]

@grantmcconnaughey working on it now! I have to do all the documentation, etc.