[web2py] Re: JSON field with MySQL database

[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

Em quinta-feira, 29 de junho de 2017 13:49:16 UTC-3, narcissus escreveu:
>
> 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
>
> Il giorno giovedì 29 giugno 2017 15:23:37 UTC+2, Anthony ha scritto:
>>
>> 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
>>
>> On Thursday, June 29, 2017 at 9:10:16 AM UTC-4, narcissus wrote:
>>>
>>> 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
>>>
>>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: JSON field with MySQL database

[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.

Il giorno lunedì 3 luglio 2017 10:55:39 UTC+2, narcissus ha scritto:
>
> Thanks, I'll try.
>
> Il giorno giovedì 29 giugno 2017 23:59:03 UTC+2, Anthony ha scritto:
>>
>> 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
>>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: JSON field with MySQL database

[narcissus]

Thanks, I'll try.

Il giorno giovedì 29 giugno 2017 23:59:03 UTC+2, Anthony ha scritto:
>
> 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
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: JSON field with MySQL database

[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

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: JSON field with MySQL database

[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

Il giorno giovedì 29 giugno 2017 15:23:37 UTC+2, Anthony ha scritto:
>
> 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
>
> On Thursday, June 29, 2017 at 9:10:16 AM UTC-4, narcissus wrote:
>>
>> 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
>>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: JSON field with MySQL database

[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

On Thursday, June 29, 2017 at 9:10:16 AM UTC-4, narcissus wrote:
>
> 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
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.