dm达梦数据库之间全部表增量同步脚本

print('\033[0:32m达梦模式之间全部表同步脚本开始执行\033[m')
import jaydebeapi
import time
from datetime import date

start_time = time.time()  # 记录开始时间
#目标库
url = 'jdbc:dm://ip:5236/目标库模式名称'
user = '账号'
password = '密码'
dirver = 'dm.jdbc.driver.DmDriver'
jarFile = 'D:\software\dbeaver\JAVA_Mybatis\lib\DmJdbcDriver18.jar'
#源库
urls = 'jdbc:dm://ip:5236/源库模式名称'
users = '账号'
passwords = '密码'
source_mode='源库模式名称'
target_end_mode='目标库模式名称'

dm_conn =jaydebeapi.connect(dirver, url, [user, password], jarFile)
dm_cursor = dm_conn.cursor()
source_dm_conn=jaydebeapi.connect(dirver, urls, [users, passwords], jarFile)
source_dm_cursor=source_dm_conn.cursor()
# 获取达梦数据库所有表名
#dm_cursor.execute(f"select OWNER,TABLE_NAME from ALL_CONSTRAINTS WHERE OWNER='{source_mode}'")
#dm_cursor.execute(f"SELECT TABLE_NAME FROM USER_TAB_COMMENTS")
source_dm_cursor.execute(f"select DISTINCT TABLE_NAME,OWNER from ALL_CONSTRAINTS WHERE OWNER='{source_mode}' and TABLE_NAME IN ('custom_card','dim_act_type')")
tables = source_dm_cursor.fetchall()
#获取表注释
source_dm_cursor.execute(f"SELECT OWNER,TABLE_NAME,COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='{source_mode}'")
table_chinses=source_dm_cursor.fetchall()
# 遍历所有表，增量更新到达梦
for table in tables:
    #print(table[0])
    table_name = table[0]
    target_table_name=table[0].upper()
    #print(table_name)
    # 获取表结构
    source_dm_cursor.execute(f"SELECT * FROM {source_mode}.{table_name} WHERE 1=0")
    dm_columns = [column[0].upper() for column in source_dm_cursor.description]
    for table_note in table_chinses:
        if table_note[1]==table[0]:
            table_note_result=table_note[2]

    #print(dm_columns)
    #print(dm_columns)
    # 检查是否有时间字段
    has_time_field = False
    for column in dm_columns:
        if 'TIME' in column.upper() or 'DATE' in column.upper():
            has_time_field = True
            if column.isupper():
                update_time = column.upper()

            else:

                update_time = column.lower()



            #print(update_time)
            break
    if has_time_field:
        dm_cursor.execute(f"SELECT max({update_time}) FROM {target_end_mode}.{target_table_name}")
        result = dm_cursor.fetchall()
        for i in result:
            if i[0] is None:
                source_dm_cursor.execute(f"SELECT * FROM {source_mode}.{table_name}")
            else:
                #print(i[0])
                dm_cursor.execute(f"SELECT max({update_time}) FROM {target_end_mode}.{target_table_name}")
                for k in dm_cursor.fetchall():
                    source_dm_cursor.execute(f"SELECT * FROM {source_mode}.{table_name} WHERE {update_time} >'{k[0]}'")
                    #print(source_dm_cursor.fetchall())
                #dm_cursor.execute(f"SELECT * FROM {source_mode}.{table_name} WHERE {update_time} > (SELECT max({update_time}) FROM {target_end_mode}.{table_name})")
    else:

                dm_cursor.execute(f"delete from {target_end_mode}.{target_table_name}")
                source_dm_cursor.execute(f"SELECT * FROM {source_mode}.{table_name}")





    data=source_dm_cursor.fetchall()
   # print(data)
    # 插入数据到达梦表
    insert_sql = f"INSERT INTO {target_end_mode}.{target_table_name} ({', '.join(dm_columns)}) VALUES ({', '.join([':' + str(i) for i in range(1, len(dm_columns) + 1)])})"
    dm_cursor.executemany(insert_sql, data)
    #dm_cursor.execute(insert_sql, data)
    dm_conn.commit()
    print(f'\033[0:32m{table_note_result}:{table_name}-表数据插入完成,总共:{len(data)}条数据\033[m')

# 关闭连接
dm_cursor.close()
dm_conn.close()
source_dm_cursor.close()
source_dm_conn.close()
end_time = time.time()  # 记录结束时间
execution_time = end_time - start_time  # 计算执行时长
today = date.today()



def convert_seconds(seconds):
    days = seconds // (24 * 3600)
    seconds %= (24 * 3600)
    hours = seconds // 3600
    seconds %= 3600
    minutes = seconds // 60
    seconds %= 60
    return f"{days}天 {hours}小时 {minutes}分钟 {seconds}秒"
print(f"当前日期是：{today},脚本执行时长：{convert_seconds(int(execution_time))}")