使用Python向MySQL資料庫中存入json類型資料

標籤：python   json   mysqldb   

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類型資料，再使用上面的方法，就非常簡便了。

本文出自 “香飄葉子” 部落格，請務必保留此出處http://xpleaf.blog.51cto.com/9315560/1905368

使用Python向MySQL資料庫中存入json類型資料