MySQL is one the most popular open-source relational database management system (RDMS). It is designed to store and process relational data in tabular form in a standardised way. Relational databases are difficult to scale because they require a large amount of memory and computing power.

On the other hand, non-relational databases are designed to store hundreds of billions of records and execute millions of queries every second. MongoDB uses a flexible schema approach and is able to store large amounts of unstructured or semi-structured data.

It can therefore be a challenging and time consuming process to migrate MySQL into MongoDB. Fortunately, Python has a strong connection and data handling capability. To migrate your MySQL Table data to the database collections this blog uses a simple Python script.

Below are the steps to achieve a MySQL to MongoDB migration successfully:

Step 1: Install the Required Modules
Step 2: Read Data from MySQL Table
Step 3: Write to MongoDB Collections

Step 1: Install the Required Modules

pip install pymysql
pip install pymongo

Step 2: Read Data from MySQL Table

You will need to import data from the MySQL source Table for the next step in your migration. A format supported by MongoDB is then used to prepare this data. MongoDB is a NoSQL database that stores data as JSON documents, as you know. Therefore, prior to placing the MySQL data in a target MongoDB database, it is advised to convert the data into JSON format.

import pymysql

connection = pymysql.connect(user=”user_name”,
passwd=”password”,
host=”xxx”,
port=123,
database=”database_neme”)

a = connection.cursor(dictionary=True)
a.execute(“select * from Table_1”)
sql_result = a.fetchall()
print(sql_result)

The following output will be produced if the script is compiled without any errors.

[
{
“id”:1,
“name”:”ABC”,
“description”:”DEF”,
“created_at”:””,
“updated_at”:””
},
{
“id”:2,
“name”:”XYZ”,
“description”:”IJK”,
“created_at”:””,
“updated_at”:””
}
]

The output is a JSON array where dictionary=True argument was passed to the cursor. Now you have fetched the MySQL source output in JSON format, Now let’s write it to MongoDB Collections.

While you may move your data straight from MySQL to MongoDB, it is not the best practice to use a relational document database to guarantee query efficiency for your documents. You should transform data in a format which is better suitable for MongoDB.

Step 3: Write to MongoDB Collections

A MongoDB Collection is basically equivalent to a MySQL Table and it consists of set of documents
The next step is to load the JSON format output data into a MongoDB Collection.

import pymongo

mongodb_host = “mongodb://localhost:27017/”

mongodb_dbname = “mymongo”

myclient = pymongo.MongoClient(mongodb_host)

mydb = myclient[mongodb_dbname]

mycol = mydb[“collection1”]

if len(myresult) > 0:

x = mycol.insert_many(sql_result) #sql_result comes from mysql cursor

print(len(x.inserted_ids))

You do not need to pre-define a schema in order to insert documents because MongoDB is schema-less. MongoDB automatically creates the database, collection, and schema.

These are a few scenarios in which a MySQL database can be converted to MongoDB.

Big Data Friendly: MongoDB can handle massive amounts of data, offering a unified view that traditional databases struggle with.

Flexibility for Complex Data: Moving from MySQL to MongoDB allows you to manage intricate data structures. MongoDB lets you embed documents for nested data and easily accommodate variations within documents over time. It even supports specialized formats like geospatial data.

Cloud Agnostic: MongoDB runs on various platforms, from your desktop to giant data center clusters or even in the cloud. You can install it yourself or leverage MongoDB Atlas, their database-as-a-service offering.

Conclusion
Traditional databases like MySQL are struggling to keep pace with modern application demands. This has led to the rise of NoSQL databases like MongoDB. MongoDB stores data in flexible JSON documents, offering key advantages: a schema that can adapt as needed, easy scaling to handle growth, and faster data retrieval. These features have solidified MongoDB’s position as a leading database solution.

Deepak Gogoi