python：从oracle同步数据到mysql 

  1 # -*- coding: GBK -*-
  2 
  3 import cx_Oracle
  4 import MySQLdb
  5 import datetime
  6 
  7 ORACAL_HOST = "127.0.0.1"                           # oracle_数据库ip
  8 ORACAL_PORT = 1521                                  # oracle_数据库端口
  9 ORACAL_SID = "orcl"                                 # oracle_数据库名称
 10 ORACAL_USERNAME = "system"                          # oracle_数据库用户名
 11 ORACAL_PASSWORD = "root"                            # oracle_数据库用户密码
 12 ORACAL_TABLE_NAME = "t_oracle"                      # oracle_数据库表名
 13 ORACAL_DB_CHARSET = "gbk"                           # oracle_数据库编码
 14 
 15 MYSQL_HOST = "127.0.0.1"                            # mysql_数据库ip
 16 MYSQL_PORT = 3306                                   # mysql_数据库端口
 17 MYSQL_DB_NAME = "mysql_db"                          # mysql_数据库名称
 18 MYSQL_USERNAME = "root"                             # mysql_数据库用户名
 19 MYSQL_PASSWORD = "root"                             # mysql_数据库用户密码
 20 MYSQL_TABLE_NAME = "t_mysql"                        # mysql_数据库表名
 21 MYSQL_DB_CHARSET = "utf8"                           # mysql_数据库编码
 22 
 23 LOG_FILE_NAME = "SyncLog.txt"                       # 日志文件名称
 24 
 25 data_from_oracle = []                               # 存放从oracle读取的数据
 26 
 27 
 28 # 读取oracle表
 29 def read_from_oracle():
 30     global ORACAL_HOST, ORACAL_PORT, ORACAL_SID, ORACAL_USERNAME, ORACAL_PASSWORD, ORACAL_TABLE_NAME
 31 
 32     dsn = cx_Oracle.makedsn(ORACAL_HOST, ORACAL_PORT, ORACAL_SID)
 33     printLog("Connect to oracle DB start, ORACAL_HOST:" + ORACAL_HOST + ", ORACAL_PORT:" + str(ORACAL_PORT)
 34           + ", ORACAL_SID:" + ORACAL_SID+ ", ORACAL_USERNAME:" + ORACAL_USERNAME )
 35 
 36     conn = cx_Oracle.connect(ORACAL_USERNAME, ORACAL_PASSWORD, dsn)
 37     printLog("Connect to oracle DB success!")
 38 
 39     cursor = conn.cursor()
 40     sql = "select * from " + ORACAL_TABLE_NAME
 41     cursor.execute(sql)
 42 
 43     global data_from_oracle
 44     data_from_oracle = cursor.fetchall()
 45     printLog("Get total " + str(len(data_from_oracle)) + " datas from " + ORACAL_SID + "." + ORACAL_TABLE_NAME)
 46 
 47     conn.close()
 48     printLog("Connect to oracle DB closed.")
 49 
 50 
 51 # 写入mysql表
 52 def write_to_mysql():
 53     global MYSQL_HOST, MYSQL_PORT, MYSQL_DB_NAME, MYSQL_USERNAME, MYSQL_PASSWORD, \
 54            MYSQL_DB_CHARSET, MYSQL_TABLE_NAME, ORACAL_DB_CHARSET
 55 
 56     printLog("Connect to mysql DB start, MYSQL_HOST:" + MYSQL_HOST + ", MYSQL_PORT:" + str(MYSQL_PORT)
 57           + ", MYSQL_DB_NAME:" + MYSQL_DB_NAME + ", MYSQL_USERNAME:" + MYSQL_USERNAME)
 58     conn = MySQLdb.Connect(host=MYSQL_HOST,
 59                            port=MYSQL_PORT,
 60                            user=MYSQL_USERNAME,
 61                            passwd=MYSQL_PASSWORD,
 62                            db=MYSQL_DB_NAME,
 63                            charset=MYSQL_DB_CHARSET)
 64     printLog("Connect to mysql DB success!")
 65 
 66     cursor = conn.cursor()
 67     sql = 'truncate table ' + MYSQL_TABLE_NAME
 68     cursor.execute(sql)
 69     printLog("Truncate table " + MYSQL_TABLE_NAME + " success!")
 70 
 71     sql = get_insert_sql()
 72     if sql is not None:
 73         cursor.execute(sql)
 74         conn.commit()
 75         effectRow = cursor.rowcount
 76         printLog("Write data to table " + MYSQL_TABLE_NAME + " success! Effect row:" + str(effectRow))
 77     else:
 78         printLog("Total length of oracle data is zero, no insert!")
 79     conn.close()
 80     printLog("Connect to mysql DB closed.")
 81 
 82 
 83 # 获取插入mysql表的sql语句
 84 def get_insert_sql():
 85     global data_from_oracle,MYSQL_TABLE_NAME 
 86     if len(data_from_oracle) == 0:
 87         return None
 88     sql = "INSERT INTO `" + MYSQL_TABLE_NAME + "` (name, age, sex) VALUES "
 89     for item in data_from_oracle:
 90         sql += "('" + item[0].strip() + "'," + item[1] + ",'" + item[2].strip() "'),"
 91     return sql[:-1]
 92 
 93 
 94 
 95 # 打印日志
 96 def printLog(content):
 97     global LOG_FILE_NAME
 98     f = open(LOG_FILE_NAME, 'a')
 99     time = datetime.datetime.now()
100     timestr = time.strftime('%Y-%m-%d %H:%M:%S.%f')
101     f.write(timestr + ' :')
102     f.write(content + "\n")
103     f.close()
104 
105 
106 # 退出程序
107 def exit_program():
108     printLog("Program exit.\n")
109     exit()
110 
111 
112 # 主程序
113 def main():
114     try:
115         read_from_oracle()
116         write_to_mysql()
117     except Exception as e:
118         printLog("Error occurred!")
119         global ORACAL_DB_CHARSET
120         printLog(str(e).decode(ORACAL_DB_CHARSET).encode('utf-8'))
121         exit_program()
122     exit_program()
123 
124 
125 main()

 

posted @ 2020-12-22 18:56  洛洛沙  阅读(1510)  评论(1)    收藏  举报