使用Python向MySQL數據庫中存入json類型數據

0.說明

    因為出于個人項目的需要，獲取到的數據都是json類型的，并且都要存入MySQL數據庫中，因為json類型數據不像一般的文本數據，所以在存入MySQL時需要注意的問題很多。

    在網上找了很多方法，整理了一下比較實用可靠的，總結下來就是下面的過程：

• MySQL表中需要保證存儲json數據的列類型為BLOB；

• 使用sql語句時，使用MySQLdb.excape_string函數來對json數據進行轉義；

• 查詢數據時，將結果使用json.loads就能夠得到原來的Python數據類型；

    下面就來實戰一下，實際上，在我的需求中，我需要將Python中的字典存入MySQL，所以只能先將其轉換為json來處理。

1.實戰存儲json數據到MySQL中

（1）數據存儲

• 1.創建能存儲json數據類型的數據庫表

mysql> create table jsondata
    -> (
    -> id int(6) auto_increment primary key,
    -> data blob(1024)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+-------------------+
| Tables_in_spyinux |
+-------------------+
| jsondata          |
| test              |
+-------------------+
2 rows in set (0.00 sec)

mysql> describe jsondata;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(6) | NO   | PRI | NULL    | auto_increment |
| data  | blob   | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

• 2.使用Python生成json數據類型

>>> import json
>>> d = {'name': 'xpleaf'}
>>> type(d)
<type 'dict'>
>>> d_json = json.dumps(d)
>>> d_json
'{"name": "xpleaf"}'
>>> type(d_json)
<type 'str'>

• 3.使用MySQLdb連接MySQL數據庫

>>> import MySQLdb
>>> conn = m.connect(host='localhost', port=3306, user='root', passwd='123456', db='spyinux')>>> cur = conn.cursor()

• 4.寫原生sql語句

>>> tsql = "insert into jsondata(data) values('{json}')";

• 5.使用MySQLdb方法轉義json數據

>>> sql = tsql.format(json=MySQLdb.escape_string(d_json));
>>> sql
'insert into jsondata(data) values(\'{\\"name\\": \\"xpleaf\\"}\')'

• 6.執行sql語句

>>> cur.execute(sql)
1L
>>> cur.execute(sql)
1L

（2）數據查詢

    上面的操作完成之后，我們已經成功將json的數據存取到MySQL中，關鍵是能不能將該數據取出來，并且最后還原成Python的字典類型類型。

• 1.先在MySQL中查詢存儲的數據

mysql> select * from jsondata;
+----+--------------------+
| id | data               |
+----+--------------------+
|  1 | {"name": "xpleaf"} |
+----+--------------------+
1 row in set (0.00 sec)

• 2.在Python交互器中查詢數據

>>> cur.execute('select * from jsondata');
1L
>>> mydata = cur.fetchall()
>>> mydata
((1L, '{"name": "xpleaf"}'),)
>>> mydata = mydata[0][1]
>>> mydata
'{"name": "xpleaf"}'
>>> type(mydata)
<type 'str'>

• 3.使用json.loads解析json數據

>>> mydata = json.loads(mydata)
>>> mydata
{u'name': u'xpleaf'}
>>> type(mydata)
<type 'dict'>
>>> mydata['name']
u'xpleaf'
>>> mydata.get('name')
u'xpleaf'

    可以看到，最開始我們使用Pythonn創建了一個字典類型的數據，之后將其轉換為json數據類型，以便于存入MySQL中，在這個過程中需要使用MySQL.escape_string方法來對json數據進行轉義，最后查詢數據時，我們使用json.loads方法來解析json數據，從而得到我們最開始存儲的Python字典類型數據。

2.在實際當中的應用

    顯然，如果在使用Python的過程中，需要將字典或其它數據類型的數據存入到MySQL中時，先將其轉換為json類型數據，再使用上面的方法，就非常簡便了。