TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
楼主 |
发表于 2024-1-12 22:45:45
|
显示全部楼层
- import cx_Oracle
- import logging
- import time
- # 配置日志记录器
- logger = logging.getLogger(__name__)
- logger.setLevel(logging.INFO)
- handler = logging.FileHandler("database.log")
- handler.setLevel(logging.INFO)
- formatter = logging.Formatter("%(asctime)s - %(levelname)s - %(message)s")
- handler.setFormatter(formatter)
- logger.addHandler(handler)
- # 从rep库获取数据库连接信息
- target_conn = cx_Oracle.connect("zhyu/zhyu@192.168.56.101:1521/ora19")
- target_cursor = target_conn.cursor()
- dbl_query = "SELECT IP_ADDRESS, LISTENER_PORT, SERVICE_NAME, USERNAME, PASSWORD, DB_UUID FROM godba_add_db"
- target_cursor.execute(dbl_query)
- # 遍历连接信息,从源库查询信息并存放到rep库
- for IP_ADDRESS, LISTENER_PORT, SERVICE_NAME, USERNAME, PASSWORD, DB_UUID in target_cursor.fetchall():
- retry_count = 0
- while retry_count < 3:
- try:
- logger.info(f"Connecting to {IP_ADDRESS}:{LISTENER_PORT}/{SERVICE_NAME}...")
- source_conn = cx_Oracle.connect(
- f"{USERNAME}/{PASSWORD}@{IP_ADDRESS}:{LISTENER_PORT}/{SERVICE_NAME}")
- source_cursor = source_conn.cursor()
- source_cursor.execute(
- "SELECT username,user_id,password,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,local_temp_tablespace,created,profile,INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME,PASSWORD_VERSIONS,EDITIONS_ENABLED,AUTHENTICATION_TYPE,PROXY_ONLY_CONNECT,COMMON,LAST_LOGIN,ORACLE_MAINTAINED,INHERITED,DEFAULT_COLLATION,IMPLICIT,ALL_SHARD,PASSWORD_CHANGE_DATE,to_char(sysdate,'yyyymmddhh24miss') check_time FROM dba_users")
- for result in source_cursor.fetchall():
- target_cursor.execute("INSERT INTO godba_users (username,user_id,password,account_status,lock_date,expiry_date,default_tablespace,temporary_tablespace,local_temp_tablespace,created,profile,INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME,PASSWORD_VERSIONS,EDITIONS_ENABLED,AUTHENTICATION_TYPE,PROXY_ONLY_CONNECT,COMMON,LAST_LOGIN,ORACLE_MAINTAINED,INHERITED,DEFAULT_COLLATION,IMPLICIT,ALL_SHARD,PASSWORD_CHANGE_DATE,check_time,db_uuid) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27)",
- result + (DB_UUID,))
- logger.info(f"Successfully processed {IP_ADDRESS}:{LISTENER_PORT}/{SERVICE_NAME}.")
- break
- except cx_Oracle.DatabaseError as e:
- logger.error(f"Unable to connect to {IP_ADDRESS}:{LISTENER_PORT}/{SERVICE_NAME}. Retry count: {retry_count+1}", exc_info=True)
- if retry_count == 2:
- logger.error(f"Connection failed after 3 retries. Skipping to the next database.")
- else:
- retry_count += 1
- time.sleep(5) # Wait for 5 seconds before retrying
- # 关闭游标,关闭连接
- source_cursor.close()
- source_conn.close()
- target_conn.commit()
- target_cursor.close()
- target_conn.close()
复制代码
在上述代码中,我们使用了try-except块来捕获cx_Oracle.DatabaseError异常,该异常表示数据库连接失败。如果连接失败,将打印错误消息,并在重试次数小于3时进行重试。如果连续尝试3次都无法连接成功,则跳过当前数据库,继续下一个数据库的处理。
请注意,我在连接失败后添加了一个5秒的延迟,以避免频繁尝试连接导致过多的资源消耗。你可以根据实际情况调整这个延迟时间。
另外,我还将提交事务的操作移动到了循环外部,在处理完所有数据库后进行提交,以提高效率。
|
|