Easily share data across your company via SQL queries. From Grove Collab.

Hey, great work on this tool!

I'm thinking it might be good to release a new version on pip. There's some good stuff in the latest version, esp a fix for showing the schema on mysql.

I added this to my pip requirements list:

git+git://github.com/groveco/django-sql-explorer.git@42237747b82aba40340bdbe1534fe2c085f96b01 # latest commit as of jan 12 2018

and had to pip uninstall the current version first.

This could be confusing for many users and I'm sure they would appreciate a release on pip.

Right now when I install this library in my Django 3.0 project, I get the following error: ImportError: cannot import name 'render_to_response' from 'django.shortcuts' (...../lib/python3.7/site-packages/django/shortcuts.py)

render_to_response was deprecated in Django 2.0 and removed from Django 3.0. Can someone please deploy a fix for this?

Add an extra exporter (PDF) using xhtml2pdf and reportlab. Uses the exporer/pdf_template.html as a template for the pdf table, you can override it in your own apps to add (for example) page numbers, extra fonts, change page layout etc. Should fix #207.

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.

Hey! After installing django-sql-explorer==3.0, I get this error:

[2022-12-15T14:06:49.875Z] #16 2.041   File "/app/venv/lib/python3.10/site-packages/explorer/utils.py", line 9, in <module>
[2022-12-15T14:06:49.875Z] #16 2.041     import boto3
[2022-12-15T14:06:49.875Z] #16 2.041 ModuleNotFoundError: No module named 'boto3'

I see boto3 is actually imported directly here https://github.com/groveco/django-sql-explorer/blob/master/explorer/utils.py#L9, but when installing this app boto3 is not installed as a dependency.

Am I missing something?

Thanks!

Setting EXPLORER_TASKS_ENABLED = True causes the following exception:

ImportError: cannot import name 'task' from 'celery' (D:\Users\my-user\projects\my-project\venv-windows\lib\site-packages\celery\__init__.py)

Django==3.2.9
django-sql-explorer==2.4.2
celery==5.2.1

Hi Team,

Can we please upgrade the JS files?

These packages have some Vulnerabilities: https://snyk.io/vuln/npm:[email protected] https://snyk.io/vuln/npm:[email protected] https://snyk.io/vuln/npm:[email protected] https://snyk.io/vuln/npm:[email protected]

Thanks, Bhola

Hello! I am running the following query:

SELECT * from eventtype where eventtype.value = 'Grant Date';

I get:

Query failed the SQL blacklist: GRANT

In order for the query to work, I have to do workarounds: SELECT * from legal_eventtype where legal_eventtype.value = concat('G', 'rant Date'); This causes a lot of headaches.

I have a great use case for an offline application to use django-sql-explorer and need the scripts and links all to be stored with the package for this purpose.

These were the references I found with a quick grep of the templates:

[explorer]# grep 'src="/' *
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/jquery-cookie/1.4.1/jquery.cookie.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.3/underscore-min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/list.js/1.5.0/list.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.36.0/codemirror.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.36.0/mode/sql/sql.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.36.0/addon/comment/comment.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/floatthead/2.1.1/jquery.floatThead.min.js"></script>
base.html:    <script src="//cdnjs.cloudflare.com/ajax/libs/pivottable/2.20.0/pivot.min.js"></script>

[explorer]# grep 'href="/' *
base.html:    <link href="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.36.0/codemirror.min.css" rel="stylesheet" />
base.html:    <link href="//cdnjs.cloudflare.com/ajax/libs/pivottable/2.20.0/pivot.min.css" rel="stylesheet" />
fullscreen.html:    <link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />

Currently exporting a CSV and opening it in Excel ruins any unicode characters.

For example, Japanese characters display as 裕太

Exporter needs to do something like this to write the BOM to the beginning of the file;

    contents = StringIO()
    contents.write(codecs.BOM_UTF8.decode('utf-8'))
    writer = csv.writer(contents)

Hi there,

I just discovered this project today, and it looks fantastic - thank you for making it!

