Python连接Oracle查询表/关联表插入

查询oracle数据库中其中一张表内容在python上

#查询表内容
import cx_Oracle as oracle

def visitOracle(sql):
    conn = oracle.connect('数据库用户','密码','ip:port/实例')
    cursor = conn.cursor()
    cursor.execute(sql)
    # 读取字段列名
    index = cursor.description
    row = list()
    for i in range(len(index)):
        row.append(index[i][0])
    # 获取返回信息
    data = cursor.fetchall()
    result = pd.DataFrame(list(data), columns = row)
    #关闭连接，释放资源
    cursor.close()
    conn.close()
    return result
    
sql = "select * from table"
result = visitOracle(sql)

关联三种表中的内容，把需要的信息与模型输出信息插入新表

#关联表内容与模型的输出，插入新表
conn = oracle.connect('数据库用户','密码','ip:port/实例')
cursor = conn.cursor()

sql_insert = sql = ("insert into 数据库用户.被插入表名1(A,B,C,D,E,F,G,H,I,J,K) "
                                    "select sys_guid() as A,                   "
                                    "       IMB.B_1 AS B, "
                                    "       IMB.C, "
                                    "       PF.D, "
                                    "       sysdate AS E," #sysdate是oracle中时间函数
                                    "       '%s' AS F, "
                                    "       IMB.G , "	
                                    "       IMB.H, "
                                    "       IMB.I, "
                                    "       IMB.J, "
                                    "       IMB.K_1 as K "
                                    " from 数据库用户.表名2 IM,数据库用户.表名3 IMA,数据库用户.表名4 PF"
                                    " where IMB.B = IMA.B and IMA.C = PF.C" #判断表关联的字段相同的
                                    " where PF.C = '%s' " %(dicts,key) #判断插入的两条内容，与上表关联中相同的关联字段，进行插入
                                    )
cursor.execute(sql_insert)

#end
conn.commit("commit")

#关闭连接，释放资源
cursor.close()
conn.close()