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

    因为出于个人项目的需要，获取到的数据都是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类型数据，再使用上面的方法，就非常简便了。

本文转自 xpleaf 51CTO博客，原文链接：http://blog.51cto.com/xpleaf/1905368，如需转载请自行联系原作者