Would you consider supporting more providers than just AWS if they are already boto/boto3 compatible ?

I see this code in the repo here which I think is responsible for uploading snapshots to an object storage service like AWS S3:

def get_s3_bucket():
    from boto.s3.connection import S3Connection

    conn = S3Connection(app_settings.S3_ACCESS_KEY,
                        app_settings.S3_SECRET_KEY)
    return conn.get_bucket(app_settings.S3_BUCKET)


def s3_upload(key, data):
    from boto.s3.key import Key
    bucket = get_s3_bucket()
    k = Key(bucket)
    k.key = key
    k.set_contents_from_file(data, rewind=True)
    k.set_acl('public-read')
    k.set_metadata('Content-Type', 'text/csv')
    return k.generate_url(expires_in=0, query_auth=False)

In a project I work on we use the boto library to talk to s3 compatible services from other providers, and I think to add this support to django-sql-explorer, it would mainly require a adding two new parameters to the code that connects to S3.

We have a comparable method on a project I work on where we fetch an S3-compatible bucket, which does a somewhat similar job to get_s3_bucket above.

def object_storage_bucket(bucket_name: str):
    """
    Return the bucket identified by `bucket_name` for uploading
    and downloading files.
    """
    session = boto3.Session(region_name=settings.OBJECT_STORAGE_REGION)

    object_storage = session.resource(
        "s3",
        endpoint_url=settings.OBJECT_STORAGE_ENDPOINT,
        aws_access_key_id=settings.OBJECT_STORAGE_ACCESS_KEY_ID,
        aws_secret_access_key=settings.OBJECT_STORAGE_SECRET_ACCESS_KEY,
    )
    return object_storage.Bucket(bucket_name)

While the example I have shared uses boto3, instead of boto, I think it would not be too complicated to add the extra params, the region_name, and endpoint_url to the code connecting to an object storage service.

Doing so would then make this library compatible with a range of S3-compatible providers (i.e. Cloudflare, Digital ocean, Scaleway, etc), as well as open source S3-compatible object storage software like Minio, Garage and so on.

I'm currently adding django-sql-explorer to a project I'm working on but once I'm a bit more familiar with it, would you accept a PR to add this functionality, and assuming it meets contribution standards, giving some pointers on how to have this gracefully integrated into the project?

In the project where we are using Django SQL explorer the number of queries has grown so large that it is hard to navigate.

I would propose to add a field path to the query which would function like a regular filesystem path. Based on that path queries could be displayed in the list view as a tree-like structure. For example queries with names A, B, C, D and paths /research/europe/, /research/europe/, /research/usa, and sales, respectively, could be shown as

• research
  • europe
    • A
    • B
  • usa
    • C
• sales
  • D

This change would be backwards compatible as the path variable would not be required and the path could default to the root directory or some special "uncategorized" category.

Also, this would open up the possibility to use that path for access management in the future such as giving a user read or write access to all queries under a specific path. However, that is just a though and not directly linked to the current proposal.

If that idea sounds interesting, I would be happy to prepare a PR.

Description

This provides the functionality to save query params so that the queries execute with them each time one comes to the Query Detail page.

Resolves: #482

Deployment Procedures

• [ ] Migration (Please specify app(s) below)
  • ./manage.py migrate explorer

Tests

Execute Manual UI Tests like the following:

1. Create a new query with a query param and save, note the same functionality exists as previously where the query does not execute due to no value for the new param:

2. Input a value for the parameter and save:

3. Now add a new parameter and save:

4. Now save a new value to the second parameter:

5. Now navigate back to the Query List page and click on the test query:

6. Validate that the new QueryParams persist and the query executed:

Also, you will want to verify that the csv and JSON exports still work correctly like:

I embedded a query with parameters using the procedure described in #485. The problem with this is that there is no way to add an optional "label" to the parameters so as to have better control on what to disply to the users.

I tried to change the parameter name to be used as a labe but for reasons unknown to me this doesn't work. For example take a peek at this query:

