python 从数据库向Elasticsearch导入数据脚本

import pymysql
from elasticsearch import Elasticsearch

hosts = (
    {"host": "192.168.188.130", "port": 9200},
)
es = Elasticsearch(hosts=hosts,
                   sniff_on_start=True,
                   sniff_on_connection_fail=True,
                   sniffer_timeout=60,
                   http_compress=True)
# 数据库信息
db_info = {
    'host': 'rm-uf6sjzuqdryz******.mysql.rds.aliyuncs.com',
    'port': 3306,
    'user': 'mysqlsa',
    'password': '******',
    'db': '*****_agent'
}
mapping = {
    "mappings": {
        "properties": {
            "address": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "agency_store_name": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "area": {
                "type": "keyword"
            },
            "area_code": {
                "type": "keyword"
            },
            "city": {
                "type": "keyword"
            },
            "city_code": {
                "type": "keyword"
            },
            "create_time": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
            },
            "is_online": {
                "type": "integer"
            },
            "latitudey": {
                "type": "double"
            },
            "location": {
                "type": "geo_point"
            },
            "longitudex": {
                "type": "double"
            },
            "store_id": {
                "type": "keyword"
            },
            "tel": {
                "type": "keyword"
            }
        }
    }
}
# 示例数据
doc = {
    "store_id": "B0FFG8G7ST",
    "city": "上海市",
    "city_code": "021",
    "area": "浦东新区",
    "area_code": "310115",
    "agency_store_name": "利兴房产(新德路)",
    "address": "新德路506弄1号",
    "longitudex": 121.693651,
    "latitudey": 31.196841,
    "tel": "021-58989378",
    "createtime": "2019-10-31 14:04:52",
    "is_online": 1,
    "location": "31.196841,121.693651",
}


def init():
    '''
    初始化index
    '''
    es.indices.delete('agent_amap_agency_store')
    es.indices.create('agent_amap_agency_store', body=mapping)


def search_sql(total_sql):
    '''
    连接数据库，执行sql查询
    '''
    try:
        db = pymysql.connect(**db_info)
        # 使⽤cursor()⽅法获取操作游标
        cursor = db.cursor(pymysql.cursors.DictCursor)
        # 使⽤execute⽅法执⾏SQL语句
        # print(total_sql)
        cursor.execute(total_sql)
        cursor.close()
        # 获取数据
        data = cursor.fetchall()
        # 关闭数据库连接
        cursor.close()
        db.commit()
        db.close()
    except Exception as e:
        data = e.args[1]
    return data


sql = 'select * from agent_amap_agency_store limit {},{}'


def put():
    '''
    从数据库每次查1000条 批量插入到Elasticsearch
    '''
    start = 0
    limit = 1000
    while True:
        data = search_sql(sql.format(start, limit))
        if isinstance(data, str):
            continue
        if data:
            create_list = []
            for d in data:
                _id = d.pop('id')
                d['longitudex'] = float(d['longitudex'])
                d['latitudey'] = float(d['latitudey'])
                d['tel'] = d['tel'] or ''
                d['create_time'] = d['create_time'].strftime('%Y-%m-%d %H:%M:%S')
                d['location'] = '{},{}'.format(float(d['latitudey']), float(d['longitudex']))
                create_list.append({"create": {"_index": "agent_amap_agency_store", "_id": _id}})
                create_list.append(d)
            es.bulk(create_list)
            start += 1
        else:
            return


if __name__ == '__main__':
    init()
    put()