Angelo Fausti added a comment - 13/Nov/17 11:43 AM - edited Updated list of packages and flask extensions: flask-restful flask-sqlalchemy flask-migrate # installs alembic marshmallow marshmallow-sqlalchemy flask-marshmallow flast-jwt pymysql

Angelo Fausti added a comment - 14/Nov/17 5:16 PM - edited The lack of support for JSON field in Django ORM + MariaDB is one of the reasons we want to migrate the SQuaSH API to Flask + SQLAlchemy. See issue: https://github.com/adamchainz/django-mysql/issues/342 The result of this investigation should demonstrate that we can use or not JSON data type in SQLAlchemy + MariaDB. CONCLUSION: SQLAlchmey 1.2 doesn't support JSON data type on MariaDB 10.3+ yet. http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#sqlalchemy.dialects.mysql.JSON The code snippet for in models.py to create a JSON field looks like from sqlalchemy.dialects.mysql import JSON

   class MeasurementModel(db.Model):
 __tablename__ = 'measurements'   id = db.Column(db.Integer, primary_key = True 
) value = db.Column(db. Float ())
 data = db.Column(JSON()) it creates a field with longtext type instead, see below. So basically SQLAlchemy and Djando ORM are in the same situation right now with respect to JSON support in MariaDB. Server version: 5.5.5-10.3.2-MariaDB-10.3.2+maria~jessie mariadb.org binary distribution   Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> show columns in measurements; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | value | float | YES | | NULL | | | data | longtext | YES | | NULL | | | metric_id | int(11) | YES | MUL | NULL | | +-----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.05 sec)   On MySQL 5.7 we benefit from the JSON support, and from the fact that we can use CloudSQL in our GKE deployment. So this seems the way to go for now. mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 186 Server version: 5.7.14-google-log (Google)   Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use qadb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed mysql> show columns in measurements; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | value | float | YES | | NULL | | | data | json | YES | | NULL | | | metric_id | int(11) | YES | MUL | NULL | | +-----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.05 sec)  

Angelo Fausti added a comment - 15/Nov/17 9:47 AM For some reason the default driver used by SQLAlchemy mysqldb (installed by flask-mysqldb) is linked to a system library and requires MariaDB/MySQL to be installed. I am using pymsql instead and the URI for connection is: "mysql+pymysql://root:{}@{}" .format(SQUASH_DB_PASSWORD, SQUASH_DB_HOST)

Angelo Fausti added a comment - 16/Nov/17 2:02 PM There are also packages like SQLAlchemy-JSONField however given the experience with similar packages in Django (see DM-8936 ) I would avoid that option.

Angelo Fausti added a comment - 27/Nov/17 10:35 AM - edited List of python packages we intend to use for the squash-rest-api. In particular for doing db migrations we are following the procedure outlined here: https://www.safaribooksonline.com/library/view/building-restful-python/9781786462251/ch07s10.html flask-restful flask-sqlalchemy flask-migrate # installs alembic marshmallow marshmallow-sqlalchemy flask-marshmallow flast-jwt pymysql