import mysql tables into mongodb

mysql2mongo.py

#!/bin/env python
import sys
from decimal import Decimal
from optparse import OptionParser
from pymongo import MongoClient
from pymongo.son_manipulator import SONManipulator
import MySQLdb
import MySQLdb.cursors


class Transform(SONManipulator):

    def transform_incoming(self, son, collection):

        for (key, value) in son.items():
            if isinstance(value, Decimal):
                son[key] = float(value)
            elif isinstance(value, str):
                son[key] = value.decode('latin1').encode('utf-8')
            elif isinstance(value, dict): # Make sure we recurse into sub-docs
                son[key] = self.transform_incoming(value, collection)
        return son

    def transform_outgoing(self, son, collection):

        return son


def main():

    usage = "usage: %prog [options]"

    parser = OptionParser()
    parser.add_option("-H", "--host", dest="mysql_host", help="MySQL host")
    parser.add_option("-P", "--port", dest="mysql_port", type="int", default=3306, help="MySQL port")
    parser.add_option("-u", "--user", dest="mysql_user", help="MySQL user")
    parser.add_option("-p", "--password", dest="mysql_passwd", help="MySQL password")
    parser.add_option("-d", "--database", dest="db_name", help="Database name")
    parser.add_option("-t", "--table", dest="table_name", help="MySQL table to export, will be the collection name in MongoDB")

    (options, args) = parser.parse_args()

    if not options.mysql_host or not options.mysql_user or not options.db_name or not options.table_name:
        parser.print_help()
        sys.exit(1)

    mysql_db=MySQLdb.connect(
        host=options.mysql_host, port=options.mysql_port,
        user=options.mysql_user, passwd=options.mysql_passwd,
        db=options.db_name, cursorclass=MySQLdb.cursors.SSDictCursor)

    client = MongoClient()
    mongo_db = client[options.db_name]
    mongo_db.add_son_manipulator(Transform())

    collection = mongo_db[options.table_name]

    offset = 0
    limit = 1000

    sql = 'select * from {0} limit {1}}, {2}'

    while True:

        cursor = mysql_db.cursor()
        cursor.execute(sql.format(options.table_name, offset, limit))

        rows = cursor.fetchall()
        if not rows:
            break

        collection.insert(rows)

        offset += limit


if __name__ == '__main__':
    main()