MySQL数据导入到mongodb_mysql datetime 插入mongodb

MySQL数据导入到mongodb

需求：将MySQL表中的数据以键值对的形式导入到mongodb中去

实现步骤：

1. 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进去查看数据是否有导入成功了，这里不再多描述