MySQL儲存Json字串遇到的問題與解決方法

2022-07-19 14:05:39

環境依賴

Python 2.7
MySQL 5.7
MySQL-python 1.2.5
Pandas 0.18.1

在日常的資料處理中，免不了需要將一些序列化的結果存入到MySQL中。這裡以插入JSON資料為例，討論這種問題發生的原因和解決辦法。現在的MySQL已經支援JSON資料格式了，在這裡不做討論；主要討論如何保證存入到MySQL欄位中的JsonString能被正確解析。

問題描述

# -*- 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()

應用場景抽象如上所示，將一個字典經過經過Json序列化後作為一個表欄位的值存入到Mysql中，按照如上的方式更新資料時，發現落庫的JsonString反序列化失敗；落庫結果和反序列化結果分別如下所示：

原因分析

對於字串中包含引號等其他特殊符號的處理思路在大多數程式語言中都是相通的：即就是通過跳脫符來保留所需要的特殊字元。Python中也不例外，如上所示，對於一個字典{"value": "<img src="xxx.jpg">", "name": "小明"}，要想在編譯器里正確的表示它，就需要通過對跳脫包裹xxx.jps的兩個雙引號，不然會提示錯誤，所以它的正確寫法為：{"value": "<img src="xxx.jpg">", "name": "小明"}；將序列化後的String作為引數傳入待執行的sql語句中，通過編輯器的debug模式檢視的效果如下所示：

而這句sql經過編譯器解析後傳入到MySQL去執行的本質為：'update demo set msg = '{"source": "<img src="xxx.jpg">", "type": "圖片"}' where id = '1'，因此落庫的實際結果其實並不是目標字典對應的序列化結果，而是目標資料對應的字面字串值。

解決方案

可以通過跳脫符替換、修改sql書寫方式或通過DataFrame.to_sql()三種方式來解決。

方案一跳脫符替換

通過上文可以瞭解到，是因為\"xxx.jpg\"的本質即就是"xxx.jpg"，所以資料庫讀到的也就是{"source": "<img src="xxx.jpg">", "type": "圖片"}，從而導致插入的結果並不能被正確反序列化。可以通過簡單粗暴的跳脫符替換方式來解決這個問題：json_str.replace('\', '\\')，這樣就保證最終的解析結果為"xxx.jpg"。

方案二修改sql書寫方式

  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

檢視MySQL-python的execute原始碼(如上所示)可以發現，在傳入待執行的sql語句的同時，還可以傳入參數列/字典；讓MySQL-Python來幫我們進行sql語句的拼接和解析操作，修改上述樣例的實現方式：

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

通過走讀原始碼發現引數經過literal()方法將Python的物件轉化為對應SQL資料的字串格式；在編譯器Debug模式下可以看到最終將\"xxx.jpg\"轉化為\\\"xxx.jpg\\\"。至於為什麼是六個反斜槓我自己也不太清楚；不過姑且可以這樣理解：把literal方法的操作可以假定為有一次的序列化，因為給定的資料來源是"，所以序列化的結果為應該為\"，即就是四個反斜槓；因為“代表的即就是”，而期望落庫的結果為"，所以需要再新增兩個反斜槓。這種解釋不是那麼準確和嚴謹，但是有利於幫助理解，若有了解底層機制和原理的，還請留言指教。

推薦使用

方案三 DataFrame.to_sql()

處理資料離不開Panda工具包；Pandas的DataFrame.to_sql()方法可以便捷有效的實現資料的插入需求；同樣該方法也能有效的規避上述這種序列化結果錯誤的情況，因為DataFrame.to_sql()底層的實現邏輯類似於方案二，也是通過引數解析的方式來拼接sql語句，核心原始碼如下所示，同於不難發現，DataFrame.to_sql()只能支援insert操作，適用場景比較侷限。對於有唯一索引的表，當待插入資料與資料表中有衝突時會報錯，實際使用時需要格外注意。

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

補充：

補充：不同情況

1.模糊查詢json型別欄位

儲存的資料格式（欄位名 people_json）：

{「name」: 「zhangsan」, 「age」: 「13」, 「gender」: 「男」}

程式碼如下（範例）：

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

2.精確查詢json型別欄位

儲存的資料格式（欄位名 people_json）：

{「name」: 「zhangsan」, 「age」: 「13」, 「gender」: 「男」}

程式碼如下（範例）：

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

3.模糊查詢JsonArray型別欄位

儲存的資料格式（欄位名 people_json）：

[{「name」: 「zhangsan」, 「age」: 「13」, 「gender」: 「男」}]

程式碼如下（範例）：

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

4.精確查詢JsonArray型別欄位

儲存的資料格式（欄位名 people_json）：

[{「name」: 「zhangsan」, 「age」: 「13」, 「gender」: 「男」}]

程式碼如下（範例）：

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

總結

到此這篇關於MySQL儲存Json字串遇到的問題與解決方法的文章就介紹到這了,更多相關MySQL儲存Json字串內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL儲存Json字串遇到的問題與解決方法

目錄

環境依賴

問題描述

原因分析

解決方案

方案一跳脫符替換

方案二修改sql書寫方式

方案三 DataFrame.to_sql()

補充：不同情況

總結

熱門文章

MySQL儲存Json字串遇到的問題與解決方法

目錄

環境依賴

問題描述

原因分析

解決方案

方案一 跳脫符替換

方案二 修改sql書寫方式

方案三 DataFrame.to_sql()

補充：不同情況

總結

熱門文章

方案一跳脫符替換

方案二修改sql書寫方式