Use Python to store JSON type data in a MySQL database

0. Description

Because of the need for personal projects, the data obtained are JSON type and are stored in the MySQL database, because JSON type data is not like normal text data, so there are a lot of issues to be aware of when storing MySQL.

On the internet to find a lot of methods, collated a bit more practical and reliable, summed up is the following process:

• The MySQL table needs to ensure that the column type that stores the JSON data is blob;

• When using SQL statements, use the Mysqldb.excape_string function to escape JSON data;

• When querying data, the original Python data type can be obtained by using the json.loads result.

Here is the actual combat, in fact, in my needs, I need to put a dictionary in Python in MySQL, so it can only be converted to JSON to handle.

1. Save JSON data to MySQL in real combat

(1) Data storage

• 1. Create a database table that can store JSON data types

mysql> create table jsondata    ->  (    - > id int (6)  auto_increment primary key,    -> data  blob (1024x768)     -> ); 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. Using Python to generate JSON data types

>>> Import json>>> d = {' name ': ' Xpleaf '}>>> type (d) <type ' dict ' >>>> D_json = j Son.dumps (d) >>> D_json ' {"name": "Xpleaf"} ' >>> type (d_json) <type ' str ' >

• 3. Connect to MySQL database using MySQLdb

>>> Import Mysqldb>>> conn = m.connect (host= ' localhost ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' Spyinux ') >>> cur = conn.cursor ()

• 4. Write Native SQL statements

>>> tsql = "INSERT into Jsondata (data) VALUES (' {JSON} ')";

• 5. Using the MySQLdb method to escape JSON data

>>> sql = Tsql.format (json=mysqldb.escape_string (D_json));>>> SQL ' INSERT into jsondata (data) values (\ ' {\ \ "name\\": \ \ "xpleaf\\"}\ ') '

• 6. Execute SQL statements

>>> cur.execute (SQL) 1l>>> cur.execute (SQL) 1L

(2) Data query

Once the above operation is complete, we have successfully accessed the JSON data into MySQL, the key is to be able to remove the data, and finally revert to the Python dictionary type type.

• 1. Query the stored data in MySQL first

Mysql> SELECT * from jsondata;+----+--------------------+| ID |  Data |+----+--------------------+| 1 | {"Name": "Xpleaf"} |+----+--------------------+1 row in Set (0.00 sec)

• 2. Querying data in the Python interaction

>>> 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. Parsing JSON data using Json.loads

>>> MyData = json.loads (mydata) >>> mydata{u ' name ': U ' xpleaf '}>>> type (mydata) <type ' Dict ' >>>> mydata[' name ']u ' xpleaf ' >>> mydata.get (' name ') u ' xpleaf '

As you can see, at first we used Pythonn to create a dictionary type of data and then convert it to JSON data type so that it could be stored in MySQL, and in this process we need to use the Mysql.escape_string method to escape the JSON data. When we finally queried the data, we used the Json.loads method to parse the JSON data to get the Python dictionary type data that we started to store.

2. Application in Practice

Obviously, if you need to save data from a dictionary or other data type to MySQL in the process of using Python, it is very easy to convert it to JSON type data before using the method above.

This article is from the "fragrant fluttering leaves" blog, please make sure to keep this source http://xpleaf.blog.51cto.com/9315560/1905368

Use Python to store JSON type data in a MySQL database