data_merge数据库合并

原创已于 2022-05-18 17:40:31 修改 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #python

于 2022-05-18 15:48:16 首次发布

本文介绍了使用pymysql库进行大规模数据迁移的过程。通过设置批处理大小，优化了数据插入性能，减少了数据库操作时间。文章详细展示了如何从一个数据库连接获取数据，分批插入到另一个数据库，并记录了各步骤的耗时，为数据库维护和数据同步提供了实战参考。

import pymysql
import time

if __name__ == '__main__':

    scs_db = pymysql.connect(host='XXXXXXXXXX',
                             port=XXXXXXXXXX,
                             user='XXXXXXXXXX',
                             password='XXXXXXXXXX',
                             db='XXXXXXXXXX',
                             charset='utf8mb4')

    select_sql = """select id + 40000, `name`, origin, create_user, create_time, update_time, 
    recommend_flag, recommend_weight, delete_status, picture_url, `explain`, explain_picture_url from c_topic"""
    scs_cursor = scs_db.cursor()
    scs_record = scs_cursor.execute(select_sql)
    print('总条数：', int(scs_record))

    # database
    scs_bak_db = pymysql.connect(host='XXXXXXXXXX',
                                 port=XXXXXXXXXX,
                                 user='XXXXXXXXXX',
                                 password='XXXXXXXXXX',
                                 db='XXXXXXXXXX',
                                 charset='utf8mb4')
    scs_bak_cursor = scs_bak_db.cursor()
    insert_sql = """insert into c_topic(id, `name`, origin, create_user, create_time, update_time, 
    recommend_flag, recommend_weight, delete_status, picture_url, `explain`, explain_picture_url, read_count) 
    values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0)"""

    fetch_many_start_time = time.time()
    try:
        # 每次执行1w条
        num = 100
        for i in range(int(scs_record / num)):
            print(i)
            _1 = time.time()
            batch_scs_data = scs_cursor.fetchmany(num)
            _2 = time.time()
            print('1：', str(_2 - _1))
            scs_bak_cursor.executemany(insert_sql, batch_scs_data)
            _3 = time.time()
            print('2：', str(_3 - _2))

        print('批次数据执行时间：', str(time.time() - fetch_many_start_time))

        fetch_all_start_time = time.time()
        # 剩下的一次性执行完
        last_scs_data = scs_cursor.fetchall()
        _4 = time.time()
        print('4：', str(_4 - _3))
        scs_bak_cursor.executemany(insert_sql, last_scs_data)
        _5 = time.time()
        print('5：', str(_5 - _4))
        print('剩余数据执行时间：', str(time.time() - fetch_all_start_time))

        scs_bak_db.commit()
    finally:
        scs_db.close()
        scs_bak_db.close()