oracle数组读取,获取 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()