python flask server class for use with dataTables, Editor, yadcf and sqlalchemy-datatables

README.md

These files are incomplete, but define server classes which set up dataTables and Editor for RESTful API.

crudapi.py

###########################################################################################
# crudapi - crudapi handling
#
#       Date            Author          Reason
#       ----            ------          ------
#       09/25/16        Lou King        Create
#
#   Copyright 2016 Lou King
#
###########################################################################################
'''
crudapi - crudapi handling
==================================================
'''

# standard
import traceback

# pypi
import flask
from flask import make_response, request, jsonify, url_for
from flask.ext.login import login_required
from flask.views import MethodView
from datatables import DataTables, ColumnDT
from datatables_editor import DataTablesEditor, dt_editor_response, get_request_action, get_request_data

# homegrown
from . import app
from database_flask import db   # this is ok because this module only runs under flask
from request import addscripts

#----------------------------------------------------------------------
def _editormethod(checkaction='', formrequest=True):
#----------------------------------------------------------------------
    '''
    decorator for CrudApi methods used by Editor

    :param methodcore: function() containing core of method to execute
    :param checkaction: Editor name of action which is used by the decorated method, one of 'create', 'edit', 'remove' or '' if no check required
    :param formrequest: True if request action, data is in form (False for 'remove' action)
    '''
    # see http://python-3-patterns-idioms-test.readthedocs.io/en/latest/PythonDecorators.html
    def wrap(f):
        def wrapped_f(self, *args, **kwargs):
            self._club_id = flask.session['club_id']

            # prepare for possible errors
            self._error = ''
            self._fielderrors = []

            try:
                # verify user can write the data, otherwise abort
                if not self.writepermission():
                    db.session.rollback()
                    cause = 'operation not permitted for user'
                    return dt_editor_response(error=cause)
                
                # get action
                # TODO: modify get_request_action and get_request_data to allow either request object or form object, 
                # and remove if/else for formrequest, e.g., action = get_request_action(request)
                # (allowing form object deprecated for legacy code)
                if formrequest:
                    action = get_request_action(request.form)
                    self._data = get_request_data(request.form)
                else:
                    action = request.args['action']

                if checkaction and action != checkaction:
                    db.session.rollback()
                    cause = 'unknown action "{}"'.format(action)
                    app.logger.warning(cause)
                    return dt_editor_response(error=cause)

                # set up parameters to query, based on whether results are limited to club
                self._dbparms = {}
                if self.byclub:
                    self._dbparms['club_id'] = self._club_id

                # execute core of method
                f(self,*args, **kwargs)

                # commit database updates and close transaction
                db.session.commit()
                return dt_editor_response(data=self._responsedata)
            
            except:
                # roll back database updates and close transaction
                db.session.rollback()
                if self._fielderrors:
                    cause = 'please check indicated fields'
                elif self._error:
                    cause = self._error
                else:
                    cause = traceback.format_exc()
                    app.logger.error(traceback.format_exc())
                return dt_editor_response(data=[], error=cause, fieldErrors=self._fielderrors)
        return wrapped_f
    return wrap


