因为出于个人项目的需要,获取到的数据都是json类型的,并且都要存入MySQL数据库中,因为json类型数据不像一般的文本数据,所以在存入MySQL时需要注意的问题很多。
在网上找了很多方法,整理了一下比较实用可靠的,总结下来就是下面的过程:
MySQL表中需要保证存储json数据的列类型为BLOB;
使用sql语句时,使用MySQLdb.excape_string函数来对json数据进行转义;
查询数据时,将结果使用json.loads就能够得到原来的Python数据类型;
下面就来实战一下,实际上,在我的需求中,我需要将Python中的字典存入MySQL,所以只能先将其转换为json来处理。
1.实战存储json数据到MySQL中
(1)数据存储
1.创建能存储json数据类型的数据库表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql>create table jsondata
-> (
-> idint(6) auto_incrementprimary key,
-> data blob(1024)
-> );
Query OK, 0rows affected (0.25 sec)
mysql> show tables;
+-------------------+
| Tables_in_spyinux |
+-------------------+
| jsondata |
| test |
+-------------------+
2rows 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 | |
+-------+--------+------+-----+---------+----------------+
2rows in set (0.00 sec)
2.使用Python生成json数据类型
1
2
3
4
5
6
7
8
9
>>>import json
>>> d= {'name':'xpleaf'}
>>>type(d)
>>> d_json= json.dumps(d)
>>> d_json
'{"name": "xpleaf"}'
>>>type(d_json)
3.使用MySQLdb连接MySQL数据库
1
2
>>>import MySQLdb
>>> conn= m.connect(host='localhost', port=3306, user='root', passwd='123456', db='spyinux')>>> cur= conn.cursor()
4.写原生sql语句
1
>>> tsql= "insert into jsondata(data) values('{json}')";
5.使用MySQLdb方法转义json数据
1
2
3
>>> sql= tsql.format(json=MySQLdb.escape_string(d_json));
>>> sql
'insert into jsondata(data) values(\'{\\"name\\": \\"xpleaf\\"}\')'
6.执行sql语句
1
2
3
4
>>> cur.execute(sql)
1L
>>> cur.execute(sql)
1L
(2)数据查询
上面的操作完成之后,我们已经成功将json的数据存取到MySQL中,关键是能不能将该数据取出来,并且最后还原成Python的字典类型类型。
1.先在MySQL中查询存储的数据
1
2
3
4
5
6
7
mysql>select *from jsondata;
+----+--------------------+
| id | data |
+----+--------------------+
| 1 | {"name":"xpleaf"} |
+----+--------------------+
1 rowin set (0.00 sec)
2.在Python交互器中查询数据
1
2
3
4
5
6
7
8
9
10
>>> cur.execute('select * from jsondata');
1L
>>> mydata= cur.fetchall()
>>> mydata
((1L,'{"name": "xpleaf"}'),)
>>> mydata= mydata[0][1]
>>> mydata
'{"name": "xpleaf"}'
>>>type(mydata)
3.使用json.loads解析json数据
1
2
3
4
5
6
7
8
9
>>> mydata= json.loads(mydata)
>>> mydata
{u'name': u'xpleaf'}
>>>type(mydata)
>>> mydata['name']
u'xpleaf'
>>> mydata.get('name')
u'xpleaf'
可以看到,最开始我们使用Pythonn创建了一个字典类型的数据,之后将其转换为json数据类型,以便于存入MySQL中,在这个过程中需要使用MySQL.escape_string方法来对json数据进行转义,最后查询数据时,我们使用json.loads方法来解析json数据,从而得到我们最开始存储的Python字典类型数据。
2.在实际当中的应用
显然,如果在使用Python的过程中,需要将字典或其它数据类型的数据存入到MySQL中时,先将其转换为json类型数据,再使用上面的方法,就非常简便了。
本文转自 xpleaf 51CTO博客,原文链接:http://blog.51cto.com/xpleaf/1905368,如需转载请自行联系原作者