Combining work with MySQL and MongoDB using Python

Recently i have reviewed a simple web application, where problem was with moving “read” count of news from main table to another table in MySQL. The logic is separating counting “read”s for news from base table. The way you can accomplish this task, you can create a new “read” table in MySQL, then add necessary code to news admin panel for inserting id,read,date into this new “read” table, while adding new articles. But for test purposes, i decide to move this functionality to MongoDB. Overall task is -> Same data must be in MySQL, counting logic must be in MongoDB and data must be synced from MongoDB to MySQL. Any programming language will be sufficient but, Python is an easy one to use. You can use Official mysql-connector-python and pymongo. Firstly you must create empty “read” table in MySQL, insert all necessary data from base table to “read” and there should be after insert trigger for inserting id,read,date into “read” table while adding new articles:

     CREATE trigger `read_after_insert` after insert on `content` for each row 
      insert into read(`news_id`,`read`,`date`) values (, new.`read`,new.`date`); 

Then you should insert all data from MySQL into MongoDB. Here is sample code for selecting old data from MySQL and importing into MongoDB using Python 2.7.x:

import pymongo import mysql.connector 
from datetime import datetime 

    client = pymongo.MongoClient('',27017) 
    print "Connected successfully!!!" 
except pymongo.errors.ConnectionFailure, e: 
   print "Could not connect to MongoDB: %s" % e 

db = client.test 
collection = 

except mysql.connector.Error as err: 
     if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: 
        print "Something is wrong with your user name or password" 
    elif err.errno == errorcode.ER_BAD_DB_ERROR: 
        print "Database does not exist" 
        print(err) cursor = cnx.cursor() 

 sql = "select id,`read`, from_unixtime(`date`) from content order by id" 
 for i in cursor: 
     print i[0],i[1],i[2] 
     doc = {"news_id":int(i[0]),"read":int(i[1]),"date":i[2]} 
     print "inserted" 


Then there must be code changes, in content admin panel, where id,read,date should be inserted into MongoDB. Also values must be incremented in MongoDB. Next step is syncing data from MongoDB to MySQL. You can create a cronjob at night, that in daily manner data is updated from MongoDB to MySQL. Here is a sample Python 3.x code updating data in MySQL from MongoDB:

import pymongo 
from pymongo import MongoClient 
import mysql.connector 

    client = pymongo.MongoClient('',27017) 
    print("Connected successfully!!!") 
except pymongo.errors.ConnectionFailure as e: 
    print("Could not connect to MongoDB: %s" % e) 

    cnx = mysql.connector.connect(user='test',password='12345',host='',database='test') 

except mysql.connector.Error as err: 
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: 
        print("Something is wrong with your user name or password") 
    elif err.errno == errorcode.ER_BAD_DB_ERROR: 
        print("Database does not exist") 

cursor = cnx.cursor() 
sql = "update read set `read` = {} where news_id = {}" 
db = client.test 
collection = 

for i in collection.find(): 
   print("Number of affected rows: {}".format(cursor.rowcount)) 


Simple path with small web app is done. From now it is working.

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group and Python user group leader. QA Engineer, bug hunter by nature and true Pythonista

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.