MySQL数据导入到mongodb
需求:将MySQL表中的数据以键值对的形式导入到mongodb中去
实现步骤:
- MySQL表中的数据导出为键值对形式的json格式文本
- 通过json文本将数据导入到mongodb中
# MySQL数据导出为键值对格式的json文件脚本 vim mysql_to_json.py #coding=utf8 import sys reload(sys) sys.setdefaultencoding("utf-8") import json import csv import sys import MySQLdb import datetime from datetime import date class CJsonEncoder(json.JSONEncoder): def default(self, obj): if isinstance(obj, datetime.datetime): return obj.strftime('%Y-%m-%d %H:%M:%S') elif isinstance(obj, date): return obj.strftime("%Y-%m-%d") else: return json.JSONEncoder.default(self, obj) def dbconnect(): try: db = MySQLdb.connect( host = "127.0.0.1", user = "root", passwd = "123456", ) except Exception as e: sys.exit("Can't connect to Database") return db def DB_to_json(output_json): try: db = dbconnect() cursor = db.cursor() # cursor.execute("select * from devops.auth_user where id >= 11000000 and id < 13000000") # 避免数据量过大超过可用内存 cursor.execute("select * from devops.auth_user;") headers = [x[0] for x in cursor.description] data = cursor.fetchall() json_result=[] for i in data: json_result.append(dict(zip(headers,i))) try: with open(output_json + '.json', 'w') as json_out: # 添加 ensure_ascii=False 解决字符集json解析异常问题 json.dump(json_result, json_out,indent=2,ensure_ascii=False,encoding='utf-8',cls=CJsonEncoder) except Exception as e: print "Error in generating json file \n", e except Exception as e: print e print "\n-------- Enter Json filename -------------\n" if __name__ == "__main__": DB_to_json(output_json="/data/sql/auth_user")
# json文本数据导入mongodb脚本 vim json2mongodb.sh #!/bin/bash /usr/local/mongodb/bin/mongoimport --port=27000 --db devops --collection auth_user --jsonArray --file /data/sql/auth_user.json
实测:
/usr/local/anaconda/bin/python mysql_to_json.py
bash json2mongodb.sh
检查:此时可以通过连接mongodb进去查看数据是否有导入成功了,这里不再多描述