select aa.id, aa.created_on,
aa.dynamic_data->>'date' as "Ημερμηνία",
aa.dynamic_data->>'authority' as "Υπηρεσία",
aa.dynamic_data->>'mail' as "Email",
aa.dynamic_data
from apps_app aa 
left join apptypes_apptype aat on 
aa.app_type_id = aat.id
where aat.id = 8
and aa.dynamic_data->>'mail' like "%$$email$$%"
and aa.dynamic_data->>'authority' like "%$$Υπηρεσία$$%"

Notice that I use a unicode name for the parameter in the last line; this parameter is not displayed at all (notice I'm using the normal process explorer view not any custom ones). Also, if I wanted to write Email as the name of the parameter instead of email I'd still get email (all lowercase) in the form field label.

One way this could be implemented (this is only for UX purposes, I haven't read the code to understand if it's possible) is to add an optional label in the field similar to the optional default value. I.e right now we can do: $$paramName:defaultValue$$, this can be changed to $$paramName:defaultValue:Label$$ (and let's suppose we don't allow : in the label).

Is this the correct way to go? Also is this a feature we'd like to add ? If yes I'd be happy to take a look at it.

Hello friends, in a recent project I wanted to embed a query in a view for all users to be able to run it. So instead of using the provided views I wanted to properly embed the report in my views so as to use my permissions etc (the requirement was a little more complex than that but it doesn't matter).

I didn't find any docs for that so I had to research the source code and use the query_viewmodel function. So, my query detail view is similar to this:

from explorer.models import Query
from explorer.views.utils import query_viewmodel

class QueryDetailView(DetailView):
    template_name = "reports/query_detail.html"
    model = Query

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        query = self.get_object()
        query.params = dict(
            [(k[:-6], v) for k, v in self.request.GET.items() if k.endswith("_param")]
        )

        vm = query_viewmodel(self.request, query)

        # dict_keys(['tasks_enabled', 'params', 'title', 'shared', 'query', 'form', 'message', 'error', 'rows', 'data', 'headers', 'total_rows', 'duration', 'has_stats', 'snapshots', 'ql_id', 'unsafe_rendering', 'fullscreen_params'])
        context.update(vm)
        context["title"] = query.title
        return context

Then I used a query_detail.html template like:

{% extends "site_base.html" %}
{% block page_content %}

{% if params %}
<form method='GET'>
    <div class="form-inline">
        {% for k, v in params.items %}
            <div class="form-group">
                <label for="{{ k }}_param" class="control-label col-sm-4">{{ k }}:</label>
                <div class="col-sm-7">
                    <input type="text" data-param="{{ k }}" class="param form-control" name="{{ k }}_param" id="{{ k }}_param" placeholder="parameter" value="{{ v }}" />
                </div>
                <div class="col-sm-1"></div>
            </div>
        {% endfor %}
    </div>
    <input type='submit' class='btn btn-primary' value='Query'>
    <a href='{{ request.path }}' class='btn btn-secondary'>Reset</a>
</form>
{% endif %}

<div class="row">
    <div class="col-md-12">
        {% if data %}
            <table class='table'>
                <thead>
                    <tr>
                        {% for col in headers %}
                            <th>{{ col }}</th>
                        {% endfor %}
                    </tr>
                </thead>
                <tbody>
                    {% for row in data %}
                        <tr>
                            {% for col in row %}
                                <td>{{ col }}</td>
                            {% endfor %}
                        </tr>
                    {% endfor %}
                </tbody>
            </table>
        {% endif %}
    </div>
</div>
{% endblock %}

I'm not sure if this is the correct way to do it, can you please confirm. If yes, I'd recommend adding this in the documentation so it's easy for other users that want to embed queries. Also we may want to make the query_viewmodel function as part of the API so we should describe its inputs/outputs.

I can provide the PR with the change to the docs if you think it will be useful.

I am curious if there is a way to save a query parameters value for later use instead of having to re-enter it each time one goes to the Query Detail page? I only ask because I have not been able to find an answer to that with Google and I do not see anything in the codebase to do it, but maybe I am missing something. Thank you