Flask Admin sqlalchemy.exc.InterfaceError model editable input validation sqla.ModelView query fails

Question

I am attempting to create Admin-user changeable drop down boxes that will then be used in another model to track transactions. The db.relationship approach doesn't work very well as the validation list will be changed almost daily. Think of a delivery tracking app (class DeliveryLog is where I store every delivery, class Driver is where I store names of drivers, class Product is where I store what they delivered).

imports and Models:

import os
from flask import Flask, url_for, redirect, render_template, request, abort
from flask_sqlalchemy import SQLAlchemy
from flask_security import Security, SQLAlchemyUserDatastore, UserMixin, RoleMixin, login_required, current_user
import flask_admin
from flask_admin.contrib import sqla
import sys


# Create Flask application
app = Flask(__name__)
app.config.from_pyfile('config.py')
db = SQLAlchemy(app)


# Define models
roles_users = db.Table(
    'roles_users',
    db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
    db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))
)


class Role(db.Model, RoleMixin):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(255))

    def __str__(self):
        return self.name


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime())
    roles = db.relationship('Role', secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))

    def __str__(self):
        return self.email

# Setup Flask-Security
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
security = Security(app, user_datastore)

class Driver(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    driver = db.Column(db.String(255), unique=True)

    def __str__(self):
        return self.driver
    def __repr__(self):
        return self.driver

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product = db.Column(db.String(255), unique=True)

    def __str__(self):
        return self.product
    def __repr__(self):
        return self.product

class DeliveryLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DATE,index=True)
    operator = db.Column(db.String(127))
    on_premises = db.Column(db.Boolean())
    cargo = db.Column(db.String(127))

    def __str__(self):
        return self.operator + ' on ' + str(self.date)

Views, security context and main

class MyAdminView(sqla.ModelView):

    def is_accessible(self):
        if not current_user.is_active or not current_user.is_authenticated:
            return False

        if current_user.has_role('superuser'):
            return True

        return False

    def _handle_view(self, name, **kwargs):
        """
        Override builtin _handle_view in order to redirect users when a view is not accessible.
        """
        if not self.is_accessible():
            if current_user.is_authenticated:
                # permission denied
                abort(403)
            else:
                # login
                return redirect(url_for('security.login', next=request.url))


class MyLogView(MyAdminView):
    form_extra_fields = {
        'cargo': sqla.fields.QuerySelectField(label='Custom Cargo',
            query_factory=db.session.query(Product).all)
    }
    #Product.query.all
    #db.session.query(Product).all

@app.route('/')
@login_required
def index():
    return render_template('index.html')

# Create admin
admin = flask_admin.Admin(
    app,
    'Fruit Tracker',
    base_template='my_master.html',
    template_mode='bootstrap3',
)

# Add model views
admin.add_view(MyAdminView(Role, db.session))
admin.add_view(MyAdminView(User, db.session))
admin.add_view(MyAdminView(Driver, db.session))
admin.add_view(MyAdminView(Product, db.session))
admin.add_view(MyLogView(DeliveryLog, db.session))


# define a context processor for merging flask-admin's template context into the
@security.context_processor
def security_context_processor():
    return dict(
        admin_base_template=admin.base_template,
        admin_view=admin.index_view,
        h=flask_admin.helpers,
        get_url=url_for
    )


if __name__ == '__main__':
    db.create_all()
    if 'debug' in sys.argv:
        app.run(host='0.0.0.0', port=5001, debug=True)
    else:
        app.run(host='0.0.0.0', port=80)

While the form generates fine and has the latest values are in the drop down, the representation of the fields in the model are incorrect to SQL Alchemy and I get :

sqlalchemy.exc.InterfaceError

sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 3 - probably unsupported type. [SQL: 'INSERT INTO delivery_log (date, operator, on_premises, cargo) VALUES (?, ?, ?, ?)'] [parameters: ('2016-08-18', 'Frank', 1, kiwi)]

Please keep in mind that this is a simplified example that may teach others how to sub class views in Flask-Admin, the real application will have large forms (20 fields + at least five drop down boxes), so single-field to single-class overrides don't work here. I think that this pattern of allowing admin-users change how the application behaves is great way to offload maintenance of the application onto non-developers. Thank you

Flask Admin sqlalchemy.exc.InterfaceError model editable input validation sqla.ModelView query fails

Answers (1)

Related Questions