Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-12191

Definition of the Flask extensions to be used in the SQuaSH REST API

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      The original implementation of the SQuaSH REST API used Django REST Framework. We decided to reimplement in Flask to be aligned with the technology stack being used in DAX and other projects in SQuaRE.

      For the Flask implementation these are the main resources I am using:

      Still Keep here the Django references in case they are useful in the future/for anyone:

        Attachments

          Issue Links

            Activity

            Hide
            afausti Angelo Fausti added a comment - - edited

            Updated list of packages and flask extensions:

            flask-restful
            flask-sqlalchemy
            flask-migrate # installs alembic
            marshmallow
            marshmallow-sqlalchemy
            flask-marshmallow
            flast-jwt
            pymysql
            

            Show
            afausti Angelo Fausti added a comment - - edited Updated list of packages and flask extensions: flask-restful flask-sqlalchemy flask-migrate # installs alembic marshmallow marshmallow-sqlalchemy flask-marshmallow flast-jwt pymysql
            Hide
            afausti Angelo Fausti added a comment - - 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)
             
            
            

            Show
            afausti Angelo Fausti added a comment - - 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)  
            Hide
            afausti Angelo Fausti added a comment -

            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)
            

            Show
            afausti Angelo Fausti added a comment - 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)
            Hide
            afausti Angelo Fausti added a comment -

            There are also packages like SQLAlchemy-JSONField however given the experience with similar packages in Django (see DM-8936) I would avoid that option.

            Show
            afausti Angelo Fausti added a comment - There are also packages like SQLAlchemy-JSONField however given the experience with similar packages in Django (see DM-8936 ) I would avoid that option.
            Hide
            afausti Angelo Fausti added a comment - - 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
            

            Show
            afausti Angelo Fausti added a comment - - 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

              People

              • Assignee:
                afausti Angelo Fausti
                Reporter:
                afausti Angelo Fausti
                Watchers:
                Angelo Fausti, Brian Van Klaveren
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: