-
Notifications
You must be signed in to change notification settings - Fork 0
/
test_xlsb_PT.py
153 lines (134 loc) · 4.23 KB
/
test_xlsb_PT.py
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
import pyxlsb
from pyxlsb import open_workbook
from pyxlsb import convert_date
import re
wb = open_workbook('npi17.xlsb')
nets = {}
params = {}
rows = wb.get_sheet('Norm_KPI').rows()
i = 0
for row in rows:
#print(row)
#print(type(row))
prev_param=''
if i == 0:
for Cell in row:
param=Cell.v
param = param.upper();
param = param.replace(' ', ' ')
param = param.replace(' ', '_')
param = param.replace('__', '_')
param = param.replace('_МГЦ', '')
if param == 'SUMMER':
param=prev_param.replace('_NORM','_SUMMER')
prev_param = ''
elif re.search('(NET|REGION|MONTH|PRIORITY|INFORMATIONAL)', param):
next
else:
param=(param+'_NORM')
prev_param = param
params[Cell.c]=param
#print(str(Cell.c)+param+'-')
# break
else:
ii = 0
norms={}
Net=''
for Cell in row:
#print(str(ii))
if params[ii] == 'MONTH':
Day=convert_date(Cell.v).day
StrDay=''
if Day<10:
StrDay='0'+str(Day)
else:
StrDay=str(Day)
MM = convert_date(Cell.v).month
StrMM = ''
if MM<10:
StrMM='0'+str(MM)
else:
StrMM=str(MM)
Month="TO_DATE('"+StrDay+'.'+StrMM+'.'+str(convert_date(Cell.v).year)+"','DD.MM.YYYY')"
#print(params[ii]+' '+Month)
norms[params[ii]] = Month
else:
norm_value=str(Cell.v)
#if norm_value == 'None':
if re.search('(^None$|^-$)', norm_value):
norm_value=''
elif re.search('\*', norm_value):
norm_value = norm_value.replace('*', '')
norm_value = norm_value.replace(',', '.')
norm_value = float(norm_value)
else:
norm_value=Cell.v
if Net == 'Yoshkar-Ola':# Sochi Yoshkar-Ola
if params[ii] == 'CUNSSR_2G3G_NORM': #INFORMATIONAL CUNSSR_2G3G_NORM
print(norm_value)
norms[params[ii]]=norm_value
if params[ii] == 'NET':
Net=Cell.v
ii +=1
#print(norms)
nets[Net+'_'+Month]=norms
#if Net=='Yoshkar-Ola':
#print(norms)
# norms{}
i += 1
#print(nets)
#for param in params:
# print (param)
##from pyxlsb import convert_date
##print(convert_date(41235.45578))
# datetime.datetime(2012, 11, 22, 10, 56, 19)
sql_list=[]
sql="INSERT INTO NPI.TEST_PY_NORM_NPI ({param_name}) VALUES ({param_value})"
for netmonth in nets:
#print(nets[net])
param_names = ''
param_values = ''
normatives = nets[netmonth]
zpt=''
for param in normatives:
param_names = param_names+zpt+param
if param == 'MONTH':
param_values_sh = "{param_value}"
else:
param_values_sh = "'{param_value}'"
param_values = param_values+zpt+param_values_sh.format(param_value=normatives[param])
zpt = ','
sql_list.append(sql.format(param_name=param_names,param_value=param_values))
#print(sql.format(param_name=param_names,param_value=param_values))
import cx_Oracle
ip = '10.136.12.164'
port = 1521
SID = 'RAN'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
print (dsn_tns)
con = cx_Oracle.connect('NPI', 'NPI', dsn_tns)
#con = cx_Oracle.connect('CC/[email protected]/RAN')
#con = cx_Oracle.connect("CC", "CC", "Violet")
connstr = 'NPI/[email protected]:1521/RAN'
con = cx_Oracle.connect(connstr)
cur = con.cursor()
cur.execute('TRUNCATE TABLE NPI.TEST_PY_NORM_NPI')
con.commit()
g=0
b=0
for sql in sql_list:
#print(sql)
try:
cur.execute(sql)
g+=1
except cx_Oracle.DatabaseError as error:
# Log error as appropriate
print(sql)
print(error)
b+=1
#raise #=break of script
con.commit()
cur.close()
con.close()
allR=g+b
print("RECORDS: "+str(allR)+', SUCCESSFUL: '+str(g)+', FAILED: '+str(b))