欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL存儲(chǔ)Json字符串遇到的問題與解決方法

 更新時(shí)間：2022年07月19日 09:17:03   作者：逸辰杳  
要在MySQL中存儲(chǔ)數(shù)據(jù),必須定義數(shù)據(jù)庫(kù)和表結(jié)構(gòu),下面這篇文章主要給大家介紹了關(guān)于MySQL存儲(chǔ)Json字符串遇到的問題與解決方法,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

環(huán)境依賴

Python 2.7
MySQL 5.7
MySQL-python 1.2.5
Pandas 0.18.1

在日常的數(shù)據(jù)處理中，免不了需要將一些序列化的結(jié)果存入到MySQL中。這里以插入JSON數(shù)據(jù)為例，討論這種問題發(fā)生的原因和解決辦法?，F(xiàn)在的MySQL已經(jīng)支持JSON數(shù)據(jù)格式了，在這里不做討論；主要討論如何保證存入到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()

應(yīng)用場(chǎng)景抽象如上所示，將一個(gè)字典經(jīng)過經(jīng)過Json序列化后作為一個(gè)表字段的值存入到Mysql中，按照如上的方式更新數(shù)據(jù)時(shí)，發(fā)現(xiàn)落庫(kù)的JsonString反序列化失??；落庫(kù)結(jié)果和反序列化結(jié)果分別如下所示：

原因分析

對(duì)于字符串中包含引號(hào)等其他特殊符號(hào)的處理思路在大多數(shù)編程語(yǔ)言中都是相通的：即就是通過轉(zhuǎn)義符來保留所需要的特殊字符。Python中也不例外，如上所示，對(duì)于一個(gè)字典{"value": "<img src="xxx.jpg">", "name": "小明"}，要想在編譯器里正確的表示它，就需要通過對(duì)轉(zhuǎn)義包裹xxx.jps的兩個(gè)雙引號(hào)，不然會(huì)提示錯(cuò)誤，所以它的正確寫法為：{"value": "<img src=\"xxx.jpg\">", "name": "小明"}；將序列化后的String作為參數(shù)傳入待執(zhí)行的sql語(yǔ)句中，通過編輯器的debug模式查看的效果如下所示：

而這句sql經(jīng)過編譯器解析后傳入到MySQL去執(zhí)行的本質(zhì)為：'update demo set msg = '{"source": "<img src="xxx.jpg">", "type": "圖片"}' where id = '1'，因此落庫(kù)的實(shí)際結(jié)果其實(shí)并不是目標(biāo)字典對(duì)應(yīng)的序列化結(jié)果，而是目標(biāo)數(shù)據(jù)對(duì)應(yīng)的字面字符串值。

解決方案

可以通過轉(zhuǎn)義符替換、修改sql書寫方式或通過DataFrame.to_sql()三種方式來解決。

方案一 轉(zhuǎn)義符替換

通過上文可以了解到，是因?yàn)?code>\\"xxx.jpg\\"的本質(zhì)即就是"xxx.jpg"，所以數(shù)據(jù)庫(kù)讀到的也就是{"source": "<img src="xxx.jpg">", "type": "圖片"}，從而導(dǎo)致插入的結(jié)果并不能被正確反序列化。可以通過簡(jiǎn)單粗暴的轉(zhuǎn)義符替換方式來解決這個(gè)問題：json_str.replace('\\', '\\\\')，這樣就保證最終的解析結(jié)果為\"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:
            # 通過調(diào)用內(nèi)置的解析函數(shù)literal，將目標(biāo)參數(shù)按照原義解析
            # 解析的依據(jù)詳見源碼的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源碼(如上所示)可以發(fā)現(xiàn)，在傳入待執(zhí)行的sql語(yǔ)句的同時(shí)，還可以傳入?yún)?shù)列表/字典；讓MySQL-Python來幫我們進(jìn)行sql語(yǔ)句的拼接和解析操作，修改上述樣例的實(shí)現(xiàn)方式：

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

通過走讀源碼發(fā)現(xiàn)參數(shù)經(jīng)過literal()方法將Python的對(duì)象轉(zhuǎn)化為對(duì)應(yīng)SQL數(shù)據(jù)的字符串格式；在編譯器Debug模式下可以看到最終將\\"xxx.jpg\\"轉(zhuǎn)化為\\\\\\"xxx.jpg\\\\\\"。至于為什么是六個(gè)反斜杠我自己也不太清楚；不過姑且可以這樣理解：把literal方法的操作可以假定為有一次的序列化，因?yàn)榻o定的數(shù)據(jù)源是\"，所以序列化的結(jié)果為應(yīng)該為\\"，即就是四個(gè)反斜杠；因?yàn)閈“代表的即就是”，而期望落庫(kù)的結(jié)果為"，所以需要再添加兩個(gè)反斜杠。這種解釋不是那么準(zhǔn)確和嚴(yán)謹(jǐn)，但是有利于幫助理解，若有了解底層機(jī)制和原理的，還請(qǐng)留言指教。

推薦使用

方案三 DataFrame.to_sql()

處理數(shù)據(jù)離不開Panda工具包；Pandas的DataFrame.to_sql()方法可以便捷有效的實(shí)現(xiàn)數(shù)據(jù)的插入需求；同樣該方法也能有效的規(guī)避上述這種序列化結(jié)果錯(cuò)誤的情況，因?yàn)镈ataFrame.to_sql()底層的實(shí)現(xiàn)邏輯類似于方案二，也是通過參數(shù)解析的方式來拼接sql語(yǔ)句，核心源碼如下所示，同于不難發(fā)現(xiàn)，DataFrame.to_sql()只能支持insert操作，適用場(chǎng)景比較局限。對(duì)于有唯一索引的表，當(dāng)待插入數(shù)據(jù)與數(shù)據(jù)表中有沖突時(shí)會(huì)報(bào)錯(cuò)，實(shí)際使用時(shí)需要格外注意。

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

補(bǔ)充：

補(bǔ)充：不同情況

1.模糊查詢json類型字段

存儲(chǔ)的數(shù)據(jù)格式（字段名 people_json）：

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

代碼如下（示例）：

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

2.精確查詢json類型字段

存儲(chǔ)的數(shù)據(jù)格式（字段名 people_json）：

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

代碼如下（示例）：

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

3.模糊查詢JsonArray類型字段

存儲(chǔ)的數(shù)據(jù)格式（字段名 people_json）：

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

代碼如下（示例）：

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

4.精確查詢JsonArray類型字段

存儲(chǔ)的數(shù)據(jù)格式（字段名 people_json）：

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

代碼如下（示例）：

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

總結(jié)

到此這篇關(guān)于MySQL存儲(chǔ)Json字符串遇到的問題與解決方法的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)Json字符串內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論