Store JSON Data into MySQL Using Python: A Simple Guide – Python Tutorial

By | April 11, 2020

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.

Store JSON Data into MySQL Using Python - A Simple Guide

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:

Store JSON Data into MySQL Using Python

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)

Leave a Reply

Your email address will not be published. Required fields are marked *