获取 oracle 数据

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2018-04-15 09:00:00
# @Author  : Canon
# @Link    : https://www.python.org
# @Version : 3.6.1

"""
oracle 安装: pip install cx_Oracle==6.4.1

在 Python36 的安装路径的 Lib\site-packages 中, 解压 cx_oracle_dll.zip
    百度云链接：https://pan.baidu.com/s/1EyrCregqBof0GaIjnqyRyw 密码：0zbk
在系统环境变量中添加: D:\Python36\Lib\site-packages\cx_oracle_dll

"""

import os
import time
import cx_Oracle as oracle

# connect oracle database develop
db = oracle.connect('test/0@192.168.10.34:1521/ora11g')

# create cursor
cursor = db.cursor()

# execute sql
query_websites = "SELECT MPC_WEBSITE FROM oms_mer_pre_check"
query_id = "SELECT MW_ID FROM oms_mer_website WHERE MW_URL='{}'"
update_id = "UPDATE oms_mer_pre_check SET MPC_WEBSITE='{}' WHERE MPC_WEBSITE='{}'"
query_back_web = "SELECT MW_URL FROM oms_mer_website WHERE MW_ID={}"


# fetch data
# 查询网站
cursor.execute(query_websites)
websites = cursor.fetchall()


def get_web(web_list):
    id_list = []
    for val in web_list:
        try:
            # 查询 MW_ID
            cursor.execute(query_back_web.format(int(val)))
            data = cursor.fetchone()
        except ValueError:
            data = ""
        # 判断为元组类型
        if isinstance(data, tuple):
            web_id = data[0]
            id_list.append(str(web_id))
        else:
            id_list.append(val)
    return ",".join(id_list)


def get_id(web_list):
    """ 获取 MW_ID 拼接 的 MPC_WEBSITE """
    filter_list = []
    id_list = []
    # 相邻组合
    for i in range(len(web_list)):
        for j in range(i+1, len(web_list)+1):
            val_list = web_list[i:j]
            # 过滤已经找到 MW_ID 的 MPC_WEBSITE
            if filter_list not in val_list:
                # 查询 MW_ID
                cursor.execute(query_id.format(",".join(val_list)))
                data = cursor.fetchone()
                # 判断是否为元组类型, 若有数据情况下为元组, 若无数据情况下为 NoneType
                if isinstance(data, tuple):
                    web_id = data[0]
                    id_list.append(str(web_id))
                    filter_list.extend(val_list)
            else:
                break
        # 查询不到 MW_ID, 则保留 MPC_WEBSITE
        if web_list[i] not in filter_list:
            id_list.append(web_list[i])
    return ",".join(id_list)


def adjacent_combination():
    """ 相邻组合, 结果: ['a', 'ab', 'abc', 'abcd', 'b', 'bc', 'bcd', 'c', 'cd', 'd'] """
    test = ['a', 'b', 'c', 'd']
    val = ["".join(test[i:j]) for i in range(len(test)) for j in range(i+1, len(test)+1)]
    print(val)

# 执行更新
res_path = os.path.dirname() + "/result.txt"
with open(res_path, "w", encoding='UTF-8') as file_obj:
    for item in websites:
        web_site = item[0]
        web_list = web_site.split(",")
        id_str = get_web(web_list)
        print(id_str)
        # 输出到结果文件
        file_obj.write("原值:\t" + str(web_list) + "\n")
        file_obj.write("更新值:\t" + id_str + "\n")
        # 更新 MW_ID
        # cursor.execute(update_id.format(id_str, web_site))

# 提交数据
# db.commit()

# close cursor and oracle
cursor.close()
db.close()