JSON field with MySQL database

[narcissus]

Hi,

I've created a database model containing a field of type json to access a pre-existent MySQL database (created with mysql server version > 5.7.11) containing the same field of type json into the same table.

I cannot understand why the json data stored into the database are read always as null by web2py DAL functions.

This happens only when I access to MySQL database (ex: Postgres works). It seems that no converter has been implemented for MySQL json field into web2py DAL.

Thanks in advanced for helping me

[Anthony]

In most databases (including MySQL), the DAL simply stores JSON in a text field and converts to and from Python objects when writing/reading the data. The Postgres adapter makes use of the Postgres native JSON field type, but the MySQL adapter does not yet do so.

Some options might be to convert the JSON field to a text field, define a DAL custom field type, or define filter_in/filter_out functions. Or submit a pull request to support the MySQL JSON field type.

Anthony

[narcissus]

Thanks for your answer.

Option 1) In my case I cannot change fields into the original database.

Option 2) and 3) can you provide implementation examples for my specific case

Option 4) I think I'll do it

[Anthony]

On Thursday, June 29, 2017 at 12:49:16 PM UTC-4, narcissus wrote:

Thanks for your answer.

Option 1) In my case I cannot change fields into the original database.

Option 2) and 3) can you provide implementation examples for my specific case

I'm not familiar with how MySQL JSON fields work, so not entirely sure what would be appropriate. Note, the filter_in/filter_out example in the book is a JSON example, so maybe start there.

Anthony

[narcissus]

Thanks, I'll try.

[narcissus]

OK, I've tried filtering_out (as described into the book) and ended up with the following error when accessing into the table containing the JSON field

object of type 'NoneType' has no len()

I think this happens because the problem is not at back-end (field filtering function) but at the 'beginning'. MySQL JSON field value is 'converted' to None by DAL methods.

I'm starting to believe that the right options is actually submit a pull request to support the MySQL JSON field type.

[Rodrigo Sacht]

from gluon.dal import SQLCustomType

json_type = SQLCustomType(type ='text',

                     native='JSON',

                     encoder=(lambda x: x),

                     decoder=(lambda x: x)

                    )

db.define_table('product_country_search',

                Field("init_search", "datetime", default=None),

                Field("end_search", "datetime", default=None),

                Field("status_now", type=json_type, default=None),

               )

Its work for me