flask-sqlite_admin

SQLite DB Management Blueprint for Flask Applications


Keywords
flask, sqlite, database, admin
License
MIT
Install
pip install flask-sqlite_admin==0.3

Documentation

flask-sqlite-admin

SQLite Database Management Blueprint for Flask Applications.

This package creates a management interface to view/modify sqlite databases in an existing Flask application from only a sqlite file. Somewhat similar to Flask-Admin except nothing is required other than a sqlite file.

Installation

Install the extension with using pip, or easy_install. Pypi Link

$ pip install flask-sqlite-admin

Usage

Basic

To create the interface, add the following to your existing Flask application:

from flask_sqlite_admin.core import sqliteAdminBlueprint
...
sqliteAdminBP = sqliteAdminBlueprint(dbPath = '/path/to/your/sqlite.db')
app.register_blueprint(sqliteAdminBP, url_prefix='/sqlite') 

IMPORTANT NOTE: This package will only work if the first column of a table is a PRIMARY KEY

Navigate to /sqlite and you should see all of your sqlite tables in that database as tabs. Click the tab to view the table contents.

  • Click column header to sort column, click again to reverse sort
  • Click wrench icon on right side of row to edit/delete the row
  • Click add button at bottom of table to add a row

Advanced

sqliteAdminBlueprint parameters

There are several parameters that can be passed to the sqliteAdminBlueprint to modify the interface:

Parameter Purpose Type Example
dbPath provide path to app. this is required string '/var/www/flaskr/flaskr.db'
bpName use if you are going to have multiple instances on the same app each one needs to have a unique bpName string 'profileTables'
tables if left blank all tables in db will show, otherwise a list of tables you want to see can be passed list ['profile','user']
title html title - used on default template or passed as "title" in render_template dict string 'sqlite profile'
h1 h1 value for page - used on default template or passed as "title" in render_template dict string 'SQLite DB for User Profile'
baseLayout the interface is built using bootstrap3. If your application also uses bootstrap3 and pages are contained within a {block body}{block} block in a layout file, you can use your own layout by passing that template name. Otherwise a default template is used with modifiable title/h1 values (using params above) string 'layout.html'
extraRules a few validation rules are checked when modifying the db - null in a non-null field, non-integer in integer field, etc. Custom rules can be created, see section below function see below
decorator a single decorator can be passed - the intention here is for a login decorator to restrict access such as flask-login, but anything can be used. see section below for more info function see below

Extra Rules

I think this is best explained with an example. Say I have a table user_location with a column "ipaddr" and I want to make sure that anything entered in that column is a valid IP address. I can create a rule to validate this and pass it to the blueprint.

Each time a row is entered each column/value pair is checked against the rules. There are a few variables available:

  • self.colData - a dict of the columns schema including:
    • name - column name
    • dataType - declared type of column
    • notNull - 0 = false, 1 = true (ie value cannot be null)
    • primaryKey - 0 = false, 1 = true (ie column is the primary key)
  • self.value - string of the value passed for that column
  • self.postData - probably not needed but a dict of the post request data (ie {'ipaddr':'10.0.0.1'})

To check the rule a function is created whereby if the rule is not passed an exception is raised. Make sure to pass "self" to the function. An example:

def sqliteAdminRulesIP(self):   
    if self.colData['name'] == 'ipaddr':
        try:
            IPNetwork(self.value) 
        except Exception, e:
            raise

Then that would be passed to the blueprint object like so:

sqliteAdminBP = sqliteAdminBlueprint(
  dbPath = '/path/to/your/sqlite.db',
  extraRules = [sqliteAdminRulesIP]
)
app.register_blueprint(sqliteAdminBP, url_prefix='/sqlite') 

Another example, say you dont want a column "firstName" to include anyone with the name "Robert"

def sqliteAdminRulesNoRoberts(self):    
    if self.colData['name'] == 'firstName' and self.value == 'Robert:
        raise ValueError('invalid name `%s`' % self.value)

Decorators

As stated before, a single decorator can be passed and this functionality is intended to be used as an authorization gate such as through the example provided here: http://flask.pocoo.org/snippets/98/. That being said any decorator can be used.

To activate the decorator in this example the following would be passed to the blueprint object:

sqliteAdminBP = sqliteAdminBlueprint(
  dbPath = '/path/to/your/sqlite.db',
  decorator = required_roles('admin', 'user')
)
app.register_blueprint(sqliteAdminBP, url_prefix='/sqlite') 

Additional Considerations

Security

This is not a security heavy interface. There is no CSRF protection on the forms and some string substitution had to be used in generating the queries.

However, the following measures do apply:

  • Login can be restricted through the decorator parameter
  • Only the tables passed in the blueprint object can be viewed/modified
  • Table schemas themselves cannod be modified, added, or deleted - only their contents

Future development

  • Variable rows per page drop down
  • Ability to create, edit, modify tables