oracle数据库到达梦数据库dm8通过时间字段增量更新

import jaydebeapi
import time
from datetime import date
print('\033[0:32moracle数据库批量增量更新到达dm梦数据库全部表同步脚本开始执行\033[m')
start_time = time.time()  # 记录开始时间
# 设置Oracle连接信息
jdbc_driver = "oracle.jdbc.driver.OracleDriver"
jdbc_url = "jdbc:oracle:thin:@IP:port:orcl"
jdbc_user = "账号"
jdbc_password = "密码"
jarFiles = ['D:\software\dbeaver\JAVA_Mybatis\lib\ojdbc8-23.3.0.23.09.jar','D:\work\libjar\DmJdbcDriver18.jar']
# 连接到Oracle数据库
oracle_conn = jaydebeapi.connect(jdbc_driver, jdbc_url, [jdbc_user, jdbc_password],jarFiles)
oracle_cursor = oracle_conn.cursor()

#设置连接dm数据库
dm_url = 'jdbc:dm://ip:端口/模式名称'
dm_user = '账号'
dm_password = '密码'
dm_dirver = 'dm.jdbc.driver.DmDriver'

dm_conn =jaydebeapi.connect(dm_dirver, dm_url, [dm_user, dm_password], jarFiles)
# 连接到dm数据库
dm_cursor = dm_conn.cursor()
target_end_mode='目标库模式名称'
#获取数据库所有表名
oracle_model='源库模式名称'
oracle_cursor.execute(f"SELECT table_name FROM all_tables f where f.owner='{oracle_model}'")
tables = oracle_cursor.fetchall()

#获取表注释
oracle_cursor.execute(f"SELECT table_name, comments FROM all_tab_comments t where t.owner='{oracle_model}'")
table_chinses=oracle_cursor.fetchall()

#遍历所有表，增量更新到达梦
for table in tables:
    #print(table[0])
    table_name = table[0]
    #注意
    target_table_name=table[0].upper()
    #target_table_name=table[0].upper()+'_NS_QFY'
    print(target_table_name)
    # 获取表结构
    oracle_cursor.execute(f"SELECT * FROM {oracle_model}.{table_name} WHERE 1=0")
    dm_columns = [column[0].upper() for column in oracle_cursor.description]

    for table_note in table_chinses:
        if table_note[0] == table[0]:
            table_note_result = table_note[1]
            #print(table_note_result)
    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:
                oracle_cursor.execute(f"SELECT * FROM {oracle_model}.{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():
                    oracle_cursor.execute(f"SELECT * FROM {oracle_model}.{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}")
                oracle_cursor.execute(f"SELECT * FROM {oracle_model}.{table_name}")

    data = oracle_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, list(data))
    dm_conn.commit()
    print(f'\033[0:32m{table_note_result}:{table_name}-表数据插入完成,总共:{len(data)}条数据\033[m')



# 关闭连接
oracle_cursor.close()
oracle_conn.close()
dm_cursor.close()
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))}")