Python读取文件解析入库

# -*- coding: utf-8 -*-
# @Author: 风过无言花易落
# @Date  : 2021/05/27 13:00
# @Desc  : 将文件解析入数据库
import os,sys
import time
import re
# from clickhouse_driver import Client
import cx_Oracle
path = os.getcwd()
parent_path = os.path.dirname(path)
sys.path.append(parent_path+'\Data_DB')
from OraclePool import OraclePool

path = os.getcwd()
parent_path = os.path.dirname(path)
sys.path.append(parent_path)

#ip_port_servername = ("db","db","123.123.123.123:1530/DB") # 数据库实例

oracle_pool = OraclePool((ip_port_servername), thread=True) 

def clickhouse(sql):
    client = Client(host='123.123.123.123',port='19002',database='iot_test',user='test_rw' ,password='QeAP')
    ans = client.execute(sql)
    return ans

def GetDatas_QueryDB(db,sql):
    dbinfo = {
        'YH': ('new', 'new', '123.123.12.123:1530/DB'),
        'API': ('db', 'az', '123.123.13.123:1530/DB')
    }
    usename, password, ip_port_servername = dbinfo[db]
    connection = cx_Oracle.connect(usename, password, ip_port_servername)
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        return result
    except Exception as e:
        print(e)
    finally:
        connection.close()

def with_open(file_name,local_path_download = None):
    '''
    fr.read()  # 读取所有内容
    fr.readline()  # 读取一行
    fr.readlines()  # 读取所有文件内容，返回一个list
    # 以上三个命令在大文件时慎用，会把内容读到内存中，占用大内存
    fr.seek(0)  # 当前文件指针位置在0位
    fr.writelines(["a", "b"])  # 把列表写入文件
    :return:
    '''
    load_get = '/app/application_iot/iotua/SJ/'
    if local_path_download == None:
        local_path_download = load_get+file_name
    else:
        pass
    try:
        allline = open(local_path_download)
        dic_dat_list = []
        list_dat = []
        while True:
            Restructure_vopi =  allline.readline().replace('\n','')
            if Restructure_vopi == '':
                break
            Restructure_vopi = Restructure_vopi.split(',')
            dic_dat = iot_dat_analysis(Restructure_vopi)
            # dic_dat = iot_dat_analysis(','.join(Restructure_vopi))
            # print (dic_dat)
            dic_dat_list.append(dic_dat)
            list_dat.append(tuple(Restructure_vopi))
            # print(dic_dat_list)
        return dic_dat_list,list_dat
    except IOError as e:
        print('读取文件失败',e)
    except Exception as e:
        print('未找到该文件',e)
    finally:
        allline.close()

def iot_dat_analysis(Original):
    FIELD_NAME = ['BZ_TYPE', 'FI_ID', 'MSISDN', 'IMSI', 'ICCID', 'IMEI', 'APN', 'SGSN', 'GGSN', 'PDP_ADDR', 'LAC',
                  'CELL_ID', 'PROV_CODE', 'CITY_CODE', 'VISIT_PROVIDER_ID', 'BEGIN_DATE', 'BEGIN_TIME', 'DATA_TOTAL',
                  'DURATION', 'RG_ID', 'RAT_TYPE', 'DISTRICT_NAME', 'RESERVE2', 'RESERVE3', 'RESERVE4', 'RESERVE5',
                  'RESERVE6', 'RESERVE7', 'RESERVE8', 'RESERVE9', 'RESERVE10', 'RESERVE11', 'RESERVE12', 'RESERVE13',
                  'RESERVE14', 'RESERVE15']
    dict = {}
    for i in range(len(FIELD_NAME)):
        dict[FIELD_NAME[i]] = Original[i]
    return dict
def dat_contrast(file_name):
    dic_dat_list = with_open(file_name)
    print(dic_dat_list)
    dict01 = {}
    for dict in dic_dat_list:
        list = []
        RAT_TYPE = dict['RAT_TYPE'] # 2G 3G 4G NB
        LAC = dict['LAC']
        CELL_ID = dict['CELL_ID']
        PROV_CODE = dict['PROV_CODE']
        CITY_CODE = dict['CITY_CODE']
        DISTRICT_NAME = dict['DISTRICT_NAME']
        list.append(RAT_TYPE,LAC,CELL_ID,PROV_CODE,CITY_CODE,DISTRICT_NAME)
        dict01[file_name] = list
    print(dict01)

def dat_put_oracle(file_name):
    dic_dat_list, list_dat = with_open(file_name)
    # print(list_dat)
    sql = ''' INSERT INTO db.mvno_dat_record(BZ_TYPE, FI_ID, MSISDN, IMSI, ICCID, IMEI, APN, SGSN, GGSN, PDP_ADDR, LAC, CELL_ID,
                                 PROV_CODE, CITY_CODE, VISIT_PROVIDER_ID, BEGIN_DATE, BEGIN_TIME, DATA_TOTAL, DURATION,
                                 RG_ID, RAT_TYPE, DISTRICT_NAME, RESERVE2, RESERVE3, RESERVE4, RESERVE5, RESERVE6,
                                 RESERVE7, RESERVE8, RESERVE9, RESERVE10, RESERVE11, RESERVE12, RESERVE13, RESERVE14,
                                 RESERVE15)
    VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
    :26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36) '''
    print(sql)
    print(list_dat)
    print(oracle_pool.executemany_sql(sql, list_dat))



if __name__ == '__main__':
    while True:
        for root, dirs, files in os.walk('/app/SJ/'):
        #print(root)  # 当前目录路径
        #print(dirs)  # 当前路径下所有子目录
        #print(files)  # 当前路径下所有非目录子文件
            for i in files:
                dat_put_oracle(i)
                os.system('mv /app/SJ/{i} /app/bak/'.format(i = i))
        print('未检测到文件,睡眠10秒')
        time.sleep(10)