Test_QuantiwiseDataExcelProcess.py

#_*_ coding: utf-8 _*_

import win32com.client
from Test_MariaDB import WrapDB
import pandas as pd
import xlsxwriter
import openpyxl
from xlutils.copy import copy as xl_copy
import math

item_dic = {
"삼천당제약":1
,"하림지주":2
,"포스코켐텍":3
,"동진쎄미켐":4
,"서부T&D":5
,"네이처셀":6
,"카카오M":7
,"포스코 ICT":8
,"유진기업":9
,"아난티":10
,"GS홈쇼핑":11
,"에이치엘비":12
,"NICE평가정보":13
,"녹십자셀":14
,"피에스케이":15
,"코웰패션":16
,"파라다이스":17
,"KG이니시스":18
,"CJ ENM":19
,"이지바이오":20
,"JYP Ent.":21
,"제이콘텐트리":22
,"SK머티리얼즈":23
,"솔브레인":24
,"삼표시멘트":25
,"상상인":26
,"이오테크닉스":27
,"오스코텍":28
,"디오":29
,"에스엠":30
,"코미팜":31
,"카페24":32
,"바텍":33
,"대아티아이":34
,"서울반도체":35
,"오스템임플란트":36
,"인트론바이오":37
,"미래컴퍼니":38
,"안랩":39
,"에스에프에이":40
,"리노공업":41
,"CMG제약":42
,"티씨케이":43
,"안트로젠":44
,"엘앤에프":45
,"아프리카TV":46
,"셀트리온제약":47
,"웹젠":48
,"메디포스트":49
,"컴투스":50
,"모두투어":51
,"크리스탈":52
,"휴온스글로벌":53
,"바이로메드":54
,"차바이오텍":55
,"동국제약":56
,"에코프로":57
,"메디톡스":58
,"나스미디어":59
,"비에이치":60
,"셀트리온헬스케어":61
,"우리은행":62
,"메리츠화재":63
,"삼양홀딩스":64
,"하이트진로":65
,"유한양행":66
,"CJ대한통운":67
,"두산":68
,"대림산업":69
,"한국타이어월드와이드":70
,"기아차":71
,"한화손해보험":72
,"동아쏘시오홀딩스":73
,"SK하이닉스":74
,"영풍":75
,"현대건설":76
,"삼성화재":77
,"한화":78
,"DB하이텍":79
,"CJ":80
,"JW중외제약":81
,"LG상사":82
,"동국제강":83
,"세아베스틸":84
,"대한전선":85
,"현대해상":86
,"동양":87
,"대상":88
,"SK네트웍스":89
,"오리온홀딩스":90
,"삼화콘덴서":91
,"코오롱":92
,"롯데푸드":93
,"넥센타이어":94
,"KCC":95
,"아모레G":96
,"부광약품":97
,"세아제강":98
,"대웅":99
,"삼양식품":100
,"태광산업":101
,"한일홀딩스":102
,"쌍용양회":103
,"유안타증권":104
,"대한항공":105
,"영진약품":106
,"대신증권":107
,"LG":108
,"쌍용차":109
,"코리안리":110
,"롯데정밀화학":111
,"현대제철":112
,"신세계":113
,"농심":114
,"송원산업":115
,"효성":116
,"롯데지주":117
,"녹십자홀딩스":118
,"롯데칠성":119
,"현대차":120
,"현대그린푸드":121
,"POSCO":122
,"삼진제약":123
,"SPC삼립":124
,"파미셀":125
,"DB손해보험":126
,"에스엘":127
,"대한해운":128
,"삼성전자":129
,"NH투자증권":130
,"동원산업":131
,"SK디스커버리":132
,"LS":133
,"녹십자":134
,"GS건설":135
,"한일현대시멘트":136
,"삼성SDI":137
,"인스코비":138
,"대한유화":139
,"미래에셋대우":140
,"AK홀딩스":141
,"GS리테일":142
,"오뚜기":143
,"에이프로젠 KIC":144
,"일양약품":145
,"F&F":146
,"메리츠종금증권":147
,"호텔신라":148
,"한미사이언스":149
,"삼성전기":150
,"한샘":151
,"태영건설":152
,"한올바이오파마":153
,"경동나비엔":154
,"현대중공업":155
,"무림P&P":156
,"한화케미칼":157
,"우리종금":158
,"OCI":159
,"LS산전":160
,"고려아연":161
,"삼성중공업":162
,"현대미포조선":163
,"아이에스동서":164
,"S-Oil":165
,"LG이노텍":166
,"롯데케미칼":167
,"현대상선":168
,"현대위아":169
,"유양디앤유":170
,"현대상사":171
,"금호석유":172
,"SKC":173
,"현대모비스":174
,"한화에어로스페이스":175
,"더존비즈온":176
,"HDC":177
,"에스원":178
,"한솔케미칼":179
,"한국전력":180
,"삼성증권":181
,"SK텔레콤":182
,"현대엘리베이":183
,"애경산업":184
,"삼성에스디에스":185
,"SK가스":186
,"한온시스템":187
,"한섬":188
,"일진머티리얼즈":189
,"아시아나항공":190
,"코웨이":191
,"롯데쇼핑":192
,"다우기술":193
,"기업은행":194
,"한국콜마홀딩스":195
,"제이준코스메틱":196
,"남해화학":197
,"동서":198
,"BGF":199
,"삼성엔지니어링":200
,"삼성물산":201
,"팬오션":202
,"삼성카드":203
,"제일기획":204
,"KT":205
,"신세계인터내셔날":206
,"신세계푸드":207
,"롯데관광개발":208
,"LG유플러스":209
,"삼성생명":210
,"필룩스":211
,"KT&G":212
,"두산중공업":213
,"LG디스플레이":214
,"NICE":215
,"SK":216
,"한국토지신탁":217
,"강원랜드":218
,"NAVER":219
,"카카오":220
,"한국가스공사":221
,"엔씨소프트":222
,"팜스코":223
,"CJ헬로":224
,"하나투어":225
,"키움증권":226
,"대우조선해양":227
,"두산인프라코어":228
,"한미반도체":229
,"대우건설":230
,"포스코대우":231
,"한국항공우주":232
,"동원F&B":233
,"한전KPS":234
,"LG생활건강":235
,"LG화학":236
,"한전기술":237
,"스카이라이프":238
,"신한지주":239
,"현대홈쇼핑":240
,"한라홀딩스":241
,"현대로템":242
,"LG전자":243
,"셀트리온":244
,"휴켐스":245
,"대웅제약":246
,"현대백화점":247
,"한국금융지주":248
,"롯데하이마트":249
,"금호타이어":250
,"GS":251
,"CJ CGV":252
,"현대리바트":253
,"아이엔지생명":254
,"LIG넥스원":255
,"휠라코리아":256
,"동양생명":257
,"미래에셋생명":258
,"현대글로비스":259
,"하나금융지주":260
,"한화생명":261
,"제주항공":262
,"아모레퍼시픽":263
,"LF":264
,"후성":265
,"JW홀딩스":266
,"SK이노베이션":267
,"CJ제일제당":268
,"풍산":269
,"KB금융":270
,"한세실업":271
,"LG하우시스":272
,"영원무역":273
,"GKL":274
,"락앤락":275
,"코오롱인더":276
,"한미약품":277
,"BNK금융지주":278
,"DGB금융지주":279
,"이마트":280
,"덴티움":281
,"삼양사":282
,"한국타이어":283
,"한국콜마":284
,"동아에스티":285
,"JB금융지주":286
,"한진칼":287
,"NHN엔터테인먼트":288
,"아세아시멘트":289
,"종근당":290
,"쿠쿠홀딩스":291
,"코스맥스":292
,"만도":293
,"삼성바이오로직스":294
,"SK디앤디":295
,"이노션":296
,"잇츠한불":297
,"두산밥캣":298
,"넷마블":299
,"현대중공업지주":300
,"현대일렉트릭":301
,"현대건설기계":302
,"오리온":303
,"제일약품":304
,"진에어":305
,"롯데제과":306
,"BGF리테일":307
,"쿠쿠홈시스":308
,"SK케미칼":309
,"에스모":310
}