#######################################################################
class CrudApi(MethodView):
#######################################################################
    '''
    provides initial render and RESTful CRUD api

    usage:
        instancename = CrudApi([arguments]):
        instancename.register()

    dbmapping is dict like {'dbattr_n':'formfield_n', 'dbattr_m':f(form), ...}
    formmapping is dict like {'formfield_n':'dbattr_n', 'formfield_m':f(dbrow), ...}
    if order of operation is importand use OrderedDict

    clientcolumns should be like the following. See https://datatables.net/reference/option/columns and 
    https://editor.datatables.net/reference/option/fields for more information

        [
            { 'data': 'name', 'name': 'name', 'label': 'Service Name' },
            { 'data': 'key', 'name': 'key', 'label': 'Key', 'render':'$.fn.dataTable.render.text()' }, 
            { 'data': 'secret', 'name': 'secret', 'label': 'Secret', 'render':'$.fn.dataTable.render.text()' },
            { 'data': 'service', 'name': 'service_id', 
              'label': 'Service Name',
              'type': 'selectize', 
              'options': [{'label':'yes', 'value':1}, {'label':'no', 'value':0}],
              'opts': { 
                'searchField': 'label',
                'openOnFocus': False
               },
              '_update' {
                'url' : <url to retrieve options from>,
                'on' : <event>
              }
            },
        ]

        * name - describes the column and is used within javascript
        * data - used on server-client interface and should be used in the formmapping key and dbmapping value
        * label - used for the DataTable table column and the Editor form label 
        * optional render key is eval'd into javascript
        * id - is specified by idSrc, and should be in the mapping function but not columns

        additionally the update option can be used to _update the options for any type = 'select', 'selectize'

        * _update - dict with following keys
            * url - url to retrieve new options 
            * on - event which triggers update. supported events are
                * 'open' - triggered when form opens
                * 'change' - triggered when 'changeselection' changes [FUTURE]
            * changeselection - required if on:'change' indicated, selection for which change triggers update
    
    servercolumns - if present table will be displayed through ajax get calls

    :param pagename: name to be displayed at top of html page
    :param endpoint: endpoint parameter used by flask.url_for()
    :param dbmapping: mapping dict with key for each db field, value is key in form or function(dbentry)
    :param formmapping: mapping dict with key for each form row, value is key in db row or function(form)
    :param writepermission: function to check write permission for api access
    :param dbtable: db model class for table being updated
    :param byclub: True if results are to be limited by club_id
    :param clientcolumns: list of dicts for input to dataTables and Editor
    :param servercolumns: list of ColumnDT for input to sqlalchemy-datatables.DataTables
    :param idSrc: idSrc for use by Editor
    :param buttons: list of buttons for DataTable, from ['create', 'remove', 'edit', 'csv']
    '''

    decorators = [login_required]

    #----------------------------------------------------------------------
    def __init__(self, **kwargs):
    #----------------------------------------------------------------------
        # the args dict has all the defined parameters to 
        # caller supplied keyword args are used to update the defaults
        # all arguments are made into attributes for self
        self.kwargs = kwargs
        args = dict(pagename = None, 
                    endpoint = None, 
                    dbmapping = {}, 
                    formmapping = {}, 
                    writepermission = lambda: False, 
                    dbtable = None, 
                    clientcolumns = None, 
                    servercolumns = None, 
                    byclub = True,        # NOTE: prevents common CrudApi
                    idSrc = 'DT_RowId', 
                    buttons = ['create', 'edit', 'remove', 'csv'])
        args.update(kwargs)        
        for key in args:
            setattr(self, key, args[key])

        # set up mapping between database and editor form
        self.dte = DataTablesEditor(self.dbmapping, self.formmapping)

    #----------------------------------------------------------------------
    def register(self):
    #----------------------------------------------------------------------
        # create supported endpoints
        my_view = self.as_view(self.endpoint, **self.kwargs)
        app.add_url_rule('/{}'.format(self.endpoint),view_func=my_view,methods=['GET',])
        app.add_url_rule('/{}/rest'.format(self.endpoint),view_func=my_view,methods=['GET', 'POST'])
        app.add_url_rule('/{}/rest/<int:thisid>'.format(self.endpoint),view_func=my_view,methods=['PUT', 'DELETE'])

    #----------------------------------------------------------------------
    def _renderpage(self):
    #----------------------------------------------------------------------
        try:
            club_id = flask.session['club_id']
            
            # verify user can write the data, otherwise abort
            if not self.writepermission():
                db.session.rollback()
                flask.abort(403)
            
            # set up parameters to query, based on whether results are limited to club
            queryparms = {}
            if self.byclub:
                queryparms['club_id'] = club_id

            # peel off any _update options
            update_uptions = []
            for column in self.clientcolumns:
                if '_update' in column:
                    update_uptions.append(column['_update'])

            # DataTables options string, data: and buttons: are passed separately
            dt_options = {
                'dom': '<"H"lBpfr>t<"F"i>',
                'columns': [
                    {
                        'data': None,
                        'defaultContent': '',
                        'className': 'select-checkbox',
                        'orderable': False
                    },
                ],
                'select': True,
                'ordering': True,
                'order': [1,'asc']
            }
            for column in self.clientcolumns:
                dt_options['columns'].append(column)

            # build table data
            if self.servercolumns == None:
                dt_options['serverSide'] = False
                dbrecords = self.dbtable.query.filter_by(**queryparms).all()
                tabledata = []
                for dbrecord in dbrecords:
                    thisentry = self.dte.get_response_data(dbrecord)
                    tabledata.append(thisentry)
            else:
                dt_options['serverSide'] = True
                tabledata = '{}/rest'.format(url_for(self.endpoint))

            ed_options = {
                'idSrc': self.idSrc,
                'ajax': {
                    'create': {
                        'type': 'POST',
                        'url':  '{}/rest'.format(url_for(self.endpoint)),
                    },
                    'edit': {
                        'type': 'PUT',
                        'url':  '{}/rest/{}'.format(url_for(self.endpoint),'_id_'),
                    },
                    'remove': {
                        'type': 'DELETE',
                        'url':  '{}/rest/{}'.format(url_for(self.endpoint),'_id_'),
                    },
                },
                
                'fields': [
                ],
            }
            # TODO: these are editor field options as of Editor 1.5.6 -- do we really need to get rid of non-Editor options?
            fieldkeys = ['className', 'data', 'def', 'entityDecode', 'fieldInfo', 'id', 'label', 'labelInfo', 'name', 'type', 'options', 'opts']
            for column in self.clientcolumns:
                # pick keys which matter
                edcolumn = { key: column[key] for key in fieldkeys if key in column}
                ed_options['fields'].append(edcolumn)

            # commit database updates and close transaction
            db.session.commit()

            # render page
            return flask.render_template('datatables.html', 
                                         pagename = self.pagename,
                                         pagejsfiles = addscripts(['datatables.js']),
                                         tabledata = tabledata, 
                                         tablebuttons = self.buttons,
                                         options = {'dtopts': dt_options, 'editoropts': ed_options, 'updateopts': update_uptions},
                                         inhibityear = True,    # NOTE: prevents common CrudApi
                                         writeallowed = self.writepermission())
        
        except:
            # roll back database updates and close transaction
            db.session.rollback()
            raise

    #----------------------------------------------------------------------
    def _retrieverows(self):
    #----------------------------------------------------------------------
        try:
            club_id = flask.session['club_id']
            
            # verify user can write the data, otherwise abort
            if not self.writepermission():
                db.session.rollback()
                flask.abort(403)
                
            # set up parameters to query, based on whether results are limited to club
            queryparms = {}
            if self.byclub:
                queryparms['club_id'] = club_id

            # columns to retrieve from database
            columns = self.servercolumns

            # get data from database
            rowTable = DataTables(request.args, self.dbtable, self.dbtable.query.filter_by(**queryparms), columns, dialect='mysql')
            output_result = rowTable.output_result()

            # back to client
            return jsonify(output_result)

        except:
            # roll back database updates and close transaction
            db.session.rollback()
            raise

    #----------------------------------------------------------------------
    def get(self):
    #----------------------------------------------------------------------
        if request.path[-4:] != 'rest':
            return self._renderpage()
        else:
            return self._retrieverows()

    #----------------------------------------------------------------------
    @_editormethod(checkaction='create', formrequest=True)
    def post(self):
    #----------------------------------------------------------------------
        # retrieve data from request
        thisdata = self._data[0]
        
        # create item
        dbitem = self.dbtable(**self._dbparms)
        self.dte.set_dbrow(thisdata, dbitem)
        app.logger.debug('creating dbrow={}'.format(dbitem))
        db.session.add(dbitem)
        db.session.flush()

        # prepare response
        thisrow = self.dte.get_response_data(dbitem)
        self._responsedata = [thisrow]


    #----------------------------------------------------------------------
    @_editormethod(checkaction='edit', formrequest=True)
    def put(self, thisid):
    #----------------------------------------------------------------------
        # retrieve data from request
        self._responsedata = []
        thisdata = self._data[thisid]
        
        # edit item
        dbitem = self.dbtable.query.filter_by(id=thisid).first()
        app.logger.debug('editing id={} dbrow={}'.format(thisid, dbitem))
        self.dte.set_dbrow(thisdata, dbitem)
        app.logger.debug('after edit id={} dbrow={}'.format(thisid, dbitem))

        # prepare response
        thisrow = self.dte.get_response_data(dbitem)
        self._responsedata = [thisrow]


    #----------------------------------------------------------------------
    @_editormethod(checkaction='remove', formrequest=False)
    def delete(self, thisid):
    #----------------------------------------------------------------------
        # remove item
        dbitem = self.dbtable.query.filter_by(id=thisid).first()
        app.logger.debug('deleting id={} dbrow={}'.format(thisid, dbitem))
        db.session.delete(dbitem)

        # prepare response
        self._responsedata = []

