python迁移数据

# -*- coding:utf-8 -*-
import pymysql
from datetime import datetime
import time 

def qianyi( table, batch_size ):
    db1 = pymysql.connect(
    host='192.168.0.1',
    port=4000,
    user='dev_user',
    password='password',
    database='db1',
    charset='utf8'
    )
    cursor1 = db1.cursor()

    db2 = pymysql.connect(
    host='192.168.0.2',
    port=4000,
    user='fat_user',
    password='password',
    database='db2',
    charset='utf8'
    )
    cursor2 = db2.cursor()

    get_column_length=f" SELECT  * from  {table}  limit 1  "    # 拼接获取源表有多少个列的SQL
    cursor1.execute(get_column_length)  
    col_len=len(cursor1.fetchone())     #获取源表有多少个列
    val_str = ''
    for i in range (1,col_len):
        val_str=val_str+'%s'+','
    val_str=val_str+'%s'    

    #MySQL批量插入语法是  insert into tb_name values(%s,%s,%s,%s)
    #Oracle批量插入语法是 insert into tb_name values(:1,:2,:3)
    insert_sql= f" insert ignore into  {table}  values({val_str} ) "
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    offset=0
    with cursor1:
        cursor1.execute(f"SELECT COUNT(*) FROM {table}   ")
        total_records = cursor1.fetchone()[0]
        print(f'total_records : {total_records}') 
        while offset < total_records:
            sql = f" SELECT  * from  {table}  where id in( SELECT  id from  {table} order by id limit  {batch_size} OFFSET {offset} ) "
            cursor1.execute(sql )
            print(sql)
            offset += batch_size

            data2 = list(cursor1.fetchall() )
            cursor2.executemany(insert_sql, data2)
            db1.commit()            
            db2.commit()
            print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))

    db1.close()
    db2.close()

qianyi( table='db1.table', batch_size=10000 )