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()
data_merge数据库合并
于 2022-05-18 15:48:16 首次发布