准备工作：
①下载cx_Oracle、pandas、numpy包；
pip install cx_Oracle
pip install pandas
pip install numpy②已知两库的连接信息；
库1的主要为读数据表，并将表内容进行条件转换
sql = ('SELECT NAME, SEX, AGE, PROVINCE, RANK, INCOME, BELONG_PROJECT, REPORT_TIME, ''APPROVER, APPROVAL_STATUS, SERVICE_RATING FROM REIMBURSEMENT_FIFTY_THOUSAND WHERE ROWNUM <= 10')conn_govern_dev = cx_Oracle.connect(数据库1的连接信息)
cursor_govern_dev = conn_govern_dev.cursor()# 读取govern_dev库的表
df = pd.read_sql(sql, conn_govern_dev)# 性别为男切换为boy，女切换为girl
def myfunc_sex(x):if x == '男':return 'boy'elif x == '女':return 'girl'else:return 'maybe'df['SEX'] = df['SEX'].map(myfunc_sex)# 对工资列，低于1万的乘以3，加一个值，高于1万的除以2，加另外一个值
def myfunc_income(x, *args, **args_dict):if x < 10000:return x * 3 + args_dict['high']else:return x / 2 + args_dict['low']df['INCOME'] = df['INCOME'].apply(myfunc_income, low=0.2, high=0.1)cursor_govern_dev.close()
conn_govern_dev.close()库2的操作就是将库1的表进行写入
conn_test = cx_Oracle.connect(数据库2的连接信息)df_array = np.array(df) # dataframe转换array
df_list = df_array.tolist() # array转换list
# print(df_list)
sql = ('insert into REIMBURSEMENT_FIFTY_THOUSAND(NAME, SEX, AGE, PROVINCE, RANK, ''INCOME, BELONG_PROJECT, REPORT_TIME, APPROVER, APPROVAL_STATUS, SERVICE_RATING) ''values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)')
cur = conn_test.cursor()
# 写入test库的表
cur.executemany(sql, df_list)
cur.close()
conn_test.commit()
conn_test.close()完整代码
# -*- coding: utf8 -*-
import cx_Oracle
import pandas as pd
import numpy as npsql = ('SELECT NAME, SEX, AGE, PROVINCE, RANK, INCOME, BELONG_PROJECT, REPORT_TIME, ''APPROVER, APPROVAL_STATUS, SERVICE_RATING FROM REIMBURSEMENT_FIFTY_THOUSAND WHERE ROWNUM <= 10')conn_govern_dev = cx_Oracle.connect(库1的链接信息)
cursor_govern_dev = conn_govern_dev.cursor()# 读取govern_dev库的表
df = pd.read_sql(sql, conn_govern_dev)# 性别为男切换为boy，女切换为girl
def myfunc_sex(x):if x == '男':return 'boy'elif x == '女':return 'girl'else:return 'maybe'df['SEX'] = df['SEX'].map(myfunc_sex)# 对工资列，低于1万的乘以3，加一个值，高于1万的除以2，加另外一个值
def myfunc_income(x, *args, **args_dict):if x < 10000:return x * 3 + args_dict['high']else:return x / 2 + args_dict['low']df['INCOME'] = df['INCOME'].apply(myfunc_income, low=0.2, high=0.1)cursor_govern_dev.close()
conn_govern_dev.close()conn_test = cx_Oracle.connect(库2的链接信息)df_array = np.array(df) # dataframe转换array
df_list = df_array.tolist() # array转换list
# print(df_list)
sql = ('insert into REIMBURSEMENT_FIFTY_THOUSAND(NAME, SEX, AGE, PROVINCE, RANK, ''INCOME, BELONG_PROJECT, REPORT_TIME, APPROVER, APPROVAL_STATUS, SERVICE_RATING) ''values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)')
cur = conn_test.cursor()
# 写入test库的表
cur.executemany(sql, df_list)
cur.close()
conn_test.commit()
conn_test.close()
