add additional exposition to JSONEncodedDict example re: how to handle operators that require type coercion

[Bob Chen]

Mysql 5.5, SqlAlchemy 1.1, Python 2.6

desc items_tbl
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| info | text | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+

Python code: filter(Items.info.like('%auto%'))

What I expect of the sql query is: WHERE items_tbl.info LIKE '%auto%'

But the actual query I find in the Mysql log is: WHERE items_tbl.info LIKE '\"%auto%\"'

[Mike Bayer]

Can't reproduce and this ticket lacks information about drivers in use, schema, create_engine() string, or a real test case. Also, SQLAlchemy doesn't do anything with the strings being sent to the DBAPI, what you see in the MySQL query log is a product of the DBAPI driver in use.

Test:

from sqlalchemy import create_engine, MetaData, Table, Column, Text, select

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

m = MetaData()
t = Table('test', m, Column('data', Text()))

e.execute(select([t]).where(t.c.data.like('%auto%')))

output in mariadb query log:

#!

160706 11:31:05     4 Connect   scott@localhost as anonymous on test
            4 Query set autocommit=0
            4 Query SHOW VARIABLES LIKE 'sql_mode'
            4 Query SELECT DATABASE()
            4 Query SELECT @@tx_isolation
            4 Query show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
            4 Query SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
            4 Query SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
            4 Query SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
            4 Query SELECT 'x' AS some_label
            4 Query rollback
            4 Query SELECT test.data 
FROM test 
WHERE test.data LIKE '%auto%'
            4 Query rollback

[Bob Chen]

Hi, I realize it has something to do with the type decorator.

MutableDict.as_mutable(JSONEncodedDict) is a very popular piece of code I found from SqlAlchemy's website, in order to achieve a dict-like column. It's underlying data structure in Mysql is also text.

Please run this code below, you will find it has a wrong compilation compared with sqlalchemy.Text.

Could you tell me how to search this kind of field using like, contains, or anything else.

from sqlalchemy import create_engine, MetaData, Table, Column, Text, select
import logging
import json
from sqlalchemy.ext.mutable import Mutable
from sqlalchemy.types import TypeDecorator

class JSONEncodedDict(TypeDecorator):
    "Represents an immutable structure as a json-encoded string."
    impl = Text

    def process_bind_param(self, value, dialect):
        if value is not None:
            try:
                value = json.dumps(value)
            except Exception as e:
                logging.error('JSONEncodedDict: dump error %s' % e)
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            try:
                value = json.loads(value)
            except Exception as e:
                logging.error('JSONEncodedDict: load error %s' % e)
        return value

class MutableDict(Mutable, dict):
    @classmethod
    def coerce(cls, key, value):
        "Convert plain dictionaries to MutableDict."

        if not isinstance(value, MutableDict):
            if isinstance(value, dict):
                return MutableDict(value)

            # this call will raise ValueError
            return Mutable.coerce(key, value)
        else:
            return value

    def __setitem__(self, key, value):
        "Detect dictionary set events and emit change events."

        dict.__setitem__(self, key, value)
        self.changed()

    def __delitem__(self, key):
        "Detect dictionary del events and emit change events."

        dict.__delitem__(self, key)
        self.changed()

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
m = MetaData()
t = Table('test', m, Column('data', MutableDict.as_mutable(JSONEncodedDict)))
e.execute(select([t]).where(t.c.data.like('%auto%')))

[Mike Bayer]

a JSON object is a structure that is non-textual; an operator like LIKE makes no sense. Here, you are looking to re-interpret the JSON structure as the textual representation this particular implementation makes use of (this would not work with a real JSON type such as that of Postgresql). Therefore, you have to tell the system to coerce the column to text for a comparison at the textual level. Forthcoming patch adds additional documentation to the example.

[Mike Bayer]

Add additional documentation re: JSONEncodedDict

Add an example of how to use coerce_compared_value() specific to JSONEncodedDict, as this example class is frequently dropped into applications.

Change-Id: I61639ac03dc5a37673ee8201b99a3c36ab944f00 Fixes: #3742

→ <<cset b33caa64189f>>

[Mike Bayer]

Add additional documentation re: JSONEncodedDict

Add an example of how to use coerce_compared_value() specific to JSONEncodedDict, as this example class is frequently dropped into applications.

Change-Id: I61639ac03dc5a37673ee8201b99a3c36ab944f00 Fixes: #3742 (cherry picked from commit b33caa64189f3fdbbabe4b381460b7610025aa6b)

→ <<cset fd5f9505a6fd>>