I'm using flask-admin with SQLAlchemy on Postgres database. One of the table fields is a MAC Address, so I used the postgres macaddr datatype. The table model is defined as follows:
class Instances(BaseModel, db.Model):
__tablename__ = 'instances'
id = db.Column(db.Integer, primary_key=True)
mac = db.Column(postgresql.MACADDR, unique=True, nullable=False)
ipv4 = db.Column(postgresql.INET, default=None)
dns = db.Column(postgresql.VARCHAR(32))
state = db.Column(postgresql.ENUM("new", "install", "started", "finished", "ready", name="statetype", create_type=True),
default='install', nullable=False)
The MAC field is unique and this causes problems with flask-admin forms when submitting an invalid MAC Address. The form looks like this:
class InstancesView(SecureModelView):
column_editable_list = ['mac', 'ipv4', 'dns', 'state'] # inline editing
form_args = {
'mac': {
'validators': [validators.required(), validators.MacAddress()]
},
'ipv4': {
'validators': [validators.required(), validators.IPAddress()]
},
'dns': {
'validators': [validators.required()]
},
'state': {
'validators': [validators.required()]
}
}
The unique validator is implemented in flask-admin. It is added automatically and run before any of the validators I add to the field, like the MACAddress validator. This results in a DataError from psycopg2, as the unique validator seems to just take the invalid MAC value and use it in a select on the database.
The best solution to me seems to be running MACAddress validator before unique validator. Is there a possibility to change the order of the validators? Or perhaps run a function before the validators from flask-admin?
Below you can see the full error:
[2017-08-02 10:39:43,649] ERROR in app: Exception on /admin/instances/new/ [POST]
Traceback (most recent call last):
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
psycopg2.DataError: invalid input syntax for type macaddr: "00:ad:qw:ew:00:00"
LINE 3: WHERE instances.mac = '00:ad:qw:ew:00:00'
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
response = self.full_dispatch_request()
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
rv = self.dispatch_request()
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/base.py", line 69, in inner
return self._run_view(f, *args, **kwargs)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/base.py", line 368, in _run_view
return fn(self, *args, **kwargs)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/model/base.py", line 1994, in create_view
if self.validate_form(form):
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/model/base.py", line 1334, in validate_form
return validate_form_on_submit(form)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/helpers.py", line 65, in validate_form_on_submit
return is_form_submitted() and form.validate()
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/wtforms/form.py", line 310, in validate
return super(Form, self).validate(extra)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/wtforms/form.py", line 152, in validate
if not field.validate(self, extra):
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/wtforms/fields/core.py", line 204, in validate
stop_validation = self._run_validation_chain(form, chain)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/wtforms/fields/core.py", line 224, in _run_validation_chain
validator(form, self)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/flask_admin/contrib/sqla/validators.py", line 37, in __call__
.filter(self.column == field.data)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2814, in one
ret = self.one_or_none()
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
ret = list(self)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__
return self._execute_and_instances(context)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2878, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
exc_info
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/home/user/.virtualenvs/flask-gateway/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for type macaddr: "00:ad:qw:ew:00:00"
LINE 3: WHERE instances.mac = '00:ad:qw:ew:00:00'
^
[SQL: 'SELECT instances.id AS instances_id, instances.mac AS instances_mac, instances.ipv4 AS instances_ipv4, instances.dns AS instances_dns, instances.state AS instances_state \nFROM instances \nWHERE instances.mac = %(mac_1)s'] [parameters: {'mac_1': '00:ad:qw:ew:00:00'}]
127.0.0.1 - - [02/Aug/2017 10:39:43] "POST /admin/instances/new/?url=%2Fadmin%2Finstances%2F HTTP/1.1" 500 -
Since it seems that the Unique validator from flask-admin is not always desirable and partly flawed (it was even removed from WTForms) but always attached, I posted an issue in the appropriate tracker. I also created a workaround using an additional field, described below.
Remove the standard mac field from edit and create view, add custom field and specify the field order on the form:
class InstancesView(SecureModelView):
# columns excluded from create and edit view
form_excluded_columns = ['mac', ]
# extra columns in create and edit view
form_extra_fields = {
'mac2': StringField('Mac Address', validators=[validators.required(), validators.mac_address()])
}
# column order in create view
form_create_rules = ('mac2', 'ipv4', 'dns', 'state')
# column order in edit view
form_edit_rules = ('mac2', 'ipv4', 'dns', 'state')
Now the custom field needs to be prefilled manually with the current mac when in edit view:
def on_form_prefill(self, form, id):
form.mac2.data = self.session.query(Instances).filter(Instances.id == id).one().mac
On form submit, the data from the new field must be fed into the original mac field:
def on_model_change(self, form, model, is_created):
if len(model.mac2):
model.mac = model.mac2