Json is a good format to share data in different applications, it is also widely used in python. In this tutorial, we will introduce python beginners on how to save json data into a mysql database.
Prepare json data
You can serialize a python object to json data, you also can create a json string data manually.
As to serialize a python object to json data, you can read this tutorial.
Serialize Python Object to String and Deserialize It to Object for Python Beginners
Here we create a json string by manually.
json_data = "{'name' : 'Tom', 'age' : '24'}"Create a MySQLUtil instance to insert json into mysql
Here we use a python MySQLUtil class to operate mysql. The class is in:
Python Select, Insert, Update and Delete Data from MySQL: A Completed Guide
We can connect mysql using MySQLUtil instance first.
mysql = MySQLUtil() mysql.connectDB(host ='localhost', user = 'root', psw = '', db_name = 'test')
We will save json data into test database.
Insert json data into mysql
We can use mysql.execSql() to insert json data. Here is an example.
json_data = pymysql.escape_string(json_data)
sql = "insert into all_tag ( index_name) values ('" + json_data + "') "
mysql.execSql(sql)
After inserting json, we should close mysql connection.
mysql.colseDB()
Run this code, you will see the result:
We have found that the json data has been saved into mysql.
Moreover, if you plan to protect your json data, you can encrypt json before inserting and decrypt it after selecting.
We can encrypt json with base64 algorithm before inserting it into mysql.
To encrypt json with base64 algorithm, you can refer these tutorials.
A Simple Guide to Python Base64 Encode String for Beginners
Improve Python Base64 to Encode String Safely: Replace +, / and = Characters
Here is an example:
json_data = "{'name' : 'Tom', 'age' : '24'}"
json_data = urlsafe_b64encode(json_data)
sql = "insert into all_tag ( index_name) values ('" + json_data + "') "
mysql.execSql(sql)