Shulou(Shulou.com)06/01 Report--

0. Description

Because for the needs of personal projects, the data obtained are of json type, and all have to be stored in the MySQL database, because the json type data is not like the general text data, so there are many problems that need to be paid attention to when storing MySQL.

Found a lot of methods on the Internet, sorted out the more practical and reliable, summed up the following process:

In the MySQL table, you need to ensure that the column type that stores json data is BLOB

When using SQL statements, use the MySQLdb.excape_string function to escape json data

When querying data, you can get the original Python data type by using json.loads.

In fact, in my requirement, I need to store the dictionary in Python into MySQL, so I can only convert it to json first.

1. Storing json data into MySQL in actual 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 (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)

two。 Use Python to generate json data types

> import json > d = {'name':' xpleaf'} > type (d) > d_json = json.dumps (d) > dumbjson'{"name": "xpleaf"}'> type (d_json)

3. Connect to the 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. Escape json data using the MySQLdb method

> sql = tsql.format (json=MySQLdb.escape_string (d_json)); > > sql'insert into jsondata (data) values (\'{\ "name\\":\ "xpleaf\"}\')'

6. Execute sql statement

> cur.execute (sql) 1L > cur.execute (sql) 1L

(2) data query

After the above operation is completed, we have successfully accessed the data of json to MySQL. The key is whether the data can be taken out and finally restored to the dictionary type of Python.

1. First query the stored data in MySQL

Mysql > select * from jsondata;+----+-+ | id | data | +-+ | 1 | {"name": "xpleaf"} | +-+ 1 row in set (0.00 sec)

two。 Query data in Python Interactor

> > cur.execute ('select * from jsondata'); 1L > mydata = cur.fetchall () > mydata ((1L,' {"name": "xpleaf"}'),) > mydata = mydata [0] [1] > mydata' {"name": "xpleaf"}'> type (mydata)

3. Parsing json data using json.loads

> mydata = json.loads (mydata) > mydata {uplixpleaf'} > type (mydata) > mydata ['name'] uprixpleaf' > mydata.get (' name') uprixpleaf'

As you can see, at first we created a dictionary type data using Pythonn, and then converted it to a json data type for storage in MySQL. In this process, we need to use the MySQL.escape_string method to escape the json data, and finally when we query the data, we use the json.loads method to parse the json data to get the Python dictionary type data that we initially stored.

two。 Application in practice

Obviously, if you need to store data from dictionaries or other data types into MySQL in the process of using Python, it is very easy to convert it to json type data before using the above method.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.