Planet MySQL :: Planet MySQL - Archives - 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 
     begin 
      insert into read(`news_id`,`read`,`date`) values (new.id, new.`read`,new.`date`); 
     end

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 

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

db = client.test 
collection = db.read 

try: 
      cnx=
      mysql.connector.connect(user='test',
      password='12345',host='192.168.1.144',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" 
    else: 
        print(err) cursor = cnx.cursor() 

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

cursor.close() 
cnx.close() 
client.close()

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 

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

try: 
    cnx = mysql.connector.connect(user='test',password='12345',host='192.168.1.144',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") 
    else: 
        print(err) 

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

for i in collection.find(): 
   cursor.execute(sql.format(int(i["read"]),int(i["news_id"]))) 
   print("Number of affected rows: {}".format(cursor.rowcount)) 

cnx.commit() 
cursor.close() 
cnx.close() 
client.close()

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

The post Combining work with MySQL and MongoDB using Python appeared first on Azerbaijan MySQL UG.

MySQL Links