RealTimeDataAccumulator.py 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461
  1. # # Data accumulator
  2. #
  3. # 참고 : https://signing.tistory.com/22
  4. # 공공데이터포털 : https://www.data.go.kr/
  5. # 본 파일은 동네예보 조회서비스 중 동네예보조회에 대한 내용임
  6. # 공공데이터포털에서 제공하는 동네예보 조회서비스 API는 최근 1일까지의 데이터만 제공하고 있음
  7. from urllib.request import urlopen
  8. from urllib.parse import urlencode, unquote, quote_plus
  9. from datetime import datetime, timedelta
  10. import urllib
  11. import requests
  12. import json
  13. import pandas as pd
  14. import pymssql
  15. import configparser
  16. import time
  17. import numpy as np
  18. def get_WF_Temperature_Humidity_info(data):
  19. HumWF=[]
  20. TempWF=[]
  21. try:
  22. weather_info = data['response']['body']['items']['item']
  23. for i in range(len(weather_info)):
  24. if weather_info[i]['category'] == 'T3H':
  25. TempWF.append([weather_info[i]['baseDate'], weather_info[i]['baseTime'], weather_info[i]['fcstDate'], weather_info[i]['fcstTime'],weather_info[i]['fcstValue']])
  26. elif weather_info[i]['category'] == 'REH':
  27. HumWF.append([weather_info[i]['baseDate'], weather_info[i]['baseTime'], weather_info[i]['fcstDate'], weather_info[i]['fcstTime'],weather_info[i]['fcstValue']])
  28. return TempWF, HumWF
  29. except KeyError:
  30. print('API 호출 실패! (Weather forecast data)')
  31. def get_base_time(hour):
  32. hour = int(hour)
  33. if hour < 3:
  34. temp_hour = '20'
  35. elif hour < 6:
  36. temp_hour = '23'
  37. elif hour < 9:
  38. temp_hour = '02'
  39. elif hour < 12:
  40. temp_hour = '05'
  41. elif hour < 15:
  42. temp_hour = '08'
  43. elif hour < 18:
  44. temp_hour = '11'
  45. elif hour < 20:
  46. temp_hour = '14'
  47. elif hour < 24:
  48. temp_hour = '17'
  49. return temp_hour + '00'
  50. def get_weather_forecast(n_x, n_y):
  51. now = datetime.now()
  52. now_date = now.strftime('%Y%m%d')
  53. now_hour = int(now.strftime('%H'))
  54. if now_hour < 6:
  55. base_date = str(int(now_date) - 1)
  56. else:
  57. base_date = now_date
  58. base_hour = get_base_time(now_hour)
  59. num_of_rows = '90'
  60. base_date = base_date
  61. base_time = base_hour
  62. # base_date = '20200622'
  63. # base_time = '1700'
  64. # 해당 지역에 맞는 죄표 입력
  65. # Setting for URL parsing
  66. CallBackURL = 'http://apis.data.go.kr/1360000/VilageFcstInfoService/getVilageFcst' # 맨 마지막 명칭에 따라 상세기능에 대한 정보가 변경될 수 있음
  67. # getUltraSrtNcst: 초단기실황조회, getUltraSrtFcst: 초단기예보조회, getVilageFcst: 동네예보조회, getFcstVersion: 예보버전조회
  68. params = '?' + urlencode({
  69. quote_plus("serviceKey"): "sOVGUogWTbCCmzCn10iCEI0pb9VqKHfiBv8PKYnxJLdz4n63U2uSO5Y2TDjS3lez%2BMNT1TVaH4sCkgsctj2xVg%3D%3D", # 인증키 (2년마다 갱신 필요) # 반드시 본인이 신청한 인증키를 입력해야함 (IP 불일치로 인한 오류 발생 가능)
  70. quote_plus("numOfRows"): num_of_rows, # 한 페이지 결과 수 // default : 10
  71. quote_plus("pageNo"): "1", # 페이지 번호 // default : 1
  72. quote_plus("dataType"): "JSON", # 응답자료형식 : XML, JSON
  73. quote_plus("base_date"): base_date, # 발표일자 // yyyymmdd
  74. quote_plus("base_time"): base_time, # 발표시각 // HHMM, 매 시각 40분 이후 호출
  75. quote_plus("nx"): n_x, # 예보지점 X 좌표
  76. quote_plus("ny"): n_y # 예보지점 Y 좌표
  77. })
  78. # URL parsing
  79. req = urllib.request.Request(CallBackURL + unquote(params))
  80. # Get Data from API
  81. response_body = urlopen(req).read() # get bytes data
  82. # Convert bytes to json
  83. json_data = json.loads(response_body)
  84. # Every result
  85. res = pd.DataFrame(json_data['response']['body']['items']['item'])
  86. print('\n============================== Result ==============================')
  87. print(res)
  88. print('=====================================================================\n')
  89. TemperatureWF, HumidityWF = get_WF_Temperature_Humidity_info(json_data)
  90. return TemperatureWF, HumidityWF
  91. def get_Temperature_Humidity_info(data, len):
  92. temperature=[]
  93. humidity=[]
  94. try:
  95. weather_info = data['response']['body']['items']['item']
  96. for i in range(int(len)):
  97. temperature.append(weather_info[i]['ta'])
  98. humidity.append(weather_info[i]['hm'])
  99. return temperature, humidity
  100. except KeyError:
  101. print('API 호출 실패! (Actual weather data)')
  102. def get_weather(start_day, end_day):
  103. if end_day.hour > start_day.hour:
  104. hour_term = (end_day - start_day).seconds/3600
  105. day_term = (end_day - start_day).days
  106. UnkonwDataLen = day_term*24 + hour_term
  107. else:
  108. hour_term = (start_day - end_day).seconds/3600
  109. day_term = (end_day - start_day).days +1
  110. UnkonwDataLen = day_term*24 - hour_term
  111. sYear = str(start_day.year)
  112. if start_day.month < 10:
  113. sMonth = "0" + str(start_day.month)
  114. else:
  115. sMonth=str(start_day.month)
  116. if start_day.day < 10:
  117. sDay = "0" + str(start_day.day)
  118. else:
  119. sDay = str(start_day.day)
  120. start_date = sYear + sMonth + sDay
  121. if start_day.hour < 10:
  122. sTime = "0" + str(start_day.hour)
  123. else:
  124. sTime = str(start_day.hour)
  125. start_time = sTime
  126. eYear = str(end_day.year)
  127. if end_day.month < 10:
  128. eMonth = "0" + str(end_day.month)
  129. else:
  130. eMonth=str(end_day.month)
  131. if end_day.day < 10:
  132. eDay = "0" + str(end_day.day)
  133. else:
  134. eDay = str(end_day.day)
  135. end_date = eYear + eMonth + eDay
  136. if end_day.hour < 10:
  137. eTime = "0" + str(end_day.hour)
  138. else:
  139. eTime = str(end_day.hour)
  140. end_time = eTime
  141. # Setting for URL parsing
  142. CallBackURL = 'http://apis.data.go.kr/1360000/AsosHourlyInfoService/getWthrDataList' # 맨 마지막 명칭에 따라 상세기능에 대한 정보가 변경될 수 있음
  143. # parameter for request
  144. params = '?' + urlencode({
  145. quote_plus("serviceKey"): "sOVGUogWTbCCmzCn10iCEI0pb9VqKHfiBv8PKYnxJLdz4n63U2uSO5Y2TDjS3lez%2BMNT1TVaH4sCkgsctj2xVg%3D%3D", # 인증키 # 반드시 본인이 신청한 인증키를 입력해야함 (IP 불일치로 인한 오류 발생 가능)
  146. quote_plus("numOfRows"): str(int(UnkonwDataLen)), # 한 페이지 결과 수 // default : 10
  147. quote_plus("pageNo"): "1", # 페이지 번호 // default : 1
  148. quote_plus("dataType"): "JSON", # 응답자료형식 : XML, JSON
  149. quote_plus("dataCd"): "ASOS",
  150. quote_plus("dateCd"): "HR", # 날짜 분류 코드: DAY, HR
  151. quote_plus("startDt"): start_date, # 시작일 // yyyymmdd
  152. quote_plus("startHh"): start_time, # 시작시 // HH
  153. quote_plus("endDt"): end_date, # 종료일 // yyyymmdd
  154. quote_plus("endHh"): end_time, # 종료시 // HH
  155. quote_plus("stnIds"): "143", # 지점번호 대구: 143
  156. quote_plus("schListCnt"): "10"
  157. })
  158. # URL parsing
  159. req = urllib.request.Request(CallBackURL + unquote(params))
  160. print('result length : ', UnkonwDataLen)
  161. # Get Data from API
  162. response_body = urlopen(req).read() # get bytes data
  163. # Convert bytes to json
  164. json_data = json.loads(response_body)
  165. # Every result
  166. res = pd.DataFrame(json_data['response']['body']['items']['item'])
  167. print('\n============================== Result ==============================')
  168. print(res)
  169. print('=====================================================================\n')
  170. Temperature, Humidity = get_Temperature_Humidity_info(json_data, UnkonwDataLen)
  171. return Temperature, Humidity
  172. def Check_Restoring_Unknown_past_data(targetDB_IP, targetDB_UserID, targetDB_UserPW, targetDB_Name, n_x, n_y):
  173. # MSSQL Access
  174. conn = pymssql.connect(host = targetDB_IP, user = targetDB_UserID, password = targetDB_UserPW, database = targetDB_Name, autocommit=True)
  175. # Create Cursor from Connection
  176. cursor = conn.cursor()
  177. now = datetime.now()
  178. InitialForecastDayforCheck = now - timedelta(days=30) # 최대 약 1개월 전까지 데이터 확인 (데이터 요청은 한번에 최대 1000건을 넘길 수 없음)
  179. # API가 전날 데이터까지 제공함 (오늘 예보데이터가 없다면 다음날 채워야 함)
  180. FinalForecastDayforCheck = now
  181. ## Temperature 가져오기
  182. cursor.execute("SELECT * FROM "+targetDBName+".dbo.BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category="+"'"+"Temperature"+"'"+" and ForecastedDateTime >= "+"'"+str(InitialForecastDayforCheck.year)+"-"+str(InitialForecastDayforCheck.month)+"-"+str(InitialForecastDayforCheck.day)+"'"+"and ForecastedDateTime < "+"'"+str(FinalForecastDayforCheck.year)+"-"+str(FinalForecastDayforCheck.month)+"-"+str(FinalForecastDayforCheck.day)+"' order by ForecastedDateTime asc")
  183. # 데이타 하나씩 Fetch하여 출력
  184. row = cursor.fetchone()
  185. TemperatureRawData = [row]
  186. while row:
  187. row = cursor.fetchone()
  188. if row == None:
  189. break
  190. TemperatureRawData.append(row)
  191. ## Humidity 가져오기
  192. cursor.execute("SELECT * FROM "+targetDBName+".dbo.BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category="+"'"+"Humidity"+"'"+" and ForecastedDateTime >= "+"'"+str(InitialForecastDayforCheck.year)+"-"+str(InitialForecastDayforCheck.month)+"-"+str(InitialForecastDayforCheck.day)+"'"+"and ForecastedDateTime < "+"'"+str(FinalForecastDayforCheck.year)+"-"+str(FinalForecastDayforCheck.month)+"-"+str(FinalForecastDayforCheck.day)+"' order by ForecastedDateTime asc")
  193. # 데이타 하나씩 Fetch하여 출력
  194. row = cursor.fetchone()
  195. HumidityRawData = [row]
  196. while row:
  197. row = cursor.fetchone()
  198. if row == None:
  199. break
  200. HumidityRawData.append(row)
  201. conn.close()
  202. TimeIdx_3h_Interval = [datetime(InitialForecastDayforCheck.year, InitialForecastDayforCheck.month, InitialForecastDayforCheck.day, 0, 0, 0)]
  203. TimeIdx_Final = datetime(FinalForecastDayforCheck.year, FinalForecastDayforCheck.month, FinalForecastDayforCheck.day, 0, 0, 0)
  204. while TimeIdx_3h_Interval[-1] < TimeIdx_Final:
  205. TimeIdx_3h_Interval.append(TimeIdx_3h_Interval[-1]+timedelta(hours=3))
  206. TimeIdx_3h_Interval = TimeIdx_3h_Interval[0:-1]
  207. ### DB에 비어있는 값 찾기
  208. idx_tem = 0 # TemperatureRawData 인덱스
  209. idx_hum = 0 # HumidityRawData 인덱스
  210. InitialDay_UnknownData_Tem = [] # 기온 unkown data 초기 일시
  211. FinalDay_UnkownDate_Tem = [] # 기온 unkown data 연속 종료 일시
  212. isContinue_Tem = False
  213. InitialDay_UnknownData_Hum = [] # 습도 unkown data 초기 일시
  214. FinalDay_UnkownDate_Hum = [] # 습도 unkown data 연속 종료 일시
  215. isContinue_Hum = False
  216. for i in range(len(TimeIdx_3h_Interval)):
  217. # DB에 데이터가 없는 경우
  218. if len(TemperatureRawData) == 1:
  219. InitialDay_UnknownData_Tem.append(TimeIdx_3h_Interval[0])
  220. FinalDay_UnkownDate_Tem.append(TimeIdx_3h_Interval[-1])
  221. break
  222. # DB 마지막 일시의 데이터와 복원하고자하는 일시의 마지막 데이터가 일치하지 않는 경우(기온)
  223. elif i >= len(TemperatureRawData):
  224. if idx_tem >= len(TemperatureRawData): ## 복원하고자하는 데이터의 마지막 일시 할당 후 for문 종료
  225. InitialDay_UnknownData_Tem.append(TimeIdx_3h_Interval[i])
  226. FinalDay_UnkownDate_Tem.append(TimeIdx_3h_Interval[-1])
  227. break
  228. elif TimeIdx_3h_Interval[i] == TemperatureRawData[idx_tem][4]:
  229. idx_tem += 1
  230. if isContinue_Tem == True:
  231. FinalDay_UnkownDate_Tem.append(TimeIdx_3h_Interval[i] - timedelta(hours=3))
  232. isContinue_Tem = False
  233. else:
  234. if isContinue_Tem == False:
  235. InitialDay_UnknownData_Tem.append(TimeIdx_3h_Interval[i])
  236. isContinue_Tem = True
  237. #####
  238. # DB에 최근 데이터가 있는 경우(기온)
  239. else:
  240. if TimeIdx_3h_Interval[i] == TemperatureRawData[idx_tem][4]:
  241. idx_tem += 1
  242. if isContinue_Tem == True:
  243. FinalDay_UnkownDate_Tem.append(TimeIdx_3h_Interval[i] - timedelta(hours=3))
  244. isContinue_Tem = False
  245. else:
  246. if isContinue_Tem == False:
  247. InitialDay_UnknownData_Tem.append(TimeIdx_3h_Interval[i])
  248. isContinue_Tem = True
  249. for i in range(len(TimeIdx_3h_Interval)):
  250. # DB 마지막 일시의 데이터와 복원하고자하는 일시의 마지막 데이터가 일치하지 않는 경우(습도)
  251. if len(HumidityRawData) == 1:
  252. InitialDay_UnknownData_Hum.append(TimeIdx_3h_Interval[0])
  253. FinalDay_UnkownDate_Hum.append(TimeIdx_3h_Interval[-1])
  254. break
  255. elif i >= len(HumidityRawData):
  256. if idx_hum >= len(HumidityRawData): ## 복원하고자하는 데이터의 마지막 일시 할당 후 for문 종료
  257. InitialDay_UnknownData_Hum.append(TimeIdx_3h_Interval[i])
  258. FinalDay_UnkownDate_Hum.append(TimeIdx_3h_Interval[-1])
  259. break
  260. elif TimeIdx_3h_Interval[i] == HumidityRawData[idx_hum][4]:
  261. idx_hum += 1
  262. if isContinue_Hum == True:
  263. FinalDay_UnkownDate_Hum.append(TimeIdx_3h_Interval[i] - timedelta(hours=3))
  264. isContinue_Hum = False
  265. else:
  266. if isContinue_Hum == False:
  267. InitialDay_UnknownData_Hum.append(TimeIdx_3h_Interval[i])
  268. isContinue_Hum = True
  269. #####
  270. # DB에 복원하고자하는 일시의 마지막 데이터가 있는 경우(습도)
  271. else:
  272. if TimeIdx_3h_Interval[i] == HumidityRawData[idx_hum][4]:
  273. idx_hum += 1
  274. if isContinue_Hum == True:
  275. FinalDay_UnkownDate_Hum.append(TimeIdx_3h_Interval[i] - timedelta(hours=3))
  276. isContinue_Hum = False
  277. else:
  278. if isContinue_Hum == False:
  279. InitialDay_UnknownData_Hum.append(TimeIdx_3h_Interval[i])
  280. isContinue_Hum = True
  281. ### Restoring unknown data from actual past weather data
  282. # MSSQL Access
  283. conn = pymssql.connect(host = targetDB_IP, user = targetDB_UserID, password = targetDB_UserPW, database = targetDB_Name, autocommit=True)
  284. # Create Cursor from Connection
  285. cursor = conn.cursor()
  286. for i in range(len(FinalDay_UnkownDate_Tem)):
  287. Tem, Hum = get_weather(InitialDay_UnknownData_Tem[i], FinalDay_UnkownDate_Tem[i] + timedelta(hours=1)) ## API 특징 end_time은 포함하지않으므로
  288. tem_date = InitialDay_UnknownData_Tem[i]
  289. for j in range(0,len(Tem),3):
  290. try:
  291. cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted (SiteId, CreatedDateTime, Category, BaseDateTime, ForecastedDateTime, ForecastedValue, nx, ny) VALUES(1," + "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','"+ "Temperature" + "','"+ str(tem_date) + "','" + str(tem_date) + "'," + str(Tem[j]) + "," + n_x + "," + n_y + ")")
  292. except:
  293. print('There is an issue in the progress of restoring unknown weather forecast data to actual past weather data. (Temperature)')
  294. tem_date += timedelta(hours=3)
  295. for i in range(len(FinalDay_UnkownDate_Hum)):
  296. Tem, Hum = get_weather(InitialDay_UnknownData_Hum[i], FinalDay_UnkownDate_Hum[i] + timedelta(hours=1)) ## API 특징 end_time은 포함하지않으므로
  297. hum_date = InitialDay_UnknownData_Hum[i]
  298. for j in range(0,len(Hum),3):
  299. try:
  300. cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted (SiteId, CreatedDateTime, Category, BaseDateTime, ForecastedDateTime, ForecastedValue, nx, ny) VALUES(1," + "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','"+ "Humidity" + "','"+ str(hum_date) + "','" + str(hum_date) + "'," + str(Hum[j]) + "," + n_x + "," + n_y + ")")
  301. except:
  302. print('There is an issue in the progress of restoring unknown weather forecast data to actual past weather data. (Humidity)')
  303. hum_date += timedelta(hours=3)
  304. conn.close()
  305. if len(FinalDay_UnkownDate_Tem) == 0:
  306. print('There is no unknown data before (Temperature)')
  307. else:
  308. for i in range(len(InitialDay_UnknownData_Tem)):
  309. print('Initial and final date of unknown data (Tempearure) : ', InitialDay_UnknownData_Tem[i], FinalDay_UnkownDate_Tem[i])
  310. if len(FinalDay_UnkownDate_Hum) == 0:
  311. print('There is no unknown data before (Humidity)')
  312. else:
  313. for i in range(len(InitialDay_UnknownData_Hum)):
  314. print('Initial and final date of unknown data (Humidity) : ', InitialDay_UnknownData_Hum[i], FinalDay_UnkownDate_Hum[i])
  315. if __name__ == "__main__" :
  316. ## Loading .ini file
  317. myINI = configparser.ConfigParser()
  318. myINI.read("Config.ini", "utf-8" )
  319. # MSSQL Access
  320. conn = pymssql.connect(host=myINI.get('LocalDB_Info','ip_address'), user=myINI.get('LocalDB_Info','user_id'), password=myINI.get('LocalDB_Info','user_password'), database=myINI.get('LocalDB_Info','db_name'), autocommit=True)
  321. # Create Cursor from Connection
  322. cursor = conn.cursor()
  323. # Execute SQL (Config data)
  324. cursor.execute('SELECT * FROM BemsConfigData where SiteId = 1')
  325. rowDB_info = cursor.fetchone()
  326. conn.close()
  327. targetDBIP = rowDB_info[5]
  328. targetDBUserID = rowDB_info[6]
  329. targetDBUserPW = rowDB_info[7]
  330. targetDBName = rowDB_info[8]
  331. nx = str(89) # 예보지점 x 좌표
  332. ny = str(91) # 예보지점 y 좌표
  333. #### Check Unknown past data when starting program ####
  334. Check_Restoring_Unknown_past_data(targetDBIP, targetDBUserID, targetDBUserPW, targetDBName, nx, ny)
  335. ##########################################################
  336. ## 오늘 예보데이터 확인 후 없으면 어제 데이터 임시 삽입
  337. # MSSQL Access
  338. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  339. # Create Cursor from Connection
  340. cursor = conn.cursor()
  341. now = datetime.now()
  342. cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted where SiteId = 1 and ForecastedDateTime >= '" + now.strftime('%Y-%m-%d 00:00:00') + "' ")
  343. # 데이타 하나씩 Fetch하여 출력
  344. row = cursor.fetchone()
  345. rawData=[]
  346. while row:
  347. row = cursor.fetchone()
  348. rawData.append(row)
  349. if len(rawData)==0:
  350. cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted where SiteId = 1 and ForecastedDateTime >= '" + (now - timedelta(days=1)).strftime('%Y-%m-%d 00:00:00') + "' order by CreatedDateTime desc")
  351. # 데이타 하나씩 Fetch하여 출력
  352. row_ = cursor.fetchone()
  353. rawData_=[row_]
  354. while row_:
  355. row_ = cursor.fetchone()
  356. rawData_.append(row_)
  357. rawData_ = rawData_[:len(rawData_)-1]
  358. for i in range(len(rawData_)):
  359. cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted (SiteId, CreatedDateTime, Category, BaseDateTime, ForecastedDateTime, ForecastedValue, nx, ny) VALUES(1," + "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','"+ str(rawData_[i][2]) + "','" + rawData_[i][3].strftime('%Y-%m-%d 01:00:00') + "','" + (rawData_[i][4] + timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S') + "','" + str(rawData_[i][5]) + "'," + nx + "," + ny + ")")
  360. conn.close()
  361. ##########################################################
  362. # Accumulate weather forecast data
  363. while True:
  364. now = datetime.now()
  365. if now.hour == 1 and now.minute <= 15: ## 하루에 한번 오전 1시 이후에 과거 데이터 체크 (오전 1시는 임의로 정한 시각)
  366. Check_Restoring_Unknown_past_data(targetDBIP, targetDBUserID, targetDBUserPW, targetDBName, nx, ny) ## 과거 실 데이터가 어제까지만 제공되기 때문에 매일 어제 데이터가 있는지 체크
  367. if now.hour == 20 and now.minute >= 45 and now.minute <= 59:
  368. AccumulationActive = True
  369. else:
  370. AccumulationActive = False
  371. if now.minute > 55:
  372. print("[ Current Time -", now.hour,":", now.minute,":", now.second,"], " "Sleeping for 10 minutes... Accumulate weather forecasted data at 8:45 ~ 9:00 p.m. every day")
  373. time.sleep(60*10)
  374. else:
  375. print("[ Current Time -", now.hour,":", now.minute,":", now.second,"], " "Sleeping for 15 minutes... Accumulate weather forecasted data at 8:45 ~ 9:00 p.m. every day")
  376. time.sleep(60*15)
  377. if AccumulationActive:
  378. TempWF, HumWF = get_weather_forecast(nx, ny)
  379. # MSSQL Access
  380. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  381. # Create Cursor from Connection
  382. cursor = conn.cursor()
  383. ##########################################################
  384. ## 기존 예보데이터가 임시데이터라면 데이터 삭제 후 삽입
  385. cursor.execute("DELETE " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted where SiteId = 1 and BaseDateTime ='" +(now - timedelta(days=2)).strftime('%Y-%m-%d 01:00:00') + "' and ForecastedDateTime >= '" + (now - timedelta(days=1)).strftime('%Y-%m-%d 00:00:00') + "' ")
  386. ##########################################################
  387. for i in range(1, len(TempWF)):
  388. baseDate = TempWF[i][0]
  389. baseTime = TempWF[i][1]
  390. FcstDate = TempWF[i][2]
  391. FcstTime = TempWF[i][3]
  392. baseDateTime = datetime(int(baseDate[0:4]), int(baseDate[4:6]), int(baseDate[6:]), int(baseTime[0:2]), int(baseTime[2:]))
  393. FcstDateTime = datetime(int(FcstDate[0:4]), int(FcstDate[4:6]), int(FcstDate[6:]), int(FcstTime[0:2]), int(FcstTime[2:]))
  394. try:
  395. cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted (SiteId, CreatedDateTime, Category, BaseDateTime, ForecastedDateTime, ForecastedValue, nx, ny) VALUES(1," + "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','"+ "Temperature" + "','"+ str(baseDateTime) + "','" + str(FcstDateTime) + "'," + TempWF[i][4] + "," + nx + "," + ny + ")")
  396. except:
  397. print('Weather forecasted temperature data already exists! (ForecastDateTime : '+str(FcstDateTime)+')')
  398. for i in range(1, len(HumWF)):
  399. baseDate = HumWF[i][0]
  400. baseTime = HumWF[i][1]
  401. FcstDate = HumWF[i][2]
  402. FcstTime = HumWF[i][3]
  403. baseDateTime = datetime(int(baseDate[0:4]), int(baseDate[4:6]), int(baseDate[6:]), int(baseTime[0:2]), int(baseTime[2:]))
  404. FcstDateTime = datetime(int(FcstDate[0:4]), int(FcstDate[4:6]), int(FcstDate[6:]), int(FcstTime[0:2]), int(FcstTime[2:]))
  405. try:
  406. cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointWeatherForecasted (SiteId, CreatedDateTime, Category, BaseDateTime, ForecastedDateTime, ForecastedValue, nx, ny) VALUES(1," + "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','"+ "Humidity" + "','"+ str(baseDateTime) + "','" + str(FcstDateTime) + "'," + HumWF[i][4] + "," + nx + "," + ny + ")")
  407. except:
  408. print('Weather forecasted humidity data already exists! (ForecastDateTime : '+str(FcstDateTime)+')')
  409. conn.close()
  410. print("Sleeping for 15 minutes ...")
  411. time.sleep(60*15)