#######################################################################
class DbQueryApi(MethodView):
#######################################################################
    '''
    class to set up api to get fields from dbtable

    jsonmapping parameter is dict with dbattr indicating the attr to retrieve 
    from dbtable, and jsonkey indicating the key in the json response
    for the returned value.

    a list of {jsonkey: dbattr_value, ...} is returned to the api caller

    url [endpoint]/query is created
    if request has args, the arg=value pairs are treated as a filter into dbtable

    :param endpoint: endpoint parameter used by flask.url_for()
    :param permission: function to check for permission to access this api
    :param byclub: True if table is to be filtered by club_id
    :param dbtable: model class from which query is to be run
    :param jsonmapping: dict {'jsonkey':'dbattr', 'jsonkey':f(dbrow), ...}
    '''

    #----------------------------------------------------------------------
    def __init__(self, **kwargs):
    #----------------------------------------------------------------------

        # the args dict has all the defined parameters to 
        # caller supplied keyword args are used to update the defaults
        # all arguments are made into attributes for self
        self.kwargs = kwargs
        args = dict(
                    endpoint = None,
                    permission = None,
                    byclub = False,
                    dbtable = None,
                    jsonmapping = {},
                   )
        args.update(kwargs)        
        for key in args:
            setattr(self, key, args[key])

        self.convert2json = DataTablesEditor({}, self.jsonmapping)


    #----------------------------------------------------------------------
    def register(self):
    #----------------------------------------------------------------------
        my_view = self.as_view(self.endpoint, **self.kwargs)
        app.add_url_rule('/{}/query'.format(self.endpoint),view_func=my_view,methods=['GET',])

    #----------------------------------------------------------------------
    def get(self):
    #----------------------------------------------------------------------
        # maybe some filters, maybe club_id required
        filters = request.args
        if self.byclub:
            club_id = flask.session['club_id']
            filters['club_id'] = club_id

        # get the data from the table
        dbrows = self.dbtable.query.filter_by(**filters).all()

        # build the response
        response = []
        for dbrow in dbrows:
            responseitem = {}
            responseitem = self.convert2json.get_response_data(dbrow)
            response.append(responseitem)

        return jsonify(response)

