因为出于个人项目的需要，获取到的数据都是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
-> (
-> 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数据类型
|
1
2
3
4
5
6
7
8
9
|
>>>
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数据库
|
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 row
in
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)
<
type
'str'
>
|
-
3.使用json.loads解析json数据
|
1
2
3
4
5
6
7
8
9
|
>>> 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类型数据，再使用上面的方法，就非常简便了。
