python玩转oracle数据库的增删改查

前言
因为有大量的一个数据，或需要数据分析，或需要完成修改查询之类的操作，达到我们预期的模样，这时候如果可以像我们jmeter中，实现CSV遍历的话，那就好办了。python就支持csv文件，我们了解，虽然说Jmeter简单、方便。但是语言才是基础，jmeter也是java语言开发的，那如果有一天jmeter不存在了呢？(打个比方，我更不希望，哈哈哈…)，所以我们不仅要jmeter能够实现我们数据的遍历，也要在python中实现。实力才是硬道理！
好了，进入主题：我实现的代码如下：

import pandas as pd  # 调用 数据处理利器
from class_oracle.oracle_dome import *  # 调用自己封装的数据库方法，可以在我的数据库连接文章中拿

csv = pd.read_csv('data.csv')
df = pd.DataFrame(csv)

def data():
    for item in range(len(df['merCode'])):  # 查csv中某个字段的行数，作为遍历次数
        try:  # 提取不同的商户号
            sql = "select substr('%s',0,3) from dual" % (df['merCode'][item])
            res = DemoOracle().select_one(sql)
            if res[0] == '847':  # 判断不同的数据类型
                sql01 = "select a.mer_code, b.mercode, c.mer_code, m.mer_code from TBL_INVENTORY_MANAGEMENT_posp a left join tbl_fee b on a.mer_code = b.mercode left join TBL_MERCHANT_COLLECT_FEE c on b.mercode = c.mer_code left join swtonlineb.tbl_mer_info m on m.mer_code = a.mer_code where a.machine_state = 1 and a.mer_code is not null and b.mercode is not null and c.mer_code is not null and m.mer_code is not null and a.mer_code='%s' order by a.create_date desc" % (df['merCode'][item])
                res01 = DemoOracle().select_all(sql01)
                if len(res01[0]) >= 4:  # 判断是否有效
                    """修改政策数据: sql02: 查终端号, sql03：修改大POS库存表, sql04：修改费表, sql05：修改无卡快捷费率,
                    sql06：修改商户信息表借记卡费率, sql07：修改成本表, sql08：修改提现费表, sql_pos:03,04,05,07,08
                    """
                    sql02 = "SELECT TERM_ID from TBL_INVENTORY_MANAGEMENT_posp a where MER_CODE='%s'order by a.TERM_ID" % (df['merCode'][item])
                    res02 = DemoOracle().select_all(sql02)  # 终端号：res02[0]
                    sql_pos = ["update TBL_INVENTORY_MANAGEMENT_posp set CASH_BACK_STATE='%s',USER_NAME2='%s',BRANCH='%s',MACHINE_ACTIVATE_DATE='%s',CREATE_DATE='%s' where MER_CODE='%s'" % (df['zhengC'][item], df['user2'][item], df['BRANCH'][item], df['DATE'][item], df['CDate'][item], df['merCode'][item]),
                               "update TBL_FEE set USERNAME2='%s',AMOUNT='%s',DAI_COMMISION_VALUE='%s',BRANCH='%s',CASH_BACK_STATE='%s',MERCHANT_TYPE='%s',CREATE_DATE='%s',VIP_FEE='%s',YUNFU_FEE='%s',WKKJ_TX_VALUE='%s',WKKJ_CREDIT_TX_VALUE='%s',OUT_FEE='%s',OUT_FEE_WSZF='%s',DAI_AMOUNT='%s',QZF_COMMISION_VALUE='%s',qyl_commision_value='%s',qyl_thousand_value='%s'  where MERCODE='%s'" % (df['user2'][item], df['AMOUNT'][item], df['Dai'][item], df['BRANCH'][item], df['zhengC'][item]