import timeit
import copy
from openpyxl import load_workbook


start_time = timeit.default_timer()
if 1:
    wb = load_workbook(filename='국내주식_MarketData1_.xlsx', read_only=False, data_only=False)
    wb_list = ['주식_시가','주식_종가','주식_고가','주식_저가','주식_거래량','주식_시가총액']
else:
    wb = load_workbook(filename='국내주식_MarketData2_.xlsx', read_only=False, data_only=False)
    wb_list = ['주식_기관순매수','주식_외인순매수']
for wb_nm in wb_list:
    ws = wb[wb_nm]
    end_time = timeit.default_timer()
    print ("엑셀 read: ", "\t", str(end_time - start_time))
    start_time = end_time

    all_columns = ws.columns


    # Wrap운용팀 DB Connect
    db = WrapDB()
    db.connet(host="127.0.0.1", port=3306, database="WrapDB_2", user="root", password="ryumaria")

    # 엑셀 load 된 데이터 처리
    dates = None
    values = None
    for column_idx, column in enumerate(all_columns):
        
        # 날짜 컬럼
        if column_idx == 0:
            dates = copy.copy(column)

        # 데이터 컬럼 시작
        elif column_idx >= 1:
            values = copy.copy(column)

            # insert 되는 item 갯수 확인
            count = 0

            # data pair가 완성되면 처음 4 row는 meta data임
            group_cd = None
            item_cd = None
            item_name = None
            ticker = None
            for row_idx in range(len(dates)):
                
                # 종목명 행
                if row_idx == 8:
                    
                    # 데이터 존재하지 않는 컬럼을 리드하려고 하는 경우
                    #if math.isnan(values[row_idx].value) == True:
                    #    break

                    item_cd = item_dic[values[row_idx].value]
                    item_nm = values[row_idx].value
                    #print (values[idx].value, item_cd)

                # 데이터 행 시작
                elif row_idx >= 14:

                    # Null 셀이면 다음 item으로 패스
                    if values[row_idx].value == None or math.isnan(values[row_idx].value) == True:
                        continue

                    #print(str(dates[idx].value)[:10], "\t", item_cd, "\t", values[idx].value, "\t", group_cd)
                    #s_date = str(dates[row_idx].value)[0:4] + '-' + str(dates[row_idx].value)[4:6] +'-'+ str(dates[row_idx].value)[6:8]
                    s_date = str(dates[row_idx].value)[0:10]
                    f_value = round(values[row_idx].value, 10)
                    count += db.insert_quantiwise_value(item_cd, s_date, f_value, wb_nm)
                    #break
            #break

            # insert된 리스트 정보 프린트
            print (item_cd, "\t", item_nm, "\t", count, "\t", str(values[row_idx]))

            '''
            # 첫번째 pair 만 테스트
            if idx == 1:
                print ("aaaaaaaaaaaaaaaaaaa")
                break
            '''
    end_time = timeit.default_timer()
    print ("DB insert: ", "\t", str(end_time - start_time))

    # Wrap운용팀 DB Disconnect
    db.disconnect()