python脚本迁移数据库_Python迁移MySQL数据到MongoDB脚本

MongoDB是一个文档数据库，在存储小文件方面存在天然优势。随着业务求的变化，需要将线上MySQL数据库中的行记录，导入到MongoDB中文档记录。

一、场景：线上MySQL数据库某表迁移到MongoDB，字段无变化。

二、Python模块：

使用Python的torndb,pymongo和time模块。

*注释：首先安装setup.py,pip,MySQLdb

执行如下命令即可：

pip install torndb

pip install pymongo

三、脚本内容如下：

[root ~]#cat nmytomongo.py

#!/usr/bin/env python

#fielName: mytomongo.py

#Author:xkops

#coding: utf-8

import torndb,pymongo,time

# connect to mysql database

mysql = torndb.Connection(host='127.0.0.1', database='database', user='username', password='password')

#connect to mongodb and obtain total lines in mysql

mongo = pymongo.MongoClient('mongodb://ip').database

mongo.authenticate('username',password='password')

countlines = mysql.query('SELECT max(table_field) FROM table_name')

count = countlines[0]['max(table_field)']

#count = 300

print count

i = 0

j = 100

start_time = time.time()

#select from mysql to insert mongodb by 100 lines.

for i in range(0,count,100):

#print a,b

#print i

#print 'SELECT * FROM quiz_submission where quiz_submission_id > %d and quiz_submission_id <= %d' %(i,j)

submission = mysql.query('SELECT * FROM table_name where table_field > %d and table_field <= %d' %(i,j))

#print submission

if submission:

#collection_name like mysql table_name

mongo.collection_name.insert_many(submission)

else:

i +=100

j +=100

continue

i +=100

j +=100

end_time = time.time()

deltatime = end_time - start_time

totalhour = int(deltatime / 3600)

totalminute = int((deltatime - totalhour * 3600) / 60)

totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)

#print migrate data total time consuming.

print "Data Migrate Finished,Total Time Consuming: %d Hour %d Minute %d Seconds" %(totalhour,totalminute,totalsecond)

*注释：按照自己的需求更改上述代码中的数据库地址，用户，密码，库名，表名以及字段名等。

四、执行迁移脚本:

[root ~]#python nmytomongo.py &> /tmp/migratelog.txt &

脚本执行完成后查看/tmp/migratelog.txt数据迁移消耗的时间。