datatables-editor.py

###########################################################################################
# datatables_editor
#
#       Date            Author          Reason
#       ----            ------          ------
#       02/08/16        Lou King        Create
#
#   Copyright 2016 Lou King.  All rights reserved
#
###########################################################################################

# standard
from collections import defaultdict

# pypi
import flask
from . import app

class ParameterError(Exception): pass;

#----------------------------------------------------------------------
def dt_editor_response(**respargs):
#----------------------------------------------------------------------
    '''
    build response for datatables editor
    
    :param respargs: arguments for response
    :rtype: json response
    '''

    return flask.jsonify(**respargs)


#----------------------------------------------------------------------
def get_request_action(form):
#----------------------------------------------------------------------
    # TODO: modify get_request_action and get_request_data to allow either request object or form object, 
    # and remove if/else for formrequest, e.g., action = get_request_action(request)
    # (allowing form object deprecated for legacy code)
    '''
    return dict list with data from request.form

    :param form: MultiDict from `request.form`
    :rtype: action - 'create', 'edit', or 'remove'
    '''
    return form['action']

#----------------------------------------------------------------------
def get_request_data(form):
#----------------------------------------------------------------------
    # TODO: modify get_request_action and get_request_data to allow either request object or form object, 
    # and remove if/else for formrequest, e.g., action = get_request_action(request)
    # (allowing form object deprecated for legacy code)
    '''
    return dict list with data from request.form

    :param form: MultiDict from `request.form`
    :rtype: {id1: {field1:val1, ...}, ...} [fieldn and valn are strings]
    '''

    # request.form comes in multidict [('data[id][field]',value), ...]
    
    # fill in id field automatically
    data = defaultdict(lambda: {})

    # fill in data[id][field] = value
    for formkey in form.keys():
        if formkey == 'action': continue
        datapart,idpart,fieldpart = formkey.split('[')
        if datapart != 'data': raise ParameterError, "invalid input in request: {}".format(formkey)

        idvalue = int(idpart[0:-1])
        fieldname = fieldpart[0:-1]

        data[idvalue][fieldname] = form[formkey]

    # return decoded result
    return data


