Flask-Admin+SQLAlchemy image upload works in MySQL production env but not in SQLite testcase: InterfaceError -

i have created web application using flask, flask-admin , flask-sqlalchemy administrator can upload images. image uploading functionality imitated largely this flask-admin example. production website, use mysql , uploading works fine. however, in test suite use memory-mapped sqlite database , attempt upload image through same form fails interfaceerror. see this gist full details , reduced test case.

it seems might have type mapping, mysql backend of sqlalchemy appears understand filename of uploaded image must inserted in sql statement while sqlite backend not. however, flask-admin example linked above works absolutely fine , based on sqlite, too.

who can tell me wrong, , needs done make test pass?

edit add: turned out issue known flask-admin developers. see ticket on github.

yes, you're right. reason of problem. problem difference in sqlite , mysql backends.

as can see in stack trace, it's trying bind parameter of type filestorage , fails.

interfaceerror: (sqlite3.interfaceerror) error binding parameter 0 - unsupported type. [sql: u'select picture.id picture_id, picture.name picture_name, picture.path picture_path \nfrom picture \nwhere picture.path = ?'] [parameters: (<filestorage: u'openclipart_hector_gomez_landscape.png' ('image/png')>,)] 

the place want put break point @ method do_execute() in sqlalchemy.engine.default module.

sqlite backend binary extension , cursor comes binary extension (_sqlite3.so). binary extension gets parameter of type filestorage , tries transform sql representation calling filestorage.__conform__() method. class doesn't have such method. that's why fails.

on other hand, mysql backend comes pure python module named mysqldb. calls mysqldb.cursors.basecursor.execute() method, in particular transforms parameter of type filestorage sql representation calling db.literal(), end calling filestorage.__repr__(). , end following query:

'select picture.id picture_id, picture.name picture_name, picture.path picture_path picture picture.path = \\'<filestorage: u\\\\'images.jpeg\\\\' (\\\\'image/jpeg\\\\')>\\''

unexpected, right? you're not sure, works correctly mysql. you? try create 2 pictures same file , integrity error. (_mysql_exceptions.integrityerror) (1062, "duplicate entry 'images.jpeg' key 'path'") [sql: u'insert picture (name, path) values (%s, %s)'] [parameters: ('test', 'images.jpeg')] instead of meaningful error message.

why work in example flask-admin?

you set unique constraint on path column of model. that's failing sql query comes from. before trying insert new one, checks whether picture same path exists in database or not.

how fix

the problem bug in flask_admin.contrib.sqla.validators.unique validator or flask_admin.form.upload.fileuploadfield (they incompatible). validator should use same value, put model flask_admin.form.upload.fileuploadfield.populate_obj() method instead of directly passing filestorage database query. raise issue on github , reference question.

i don't think can fixed in test case, since it's quite significant bug in library rely on. of course, provided want keep unique constraint on path field.