Python实现数据库准实时数据同步：区间抽取与批量插入优化

小满

发布时间：2026-10-15 | 4人浏览过

原创

本文介绍使用Python实现不同数据库间准实时数据同步的方案。通过读取配置文件获取源表及更新时间，利用流式游标按时间区间抽取数据以节省内存，并采用executemany进行批量插入以提升写入效率。同时提供日志记录与时间更新机制，确保同步过程可控且可追溯。

功能描述

数据库间的数据同步有多种实现方式，此前文章已做过总结。本文重点展示如何使用Python程序完成数据同步任务。假设源数据库A包含数十张表，需将这些表的数据准实时地同步至结构一致的数据库B。由于通用同步工具在控制粒度上较为有限且配置繁琐，因此选择编写自定义程序。该方案允许自由设定同步的时间区间，并记录符合需求的详细日志，从而实现对同步过程的精细化控制。

代码

本代码实现的功能相对基础，采用面向过程风格编写。有进阶需求的读者可自行将其重构为面向对象模式。此外，当前版本在日志模块中缺乏数据监控功能，可根据实际业务需求进行补充。主要技术要点如下： 1、数据抽取阶段采用基于时间区间的区间抽取策略，结合流式游标迭代器与fetchone方法，有效避免大量数据加载导致的内存溢出问题。 2、数据插入阶段使用executemany(list)进行批量操作，显著加快数据写入效率。


import pymysql
import os
import datetime,time

def update_time(content):
  with open(filepathtime, 'w') as f:
    f.writelines(content)

def recode_log(content):
  with open(filepathlog, 'a') as f:
    f.writelines(content)