###########################################################################################
class DataTablesEditor():
###########################################################################################
    '''
    handle CRUD request from dataTables Editor

    dbmapping is dict like {'dbattr_n':'formfield_n', 'dbattr_m':f(form), ...}
    formmapping is dict like {'formfield_n':'dbattr_n', 'formfield_m':f(dbrow), ...}
    if order of operation is importand use OrderedDict

    :param dbmapping: mapping dict with key for each db field, value is key in form or function(dbentry)
    :param formmapping: mapping dict with key for each form row, value is key in db row or function(form)
    '''

    #----------------------------------------------------------------------
    def __init__(self, dbmapping, formmapping):
    #----------------------------------------------------------------------
        self.dbmapping = dbmapping
        self.formmapping = formmapping

    #----------------------------------------------------------------------
    def get_response_data(self, dbentry):
    #----------------------------------------------------------------------
        '''
        set form values based on database model object

        :param dbentry: database entry (model object)
        '''

        data = {}

        # create data fields based on formmapping
        for key in self.formmapping:
            # call the function to fill data[key]
            if hasattr(self.formmapping[key], '__call__'):
                callback = self.formmapping[key]
                data[key] = callback(dbentry)
            
            # simple map from dbentry field
            else:
                dbattr = self.formmapping[key]
                data[key] = getattr(dbentry, dbattr)

        return data

    #----------------------------------------------------------------------
    def set_dbrow(self, inrow, dbrow):
    #----------------------------------------------------------------------
        '''
        update database entry from form entry

        :param inrow: input row
        :param dbrow: database entry (model object)
        '''

        for dbattr in self.dbmapping:
            # call the function to fill dbrow.<dbattr>
            if hasattr(self.dbmapping[dbattr], '__call__'):
                callback = self.dbmapping[dbattr]
                setattr(dbrow, dbattr, callback(inrow))

            # simple map from inrow field
            else:
                key = self.dbmapping[dbattr]
                if key in inrow:
                    setattr(dbrow, dbattr, inrow[key])
                else:
                    # ignore -- leave dbrow unchanged for this dbattr
                    pass

datatables.html

