MySQL存储Json字符串遇到的问题与解决方法

# -*- coding: utf-8 -*-
import MySQLdb
import json

mysql_conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306, charset='utf8')
mysql_cur = mysql_conn.cursor()

increment_id = 1
dic = {"value": "<img src=\"xxx.jpg\">", "name": "小明"}
json_str = json.dumps(dic, ensure_ascii=False)

sql = "update demo set msg = '{0}' where id = '{1}'".fORMat(json_str, increment_id)
mysql_cur.execute(sql)
mysql_conn.commit()
mysql_cur.close()

  def execute(self, query, args=None):
        del self.messages[:]
        db = self._get_db()
        if isinstance(query, unicode):
            query = query.encode(db.unicode_literal.charset)
        if args is not None:
            # 通过调用内置的解析函数literal，将目标参数按照原义解析
            # 解析的依据详见源码的MySQLdb.converters
            if isinstance(args, dict):
                query = query % dict((key, db.literal(item))
                                     for key, item in args.iteritems())
            else:
                query = query % tuple([db.literal(item) for item in args])
        try:
            r = None
            r = self._query(query)
        except TypeError, m:
            if m.args[0] in ("not enough arguments for format string",
                             "not all arguments converted"):
                self.messages.append((ProgrammingError, m.args[0]))
                self.errorhandler(self, ProgrammingError, m.args[0])
            else:
                self.messages.append((TypeError, m))
                self.errorhandler(self, TypeError, m)
        except (SystemExit, KeyboardInterrupt):
            raise
        except:
            exc, value, tb = sys.exc_info()
            del tb
            self.messages.append((exc, value))
            self.errorhandler(self, exc, value)
        self._executed = query
        if not self._defer_warnings: self._warning_check()
        return r

increment_id = 1
dic = {"value": "<img src=\"xxx.jpg\">", "name": "小明"}
json_str = json.dumps(dic, ensure_ascii=False)

sql = "update demo set msg = %s where id = %s"
mysql_cur.execute(sql, [json_str, increment_id])
mysql_conn.commit()
mysql_cur.close()

def insert_statement(self):
        names = list(map(text_type, self.frame.columns))
        flv = self.pd_sql.flavor
        wld = _SQL_WILDCARD[flv]  # wildcard char
        escape = _SQL_GET_IDENTIFIER[flv]

        if self.index is not None:
            [names.insert(0, idx) for idx in self.index[::-1]]

        bracketed_names = [escape(column) for column in names]
        col_names = ','.join(bracketed_names)
        wildcards = ','.join([wld] * len(names))
        # 只支持Insert操作
        insert_statement = 'INSERT INTO %s (%s) VALUES (%s)' % (
            escape(self.name), col_names, wildcards)
        return insert_statement

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

select * from table_name  where people_json->'$.name' like '%zhang%'

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

select * from table_name  where people_json-> '$.age' = 13

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

select * from table_name  where people_json->'$[*].name' like '%zhang%'

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

select * from table_name  where JSON_CONTAINS(people_json,JSON_OBJECT('age', "13"))