mongo 导入 mysql 通用脚本

mongo 导入 mysql 通用脚本

 

使用说明:

1.需要安装 pandas、sqlalchemy、pymongo 模块, 每次导入 1万条

2.数据库连接信息为 UCloud 上的 mongo，使用时注意更改 mongo 连接信息，包括数据库名

3.main 方法中需要传入两个参数 db_name (表名称) 和 column_delete (该表中不需要导入 mysql 的字段名称, 列表类型)

4.简单使用：mongo 中的表没有数组、字典等类型，且不需要导入某些字段
db_name, column_delete = 'xiangyu_detail', ['vacantStartDate', 'vacantEndDate', 'tabList']
create_new_with_limit(db_name, column_delete, insert_ts)

5.进阶使用：mongo 中的表有数组、字典等类型，且需要该字段(转成字符串、从字典中截取部分数据等)

在 create_new_with_limit 方法的 for 循环中 根据字段名称 对该字段进行更该

代码如下:

from pymongo import MongoClient
import pandas as pd
from sqlalchemy import create_engine
import datetime

def create_new_with_limit(db_name, column_delete, insert_ts):
    print 'starting...'
    host = 'xxx'
    port = xxx
    user = 'xxxx'
    password = 'xxx'
    url = 'mongodb://' + user + ':' + password + '@' + host + ':' + str(port) + '/'
    client = MongoClient(url)
    db = client.rental
    collection = db[db_name]
    select_condition = {'_id': 0}
    if len(column_delete) > 0:
        for temp in column_delete:
            select_condition[ temp] = 0
    print select_condition

    total = collection.find({'ts_string': '2017-11-08'}, select_condition).count()
    print total
    every_time = 10000
    for i in range(0, total / every_time + 1):
        print i
        skip_number = i * every_time
        # collection_pd = pd.DataFrame(list(collection.find({}, select_condition).limit(every_time).skip(skip_number)))
        collection_pd = pd.DataFrame(list(collection.find({'ts_string': '2017-11-08'}, select_condition).limit(every_time).skip(skip_number)))

        print collection_pd.info()

        # 对特殊字段进行处理, 如数组类型、字典类型
        # ziru 中 bizcircle_name 有数组类型, 需要转成字符串
        if 'bizcircle_name' in collection_pd.columns:
            collection_pd['bizcircle_name'] = collection_pd['bizcircle_name'].map(lambda x: ','.join(x) if type(x) == list else x)

        # mogu 中 rentType 字段是字典类型, 需要截取出 value 值
        if 'rentType' and 'image' in collection_pd.columns:
            collection_pd['rentType'] = collection_pd['rentType'].map(lambda x: x['value'])

        collection_pd['insert_ts'] = pd.Series([insert_ts] * len(collection_pd))
        insert_to_mysql(collection_pd, db_name)
    client.close()


def insert_to_mysql(temp_pd, table_name):
    host = 'xxx'
    user = 'xxx'
    password = 'xxx'
    db = 'xxx'
    mysql_url = 'mysql+mysqldb://' + user + ':' + password + '@' + host + '/' + db + '?charset=utf8'
    engine = create_engine(mysql_url, echo=True)
    temp_pd.to_sql(name = table_name, con=engine, flavor=None, index=False, if_exists='append')


def main(insert_ts):
    # lianjia
    db_name, column_delete = 'lianjia_detail', []
    create_new_with_limit(db_name, column_delete, insert_ts)

    # ziru
    db_name, column_delete = 'ziru_detail', ['subway_line_code', 'subway_station_code']
    create_new_with_limit(db_name, column_delete, insert_ts)

    # xiangyu
    db_name, column_delete = 'xiangyu_detail', ['vacantStartDate', 'vacantEndDate', 'tabList']
    create_new_with_limit(db_name, column_delete, insert_ts)

    # mogu
    db_name, column_delete = 'mogu_detail', ['metroInfo']
    create_new_with_limit(db_name, column_delete, insert_ts)


if __name__ == '__main__':
    today = datetime.datetime.now()
    insert_ts = today.strftime('%Y-%m-%d')
    main(insert_ts)