{# datatables.html is used, in conjunction with datatables.js to display a single
   dataTable $('#datatable'), with optional Editor support

   parameters:
      pagename:     name of page to be displayed at the top of the page
      tabledata:    data for the table, as expected by DataTables, 
                    with additional label: field per column, used for column headings
      tablebuttons: buttons: option value for options.dtopts
      options:      options with the following keys
        dtopts:     options to be passed to DataTables instance, 
                      except for data: and buttons: options, passed in tabledata, tablebuttons
        editoropts: options to be passed to Editor instance, 
                    if not present, Editor will not be configured
        yadcfopts:  yadcf options to be passed to yadcf 
                    if not present, yadcf will not be configured

      pagejsfiles:  list of js files for page
      pagecssfiles: list of css files for page

      chartloc:     present only if chart is to be drawn
                    'beforeprehtml' - chart is before pretablehtml
                    'beforetable' - chart is between pretablehtml and table
                    'aftertable' - chart is after table
          NOTES: - if chart is to be drawn, pagejsfiles must include script file 
                   which has dtchart() entry point
                 - if table should not be visible, use 'beforetable' and have dtopts 'dom': ''
#}

{% extends "layout.html" %}
{% block pagename %}
    {{ pagename }}
{% endblock %}
{% block css %}
{% endblock %}
{% block scripts %}
  <script type="text/javascript">
    $( document ).ready( function() {
      var 
        options = {{ options|tojson|safe }},
        tabledata  = {{ tabledata|tojson|safe }},
        tablebuttons = {{ tablebuttons|tojson|safe }};

      datatables(tabledata, tablebuttons, options);
      {% if chartloc %} 
        datatables_chart();
      {% endif %}
    })
  </script>
  <script type="text/javascript">
  </script>
{% endblock %}
{% block body %}
  {% if chartloc == 'beforeprehtml' %}
    <div class='dt-chart'></div>
  {% endif %}
  {{ pretablehtml|safe }}
  {% if chartloc == 'beforetable' %}
    <div class='dt-chart'></div>
  {% endif %}
  <table class="" id="datatable" >
    <thead>
      <tr>
        {# assume select column if Editor options are provided #}
        {% if options.editoropts is defined %}
          <th class=""></th>
        {% endif %}
        {% for col in options.dtopts.columns %}
          {% if col.label %}
            <th class="">{{ col.label }}</th>
          {% endif %}
        {% endfor %}
      </tr>
    </thead>
    <tbody>
    </tbody>
  </table>
  {% if chartloc == 'aftertable' %}
    <div class='dt-chart'></div>
  {% endif %}
{% endblock %}

datatables.js

// generic datatables / Editor handling

// data is an list of objects for rendering or url for ajax retrieval of similar object
// buttons is a JSON parsable string, as it references editor which hasn't been instantiated yet
// options is an object with the following keys
//     dtopts:       options to be passed to DataTables instance, 
//                   except for data: and buttons: options, passed in tabledata, tablebuttons
//     editoropts:   options to be passed to Editor instance, 
//                   if not present, Editor will not be configured
//     yadcfopts:    yadcf options to be passed to yadcf 
//                   if not present, yadcf will not be configured


function datatables(data, buttons, options) {

    // configure editor if requested
    if (options.editoropts !== undefined) {
        $.extend(options.editoropts,{table:'#datatable'})
        var editor = new $.fn.dataTable.Editor ( options.editoropts );
    }

    // set up buttons, special care for editor buttons
    var button_options = [];
    for (i=0; i<buttons.length; i++) {
        button = buttons[i];
        if ($.inArray(button, ['create', 'edit', 'remove']) >= 0) {
            button_options.push({extend:button, editor:editor});
        } else {
            button_options.push(button);
        }
    };

    $.extend(options.dtopts, {buttons:button_options});

    // assume data is url if serverSide is truthy
    if (options.dtopts.serverSide) {
        $.extend(options.dtopts, { ajax: data });

    // otherwise assume it is object containing the data to render
    } else {
        $.extend(options.dtopts, { data: data });
    };

    // convert rendering to javascript, // kludge for text rendering
    if (options.dtopts.hasOwnProperty('columns')) {
        for (i=0; i<options.dtopts.columns.length; i++) {
            if (options.dtopts.columns[i].hasOwnProperty('render')) {
                // if (options.dtopts.columns[i].render == 'text') {
                //     options.dtopts.columns[i].render = $.fn.dataTable.render.text();
                // }
                options.dtopts.columns[i].render = eval(options.dtopts.columns[i].render)
            }
        }        
    }

    // define the table
    _dt_table = $('#datatable').DataTable ( options.dtopts );

    // any column filtering required? if so, define the filters
    if (options.yadcfopts !== undefined) {
        yadcf.init(_dt_table, options.yadcfopts);
    }

}

layout.html

<!doctype html>
<head>
  <title>{{self.pagename()|striptags}}</title>
  <!-- Google Analytics -->
  <script>
    (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
    (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
    m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
    })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');

    ga('create', 'UA-49724494-1', 'auto');
    ga('send', 'pageview');

  </script>
  <!-- End Google Analytics -->
</head>
<body>
{% if not printerfriendly %}
  <div class=heading>
    <h1>{{_rrwebapp_productname|safe}}
    {% if request.url[0:16] == "http://127.0.0.1" %} (development){% endif %}
    {% if request.url[0:14] == "http://sandbox" %} (sandbox){% endif %}
  </h1>
    <div class=pagename>{{self.pagename()}}</div>
    <div class=metanav>
      {% if not session.logged_in %}
        <a href="{{ url_for('login') }}">log in</a>
      {% else %}
        {{ session.user_name }}
        {% if not inhibityear and session.year %}
          |
          <select id="year_select" class=sessionoption sessionoptionapi="/_useryear">
            {% for year in session.year_choices %}
              <option value={{ year[0] }} {% if year[0] == session.year %}selected{% endif %}>{{ year[1] }}</option>
            {% endfor %}
          </select>
        {% endif %}
        {% if not inhibitclub and session.club_name %}
          | 
          <select id="club_select" class=sessionoption sessionoptionapi="/_userclub">
            {% for club in session.club_choices %}
              <option value={{ club[0] }} {% if club[0] == session.club_id %}selected{% endif %}>{{ club[1]}}</option>
            {% endfor %}
          </select>
        {% endif %}
        | <a href="{{ url_for('logout') }}">log out</a>
        | <a href="{{ url_for('usersettings')}}?next={{request.url}}">
            <span class="ui-icon ui-icon-gear" style="display: inline-block"></span>
          </a>
      {% endif %}
    </div>  
  </div>
  <div class=wrapper>
    {% if session.nav %}
      <ul id="navigation">
      {% for thisnav in session.nav %}
        {# if list not present, just simple entry #}
        {% if not thisnav.list %}
          <li><a href="{{ thisnav.url }}"{%for a in thisnav.attr%} {{a.name}}="{{a.value}}"{%endfor%}>{{ thisnav.display }}</a></li>
        {% else %}
          <li>
            <a href="#">{{ thisnav.display }}</a>
            <ul>
              {% for subnav in thisnav.list %}
                <li><a href="{{ subnav.url }}"{%for a in subnav.attr%} {{a.name}}="{{a.value}}"{%endfor%}>{{ subnav.display }}</a></li>
              {% endfor %}
            </ul>
          </li>
        {% endif %}
      {% endfor %}
      </ul>
    {% endif %}
    <div class=body>
      {% if error %}<p class=error><strong>Error: {{ error }}</strong></p>{% endif %}
      {% for message in get_flashed_messages() %}
        <div class=flash><p>{{ message }}</p></div>
      {% endfor %}
      <div id="widgets" style="{display: none}"></div>
      <div id="progressbar-container"></div>
      {% block body %}
      {% endblock %}
      {% if addfooter %}  
        <div class="Footer">
          <div>
            <a href="{{url_for('terms') }}">Terms of Service</a>
            <a href="{{url_for('feedback')}}?next={{request.url}}">Questions/Feedback</a>
          </div>
          <span>&copy; 2016 loutilities (Lou King).   All rights reserved.</span>
        </div>
      {% endif %}
    </div>
  </div>
{% else %}
  <div class=body-printerfriendly>
    {{ self.body() }}
  </div>
{% endif %}
<script type="text/javascript">
  var $SCRIPT_ROOT = {{ request.script_root|tojson|safe }};
</script>
  
{# see request.py for list of css, js files #}
{% for css in _rrwebapp_cssfiles %}
  <link rel=stylesheet type=text/css href="{{ css }}">
{% endfor %}
{% for css in pagecssfiles %}
  <link rel=stylesheet type=text/css href="{{ css }}">
{% endfor %}
<style>
  {% if request.url[0:16] == "http://127.0.0.1" %}
    html {background: antiquewhite;}
  {% endif %}
  {% if request.url[0:14] == "http://sandbox" %}
    html {background: lightcyan;}
  {% endif %}
</style>
{% block css %}
{% endblock %}

{% for js in _rrwebapp_jsfiles %}
  <script type="text/javascript" src="{{ js }}"></script>
{% endfor %}
{% if pagejsfiles %}
  {% for js in pagejsfiles %}
    <script type="text/javascript" src="{{ js }}"></script>
  {% endfor %}
{% endif %}
{% block scripts %}
{% endblock %}
</body>
</html>

services.py

###########################################################################################
# services - external service access
#
#       Date            Author          Reason
#       ----            ------          ------
#       09/23/16        Lou King        Create
#
#   Copyright 2016 Lou King.  All rights reserved
#
###########################################################################################

# pypi
from collections import OrderedDict
from datatables import ColumnDT

# homegrown
from crudapi import CrudApi, DbQueryApi
from accesscontrol import owner_permission
from racedb import ApiCredentials, RaceResultService


#----------------------------------------------------------------------
# servicecredentials endpoint
#----------------------------------------------------------------------

sc_dbattrs = 'id,name,key,secret'.split(',')
sc_formfields = 'rowid,name,key,secret'.split(',')
sc_dbmapping = OrderedDict(zip(sc_dbattrs, sc_formfields))
sc_formmapping = OrderedDict(zip(sc_formfields, sc_dbattrs))
sc = CrudApi(pagename = 'Service Credentials', 
             endpoint = 'servicecredentials', 
             dbmapping = sc_dbmapping, 
             formmapping = sc_formmapping, 
             writepermission = owner_permission.can, 
             dbtable = ApiCredentials, 
             clientcolumns = [
                { 'data': 'name', 'name': 'name', 'label': 'Service Name' },
                { 'data': 'key', 'name': 'key', 'label': 'Key', 'render':'$.fn.dataTable.render.text()' }, 
                { 'data': 'secret', 'name': 'secret', 'label': 'Secret', 'render':'$.fn.dataTable.render.text()' }
             ], 
             servercolumns = None,  # no ajax
             byclub = False, 
             idSrc = 'rowid', 
             buttons = ['create', 'edit', 'remove'])
sc.register()

rrs_dbattrs = 'id,apicredentials_id'.split(',')
rrs_formfields = 'rowid,service'.split(',')
rrs_dbmapping = OrderedDict(zip(rrs_dbattrs, rrs_formfields))
rrs_formmapping = OrderedDict(zip(rrs_formfields, rrs_dbattrs))
rrs_formmapping['service'] = lambda rrsrow: ApiCredentials.query.filter_by(id=rrsrow.apicredentials_id).first().name
rrs_apicredentials = ApiCredentials.query.all()
rrs_services = [{'label':ac.name, 'value':ac.id} for ac in rrs_apicredentials]
rrs = CrudApi(pagename = 'Race Result Services', 
             endpoint = 'raceresultservices', 
             dbmapping = rrs_dbmapping, 
             formmapping = rrs_formmapping, 
             writepermission = owner_permission.can, 
             dbtable = RaceResultService, 
             clientcolumns = [
                { 'data': 'service', 'name': 'service_id', 'label': 'Service Name',
                  'type': 'selectize', 'options': [],
                  'opts': { 
                    'searchField': 'label',
                    'openOnFocus': False
                   },
                  '_update': {
                    'url': url_for('services'),
                    'on': 'open',
                  }
                },
             ], 
             servercolumns = None,  # no ajax
             byclub = True, 
             idSrc = 'rowid', 
             buttons = ['create', 'edit', 'remove'])
rrs.register()

rrs_services = DbQueryApi(endpoint = 'services',
                          permission = owner_permission.can,
                          byclub = False,
                          dbtable = ApiCredentials,
                          jsonmapping = {'label':'name', 'value':'id'}
                         )
rrs_services.register()