python同步数据脚本

#! /usr/bin/env python3.9
#--*-- coding:utf-8 --*--

import time,redis,logging,shlex
import traceback

logger = logging.getLogger('wasadm')

def main(host,port=6379,db=1,password=""):
    r = redis.Redis(host=host,port=port,db=db,password=password)
    if not r:
        print("redis连接失败")
        return None
    pipe,counter = r.pipeline(),0
    try:
        with open('redis_set.txt','r') as fin:
            for command in fin:
                command = command.rstrip()
                logger.debug("command : %s" % command)
                comm = shlex.split(command)
                pipe.execute_command(*comm)
                counter += 1
                if counter % 2000 > 0: 
                    continue
                pipe.execute()
                
                pipe = r.pipeline()
                time.sleep(0.5)
            pipe.execute()
    except :
        logger.error("error occur : %s" % traceback.format_exc())
        logger.error("counter = %d " % counter)
    logger.info("task done , counter = %d " % counter)

if __name__ == "__main__":
    print("redis.__version__ :",redis.__version__)
    logging.basicConfig(
        filename='log/redis-py.log', level=logging.DEBUG, 
        format='%(asctime)s - %(levelname)s - %(message)s')
main(host='IP',port=端口,db=库,password='密码')

脚本逻辑：
调用redis的pipeline功能，每次读取2000行内容写入管道，之后一次提交到redis数据库，该方案不会阻塞redis，不影响业务，可以导入大批量数据，实测1千万行数据导入用时35分钟左右
要求是文件需要提前处理成redis格式，例如hash数据--hmset key value1 value2 value3 

初始版本脚本：
#! /usr/bin/env python3.9
#--*-- coding:utf-8 --*--

import time
import redis
import logging
import shlex

print("redis.__version__ :",redis.__version__)

logger = logging.getLogger('wasadm')
logging.basicConfig(filename='log/redis-py.log', level=logging.ERROR, format='%(asctime)s - %(levelname)s - %(message)s')

r = redis.Redis(host='IP',port=端口,db=库,password='密码')

if not r:
    print("redis连接失败")
    sys.exit()
else:
    pipe = r.pipeline()
    counter = 0

try:
    with open('expire.txt','r') as file:
        for command in file:
            command = command.strip()
            try:
                #pipe.execute_command(*command.split(maxsplit=5))
                #pipe.append(*command.split(maxsplit=1))
                comm = shlex.split(command)
                pipe.execute_command(*comm)
                #pipe.execute_command(command)
            except Exception as e:
                logger.error(f"数据放入管道失败:{str(e)}")

            counter += 1
            
            if counter % 9 == 0:
                try:
                    pipe.execute()
                except Exception as e:
                    logger.error(f"执行pipe失败:{str(e)}")

                pipe = r.pipeline()
                counter = 0
                time.sleep(0.5)
        try:
            pipe.execute()
        except Exception as e:
            logger.error(f"执行pipe失败：(e)")
        print("success")
except Exception as e:
    logger.error(f"打开文件失败:{str(e)}")

该脚本功能和第一个脚本一样，但是代码不够简洁，可以对比两个脚本差异进行学习

import pymysql

# 连接源数据库
source_conn = pymysql.connect('host': '源数据库主机',
                              'port': 源数据库端口,
                              'user': '用户名',
                              'password': '密码',
                              'database': '源数据库名称')
source_cursor = source_conn.cursor()

# 连接目标数据库
target_conn = pymysql.connect('host': '目标数据库主机',
                              'port': 目标数据库端口,
                              'user': '用户名',
                              'password': '密码',
                              'database': '目标数据库名称')
target_cursor = target_conn.cursor()

# 执行数据同步
try:
    source_cursor.execute('SELECT * FROM 源表 where ')
    rows = source_cursor.fetchall()
    rowsCount = len(rows)
    if rowsCount == 0:
        print ('')
    for row in rows:
        # 插入到目标数据库的操作
        target_cursor.execute('INSERT INTO 目标表 VALUES (?, ?, ...) ON DUPLICATE KEY UPDATE ...', row)

    target_conn.commit()
except Exception as e:
    print('数据同步过程中发生错误：', e)
    target_conn.rollback()

# 关闭数据库连接
source_cursor.close()
source_conn.close()
target_cursor.close()
target_conn.close()

通过python同步MySQL-a数据库数据到MySQL-b数据库(未验证)