Migrating MongoDB data to MySQL using Python

The database developers/DBAs go through the task of database migration very often in various projects. Most of the times, such tasks have source and destination using the same technology, for example, MS SQL Server to MS SQL Server or MySQL to MySQL, etc. But, in some challenging projects, we encounter some situations in which, the source and destination technologies differ, leaving us in a mess, for example, MySQL to MongoDB, etc. Even this situation can be handled, where we have to migrate from structured to non-structured database technology. But, when it comes to move data from non-structured to structured technology, it becomes a tedious task, for example, MongoDB to MySQL. 

As a database developer, I was working on one such project where I was required to migrate data from MongoDB to MySQL. I tried the following steps at the initial level for connecting the source (MongoDB) to destination (MySQL) using Python

Requirements :
1. Python 2.7
2. MongoDB 3.2
3. MySQL 5.7
4. MySQLdb : a Python instance for MySQL connectivity
5. Pymongo : a Python driver for MongoDB connectivity


Steps :
1. After installing the software mentioned in the 1,2,3 and 4 points of the requirements part, we installed the Python driver Pymongo. For doing this, we first set the environment variable in our system. 

Steps : The PATH is the system variable that your operating system uses to locate needed executable from the command line or Terminal window. The PATH system variable can be set using System Utility in control panel on Windows In Search, search for and then select: System (Control Panel) Click the Advanced system settings link.

Click Environment Variables. In the section System Variables, find the PATH environment variable and select it. Click Edit. If the PATH environment variable does not exist, click New. In the Edit System Variable (or New System Variable) window, specify the value of the PATH environment variable by mentioning the where that particular software is installed. Click OK. Close all remaining windows by clicking OK. Reopen Command prompt window. 

Then, install Pymongo. 

 

Steps :
2. After installing the required software and software drivers, we can proceed to the connection phase.

3. Firstly, we will connect to MongoDB, using the following code in Python IDLE editor :

import pymongo
import json
from pymongo import MongoClient
if __name__ == '__main__':
client = MongoClient("localhost", 27017, maxPoolSize=50)

4. After initial connection to MongoDB, we can mention the source database and the source collection.
import pymongo
import json
from pymongo import MongoClient

if __name__ == '__main__':
client = MongoClient("localhost", 27017, maxPoolSize=50)
db=client.DatabaseName
collection=db['CollectionName']
cursor = collection.find()

5. Now, after accomplishing the MongoDB connectivity, we can proceed towards the MySQL connectivity. We can write the following code for achieving this task :

import MySQLdb
db = MySQLdb.connect(host="HostName",
user="UserName",
passwd="Credentials",
db="DatabaseName")

6. Now, before proceeding for the further processing, we should create a table in the same database which we have provided above in the connection string, having columns according to the data we are going to migrate from MongoDB database collection.

7. After table creation in MySQL, we will proceed with the following code :

In the code above, we have declared a variable cursor1 which is using predefined method db.cursor() for iterating through the destination MySQL table in the whole insertion process. This is used in the code with execute(sql) method.

We have declared one variable i for keeping a count on how many rows/records have been inserted in the destination MySQL table.

Further, we have declared one cursor abc for inserting records in the destination table. In the same cursor, we have fetched the source attribute values in the respective variables according to the destination table columns, using get(). 
And, we have also done the type casting wherever necessary. In the same cursor abc, we have declared a variable named sql in which we have written the insert statement for the destination table. After insertion, we have called db.commit() to save the changes in the destination table.

At the end, we have closed the database connection using db.close() method.

Thanks for reading
Gunjan Singh