使用Python向MySQL数据库中存入json类型数据

131次阅读

没有评论

共计 2586 个字符，预计需要花费 7 分钟才能阅读完成。

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 语句

1	`>>> 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://www.linuxidc.com/Linux/2017-03/141865.htm

正文完

星哥说事-微信公众号