Skip to main content

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)
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",

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


Post a Comment

Plz dont forget to like Facebook Page..

Popular posts from this blog

How to encrypt and decrypt Table data in postgres

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >>    Inserting the data in an encrypted format INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes')); select * from userinfo ; >>    Retrieving the data as decrypted format SELECT decrypt(password,decode('password','escape'::text),'aes'::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page..

How to recover msdb database from suspect mode

 It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..

SQL71562: external references are not supported when creating a package from this platform

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option. After connecting to SQL Azure portal when operation started to deployment below errors occurs. Validation of the schema model for data package failed. Error SQL71562: Error validating element has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform . Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming. Solution : I suggested him to create those function to locally