def transferdata():
  #1、获取需要抽取的表，抽取数据的时间点
  with open(filepathtime, 'r') as f:
    lines = f.readlines() # 读取所有数据
    print("需要同步的表信息",lines)
    for line in lines:
      startdatetime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
      tablename_list =line.split(',')
      #print(tablename_list)
      #print(tablename_list[-1])
      tablename_list[-1] = tablename_list[-1].replace('\n','')
      #print(tablename_list)
      tablename = tablename_list[0]
      updatetime = tablename_list[1]
      #print(tablename,updatetime)

      #2、抽取此表此时间点的数据，同步
      updatetime_s = datetime.datetime.strptime(updatetime, '%Y-%m-%d %H:%M:%S')
      updatetime_e = (updatetime_s + datetime.timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
      #print(updatetime_s)
      #print(q_sql)
      db = pymysql.connect(host=host_o, port=port_o, user=user_o, passwd=passwd_o, db=db_o)
      cursor = db.cursor()
      q_sql = "select a,b,c from %s where c >= '%s' " % \
          (tablename, updatetime_s)
      #2.1 首先判断下原表中是否有待同步数据，若有则同步且更新同步的时间参考点，若没有则不同步且不更新同步的时间参考点
      try:
        cursor.execute(q_sql)
        results = cursor.fetchone()
        #print(results) #返回是元组
        #print("查询原表数据成功！",tablename)
      except BaseException as e:
        print("查询原表数据失败！",tablename, str(e))
        #记录异常日志
        updatetime_n = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        eachline_log = updatetime_n + '[erro]：' + tablename + str(e) + '\n'
        content_log.append(eachline_log)
        recode_log(content_log)
      db.close()

      if results:
        print("===============================================================================")
        print("有数据可同步",tablename)
        db = pymysql.connect(host=host_o, port=port_o, user=user_o, passwd=passwd_o, db=db_o, charset='utf8', cursorclass=pymysql.cursors.SSDictCursor)
        cursor = db.cursor()
        q_sql1 = "select a,b,c from %s where c >= '%s' and c < '%s' " % \
             (tablename, updatetime_s, updatetime_e)
        #print(q_sql1)
        result_list = []
        try:
          # startdatetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
          cursor.execute(q_sql1)
          #results = cursor.fetchall()
          # enddatetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
          # print(results) #返回是元组
          #使用流式游标迭代器+fetchone，减少内存消耗
          while True:
            result = cursor.fetchone()
            if not result:
              print("此区间无数据", q_sql1)
              break
            else:
              one_list = list(result.values())
              # print(result_list)
              result_list.append(one_list)
          print(result_list) #返回是列表
          #print("查询数据成功！", tablename)
        except BaseException as e:
          print("查询数据失败！", tablename, str(e))
          # 记录异常日志
          updatetime_n = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
          eachline_log = updatetime_n + '[erro]：' + tablename + str(e) + '\n'
          content_log.append(eachline_log)
          recode_log(content_log)
        db.close()

        results_len = (len(result_list))
        if results_len>0:
          #3、将数据插入到目标表中，利用list提高插入效率
          i_sql = "insert into table_t(a,b,c) values (%s,%s,%s)"
          #print(i_sql)
          db = pymysql.connect(host=host_d, port=port_d, user=user_d, passwd=passwd_d, db=db_d)
          cursor = db.cursor()
          try:
            #startdatetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            cursor.executemany(i_sql, result_list)
            db.commit()
            #enddatetime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            print("插入成功！",tablename)
          except BaseException as e:
            db.rollback()
            print("插入失败！", tablename,str(e))
            #记录异常日志
            updatetime_n = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            eachline_log = updatetime_n + '[erro]：' + tablename + str(e) + '\n'
            content_log.append(eachline_log)
            recode_log(content_log)
          db.close()
        enddatetime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))

        #4、如果有数据同步，则更新参考点时间为下一个节点时间
        eachline_time = tablename+','+updatetime_e+'\n' #此时间点是下一个时间点updatetime_e
        content_time.append(eachline_time)
        print("更新表时间点",content_time)

        # 5、记录成功日志
        eachline_log = enddatetime + '[success]：' + tablename + '开始时间' + startdatetime + \
          '结束时间' + enddatetime + '，同步数据量'+str(results_len)+'，当前参考点' + updatetime_e + '\n'
        content_log.append(eachline_log)
        print("日志信息",content_log)
        #print("===============================================================================")
      else:
        print("===============================================================================")
        print("无数据可同步",tablename)
        #db.close()
        enddatetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        # 4、如果无数据同步，则参考点时间不更新
        eachline_time = tablename + ',' + updatetime + '\n' #此时间点还是原时间updatetime
        content_time.append(eachline_time)
        print("不更新表时间点",content_time)

        # 5、成功日志信息
        eachline_log = enddatetime + '[success]：' + tablename + '开始时间' + startdatetime + \
          '结束时间' + enddatetime + '，同步数据量0'+ '，当前参考点' + updatetime_e + '\n'
        content_log.append(eachline_log)
        print("日志信息",content_log)
        #print("===============================================================================")

    #更新配置文件，记录日志
    update_time(content_time)
    recode_log(content_log)

if __name__ == '__main__':
  filepathtime = 'D:/test/table-time.txt'
  filepathlog = 'D:/test/table-log.txt'
  host_o = 'localhost'
  port_o = 3306
  user_o = 'root'
  passwd_o = 'root@123'
  db_o = 'csdn'
  host_d = 'localhost'
  port_d = 3306
  user_d = 'root'
  passwd_d = 'root@123'
  db_d = 'csdn'
  content_time = []
  content_log = []
  transferdata()

  #每5分钟执行一次同步
  # while True:
  #   transferdata()
  #   time.sleep(300)

table-time.txt配置文件，格式说明：每行包含源数据库表名及该表的最小同步时间time，字段间以逗号分隔。若涉及多个表的同步，可在配置中指定多个对应的时间点。每次脚本执行完毕后，系统会自动更新同步时间time。默认时间间隔设置为1小时，用户可根据实际情况在updatetime_e变量中调整增量参数。

table-log.txt 用于记录每次同步任务的执行结果，或记录执行过程中发生的异常日志。建议定期对该日志文件进行清理，以维持系统存储空间的整洁。

到此这篇关于python实现不同数据库间数据同步功能的文章就介绍到这了,更多相关python实现数据同步内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！