python3批量迁移MySQL已有数据至Elasticsearch7.x（删除索引）

环境

• python 3.6.8
• elasticsearch 7.4.0

实现

#!/usr/bin/python
# -*- coding:utf-8 -*-
import random
import pymysql
from datetime import datetime
from elasticsearch import Elasticsearch


# 官网测试用例
def test():
    # By default we connect to localhost:9200
    es = Elasticsearch(hosts="127.0.0.1:9200")

    # 7.x开始不建议使用doc_type，因此使用默认值"_doc"
    # 指定id
    es.index(index="my-index-000001", doc_type="_doc", id=42,
             body={"any": "data", "timestamp": datetime.now()})

    # ...but not deserialized
    print(es.get(index="my-index-000001", doc_type="test-type", id=42)['_source'])

    # 删除该索引
    es.indices.delete(index='my-index-000001', ignore=[400, 404])

    es.close()


def MysqlMigrate2Es():
    db_config = {
        'host': "127.0.0.1",
        'user': "xxxx",
        'password': "xxx",
        'port': 3306,
        'database': "xxx",
        'charset': "utf8"
    }
    # 连接数据库
    db = pymysql.connect(**db_config)
    cursor = db.cursor()

    # 连接es
    es = Elasticsearch(hosts="127.0.0.1:9200")

    sql = "SELECT goods_id,price,title,product_url,image FROM xxxx.xxx"
    try:

        cursor.execute(sql)
        results = cursor.fetchall()
        i = 0
        for row in results:
            es.index(index='shein', doc_type='_doc', id=i, body={
                'goods_id': row[0],
                'price': row[1],
                'title': row[2],
                'product_url': row[3],
                'image': row[4]
            })
            i += 1
            print(f"id: {i} migrate success")
        # 随机测试
        rand_id = random.randint(0, len(results) - 1)
        print(es.get(index="shein", doc_type='_doc', id=rand_id)['_source'])
        db.close()
    except:
        print("Error: unable to fecth data")


if __name__ == '__main__':
    test()
    MysqlMigrate2Es()