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

Issue #3742 resolved
Bob Chen
created an issue

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%\"'

Comments (11)

  1. Michael Bayer repo owner

    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
    
  2. Bob Chen reporter

    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%')))
    
  3. Michael Bayer repo owner

    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.

  4. Michael Bayer repo owner

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

  5. Log in to comment