-
Notifications
You must be signed in to change notification settings - Fork 0
/
readandloadaumblock-retire01.py
151 lines (117 loc) · 6.49 KB
/
readandloadaumblock-retire01.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
import ftplib
class perform:
#def __init__(self):
# print 'class initialized...'
# #self.DataFilePathName = []
# #self.BuildFilepaths()
def __init__(self,p_datafilepathname):
print 'initialized readandloadperfaumblock.py'
self.DataFilePathName = p_datafilepathname
self.ReadAndLoad()
print 'exiting readandloadperfaumblock.py'
def set_DataFilePathName(self,DataFilePathName):
self._DataFilePathName = DataFilePathName
def get_DataFilePathName(self):
return self._DataFilePathName
DataFilePathName = property(get_DataFilePathName, set_DataFilePathName)
def set_Results(self,Results):
self._Results = Results
def get_Results(self):
return self._Results
Results = property(get_Results, set_Results)
def xstr(self,s):
try:
return '' if s is None else str(s)
except:
return ''
def ReadAndLoad(self):
procresults = {}
try:
my_datafilepathname = self.DataFilePathName #r"//Ipc-vsql01/data/Batches/prod/WatchFolder/incoming/PagesOutput_GetPadPortBenchAsOf_20161124_ADAKAT.xls"
# get and format the modified date
import os.path, time
print 'got here !', my_datafilepathname
filedatetime = os.path.getmtime(my_datafilepathname)
from datetime import datetime
filedatetime_forsql = datetime.fromtimestamp(filedatetime).strftime('%Y-%m-%d %H:%M:%S')
import bs4, sys
with open(my_datafilepathname, 'r') as f:
webpage = f.read().decode('utf-8')
soup = bs4.BeautifulSoup(webpage, "lxml")
MasterPortfolioStateCode = ''
fieldnames = {}
is_dataline = 0
total_deleted = 0
total_inserted = 0
print '-- headers --'
for node in soup.find_all('th', attrs={}): #'style':'display: table-header-group; mso-number-format:\@;'
if node.attrs['class'][0] in ['HeaderCellNumeric','HeaderCellString']:
fieldnames[len(fieldnames)] = node.string
print len(fieldnames),node.string
for nodeA in soup.find_all('tr', attrs={}):
print '-----------------------'
is_dataline = 0
fieldvalues = {}
for nodeB in nodeA.find_all('td', attrs={}):
#print 'got here!!'
#print nodeB.attrs['class'][0]
if nodeB.attrs['class'][0] in ['DataCellNumeric','DataCellString']:
#print 'got here!!!'
if fieldnames[len(fieldvalues)] == 'MasterPortfolioStateCode':
#print 'got here!!!!'
is_dataline = 1
MasterPortfolioStateCode = nodeB.string
if nodeB.string == None:
fieldvalueasstring = ''
else:
fieldvalueasstring = nodeB.string
#fieldvalueasstring = self.xstr(nodeB.string)
#print 'fieldvalueasstring=',fieldvalueasstring
fieldvalues[fieldnames[len(fieldvalues)]] = fieldvalueasstring.replace("'", "''").rstrip()
if is_dataline == 1:
#print 'got here !@'
fieldnames_string = ''
fieldvalues_string = ''
for k,v in fieldvalues.items():
fieldnames_string = fieldnames_string + k + ','
fieldvalues_string = fieldvalues_string + "'" + self.xstr(v) + "',"
fieldnames_string = fieldnames_string[:-1]
fieldvalues_string = fieldvalues_string[:-1]
#print 'fieldnames_string....................'
#print fieldnames_string
#fieldnames_string = fieldnames_string + 'last_update'
#fieldvalues_string = fieldvalues_string + "'" + filedatetime_forsql + "'"
#print fieldnames_string
#print fieldvalues_string
#print MasterPortfolioStateCode
#print fieldvalues[fieldnames[0]],fieldvalues[fieldnames[1]],fieldvalues[fieldnames[2]]
import pyodbc
cnxn = pyodbc.connect(r'DRIVER={SQL Server};SERVER=ipc-vsql01;DATABASE=DataAgg;Trusted_Connection=True;')
cursor = cnxn.cursor()
print 'got here !!!!!', fieldvalues['AsOfDate']
#sql_delete = "delete from dbo.xanalysisofbenchmarks_aumblock_imported where AsOfDate = ?", fieldvalues['AsOfDate']
#print sql_delete
if total_inserted == 0:
cursor.execute("delete from dbo.xanalysisofbenchmarks_aumblock_imported where AsOfDate = ?", fieldvalues['AsOfDate'])
total_deleted = total_deleted + cursor.rowcount
print ' ',cursor.rowcount, 'records deleted'
cnxn.commit()
insert_sql = "insert into xanalysisofbenchmarks_aumblock_imported("+fieldnames_string+") values ("+fieldvalues_string+")"
#print insert_sql
cursor.execute(insert_sql)
procresults['records inserted'] = cursor.rowcount
total_inserted = total_inserted + cursor.rowcount
print ' ',cursor.rowcount, 'records inserted'
cnxn.commit()
procresults['resultvalue1'] = 'success'
procresults['total_deleted'] = total_deleted
procresults['total_inserted'] = total_inserted
except Exception,e:
print type(e)
print 'there was an error on ' + self.DataFilePathName
self.Results = procresults
if __name__=='__main__':
print 'running ___name___'
myDataFilePathName = r"//Ipc-vsql01/data/Batches/prod/WatchFolder/incoming/PagesOutput_GetAUMBlock_2016-12-19 164553767.xls"
o = perform(myDataFilePathName)
print o.Results