A pluggable app that allows users (admins) to execute SQL, view, and export the results. Inspired by Stack Exchange Data Explorer.
Project description
Upgrading from previous verion
As of version 0.5, South migrations have been introduced to handle model schema changes. After upgrading to 0.5, you will have to convert Explorer to a south app by faking the first migration:
python manage.py migrate explorer 0001 --fake
You can then run the rest of the migrations as usual.
python manage.py migrate explorer
If you are installing Explorer for the first time, you can just follow the normal installation instructions below.
Django SQL Explorer
Django SQL Explorer is inspired by Stack Exchange’s Data Explorer and is designed to make the flow of data between people in your company fast, simple, and confusion-free. Quickly write and share SQL queries in a clean, usable query builder, preview the results in the browser, share links to download CSV files, and keep the information flowing baby!
django-sql-explorer is MIT licensed, and pull requests are welcome!
A view of a query
Viewing all queries
Queries can accept parameters. Neato!
Quick access to DB schema info
View & access query history
Features
- Security
Let’s not kid ourselves - this tool is all about giving people access to running SQL in production. So if that makes you nervous (and it should) - you’ve been warned. Explorer makes an effort to not allow terrible things to happen, but be careful! Note there is a setting in the tip (master) to use a different SQL connection than the default django connection. It’s recommended you use a read-only database role.
Explorer supports two different permission checks for users of the tool. Users passing the EXPLORER_PERMISSION_CHANGE test can create, edit, delete, and execute queries. Users who do not pass this test but pass the EXPLORER_PERMISSION_VIEW test can only execute queries. Other users cannot access any part of Explorer. Both permission groups are set to is_staff by default and can be overridden in your settings file.
Enforces a SQL blacklist so destructive queries don’t get executed (delete, drop, alter, update etc). This is not bulletproof and it’s recommended that you instead configure a read-only database role, but when not possible the blacklist provides reasonable protection.
- Easy to get started
100% built on Django’s ORM, so works with Postgresql, Mysql, and Sqlite.
Zero dependencies other than Django and front-end libraries. More detail below.
Just want to get in and write some ad-hoc queries? Go nuts with the Playground area.
Cmd+Enter and/or Ctrl+Enter sill submit and execute the query - no mouse required.
- Looks Reasonably Not Crappy
Thanks to CodeMirror and Bootstrap you might actually enjoy this for querying more than pgadmin or phpmyadmin
- Parameterized Queries
Use $$foo$$ in your queries and Explorer will build a UI to fill out parameters. When viewing a query like ‘SELECT * FROM table WHERE id=$$id$$’, Explorer will generate UI for the ‘id’ parameter.
Parameters are stashed in the URL, so you can share links to parameterized queries with colleagues
- Schema Helper
/explorer/schema/ renders a list of your Django apps’ table and column names (and types) that you can refer to while writing queries. Apps are excludable from this list so users aren’t bogged down in tons of irrelevant tables. See settings documentation below for details.
This is available quickly as a sidebar helper while composing queries (see screenshot)
Supports many_to_many relations as well
- Template Columns
Let’s say you have a query like ‘select id, email from user’ and you’d like to quickly drill through to the profile page for each user in the result. You can create a “template” column to do just that.
Just set up a template column in your settings file:
EXPLORER_TRANSFORMS = [('user', '<a href="https://yoursite.com/profile/{0}/">{0}</a>')]
And change your query to ‘SELECT id AS “user”, email FROM user’. Explorer will match the “user” column alias to the transform and merge each cell in that column into the template string. Cool!
- Query Logs
Explorer will save a snapshot of every query you execute so you can recover lost ad-hoc queries, and see what you’ve been querying.
- Django Admin Support
Download multiple queries at once as a zip file through Django’s admin interface via a built-in admin action.
- Meaningful Test Coverage
95% according to coverage…for what that’s worth
You can run them yourself! Just install factory_boy and run “manage.py test”
Install
Requires Python 2.7. No Python 3 support…yet.
This has been tested only with Django 1.6, however it should work back to 1.4. Please report an issue if you encounter problems on earlier versions of Django.
Install with pip from github:
pip install django-sql-explorer
Add to your installed_apps:
INSTALLED_APPS = ( ..., 'explorer', ... )
Add the following to your urls.py (all Explorer URLs are restricted to staff only per default):
url(r'^explorer/', include('explorer.urls')),
Run syncdb to create the tables (well…just one table really):
python manage.py syncdb
Browse to https://yoursite/explorer/ and get exploring!
Dependencies
An effort has been made to require no packages other than Django and South (for migrations). However a number of front-end dependencies do exist and are documented below. All front-end dependencies are served from CDNJS.com
Name |
Version |
License |
|---|---|---|
3.0.3 |
MIT |
|
2.0.3 |
MIT |
|
1.5.2 |
MIT |
|
3.19.0 |
MIT |
|
1.2.0 |
Creative Commons |
Factory Boy is needed if you’d like to run the tests, which can you do easily:
python manage.py test
and with coverage:
coverage run --source='.' manage.py test explorer
Settings
Setting |
Description |
Default |
|---|---|---|
EXPLORER_SQL_BLACKLIST |
Disallowed words in SQL queries to prevent destructive actions. |
(‘ALTER’, ‘RENAME ‘, ‘DROP’, ‘TRUNCATE’, ‘INSERT INTO’, ‘UPDATE’, ‘REPLACE’, ‘DELETE’, ‘ALTER’, ‘CREATE TABLE’, ‘SCHEMA’, ‘GRANT’, ‘OWNER TO’) |
EXPLORER_SQL_WHITELIST |
These phrases are allowed, even though part of the phrase appears in the blacklist. |
(‘CREATED’, ‘DELETED’) |
EXPLORER_DEFAULT_ROWS |
The number of rows to show by default in the preview pane. |
100 |
EXPLORER_SCHEMA_EXCLUDE_APPS |
Don’t show schema for these packages in the schema helper. |
(‘django.contrib.auth’, ‘django.contrib.contenttypes’, ‘django.contrib.sessions’, ‘django.contrib.admin’) |
EXPLORER_CONNECTION_NAME |
The name of the Django database connection to use. Ideally set this to a connection with read only permissions |
None # Which means use the ‘default’ connection |
EXPLORER_PERMISSION_VIEW |
Callback to check if the user is allowed to view and execute stored queries |
lambda u: u.is_staff |
EXPLORER_PERMISSION_CHANGE |
Callback to check if the user is allowed to add/change/delete queries |
lambda u: u.is_staff |
EXPLORER_TRANSFORMS |
List of tuples like [(‘alias’, ‘Template for {0}’)]. See features section of this doc for more info. |
[] |
EXPLORER_RECENT_QUERY_COUNT |
The number of recent queries to show at the top of the query listing. |
10 |
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
File details
Details for the file django-sql-explorer-0.5.tar.gz.
File metadata
- Download URL: django-sql-explorer-0.5.tar.gz
- Upload date:
- Size: 30.4 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 437f9de5c48bcfd4422159e02265143d9e04f23f0d327486c3eb99d73e5f9c53 |
|
| MD5 | 610182f3d081f6fa27b4727bc9dc2c98 |
|
| BLAKE2b-256 | 2bac37e3bdf504aca8ff3f2d32a30eb2d52444fd21612f6712c01df02edf0455 |
