def assemblymanset(cnx, cursor): cnx, cursor = database_setting() table = "assemblyman" table_column = "(assemblymanCd, empNm, partyNm, reeleGbnNm, origCd, hobbyNm) " insert_tuple = "INSERT into " + table + table_column + "values" query = insert_tuple + "(%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE assemblymanCd = assemblymanCd;" items0 = test.getitem(0) for i, item in enumerate(items0): data_assemblymanCd = item['num'] data_empNm = item['empNm'] data_partyNm, data_hobbyNm = test.getpartyCd(data_assemblymanCd, item['deptCd']) data_reeleGbNm = item['reeleGbnNm'] data_origNm = item['origNm'] cursor.execute( "select runningAreaCd from runningarea where runningarea.runningAreaNm = '" + data_origNm + "';") origCd = cursor.fetchall() data_origCd = origCd[0][0] cursor.execute(query, (data_assemblymanCd, data_empNm, data_partyNm, data_reeleGbNm, data_origCd, data_hobbyNm)) cnx.commit() if i % 30 == 0: print("■", end="")
def provinceset(cnx, cursor): cnx, cursor = database_setting() array = [["021001", "서울광역시"], ["021002", "부산광역시"], ["021003", "대구광역시"], ["021004", "인천광역시"], ["021005", "광주광역시"], ["021006", "대전광역시"], ["021007", "울산광역시"], ["021008", "경기도"], ["021009", "강원도"], ["021010", "충청북도"], ["021011", "충청남도"], ["021012", "전라북도"], ["021013", "전라남도"], ["021014", "경상북도"], ["021015", "경상남도"], ["021016", "제주도"], ["021017", "비례대표"]] table = "province" table_column = "(provinceCd, provinceNm) " insert_tuple = "INSERT into " + table + table_column + "values " insert_query = insert_tuple + "(%s, %s) ON DUPLICATE KEY UPDATE provinceCd = provinceCd;" for i, value in enumerate(array): data_provinceCd = array[i][0] data_provinceNm = array[i][1] cursor.execute(insert_query, (data_provinceCd, data_provinceNm)) cnx.commit() cursor.execute(insert_query, ("021018", "세종특별자치시")) cnx.commit()
def comitteeset(cnx, cursor): cnx, cursor = database_setting() array = [["130001", "국회운영위원회", "2537"], ["130002", "법제사법위원회", "2747"], ["130003", "정무위원회", "110"], ["130004", "기획재정위원회", "2"], ["130005", "교육위원회", "2540"], ["130006", "과학기술정보방송통신위원회", "133"], ["130007", "외교통일위원회", "2638"], ["130008", "국방위원회", "2659"], ["130009", "행정안전위원회", "2609"], ["130010", "문화체육관광위원회", "105"], ["130011", "농림축산식품해양수산위원회", "2811"], ["130012", "산업통상자원중소벤처기업위원회", "2782"], ["130013", "보건복지위원회", "2764"], ["130014", "환경노동위원회", "2630"], ["130015", "국토교통위원회", "114"], ["130016", "정보위원회", "52"], ["130017", "여성가족위원회", "2690"]] table = "comittee" table_column = "(comitteeCd, comitteeNm, chairmanCd) " insert_tuple = "INSERT into " + table + table_column + "values " insert_query = insert_tuple + "(%s, %s, %s) ON DUPLICATE KEY UPDATE comitteeCd = comitteeCd;" for i, value in enumerate(array): cursor.execute(insert_query, (value[0], value[1], value[2])) cnx.commit() cnx.commit()
def proposalset(cnx, cursor): cnx, cursor = database_setting() array = [["400001", "장애인활동 지원에 관한 법률 일부개정법률안", "2667"], ["400002", "변호사법 일부개정법률안", "2842"], ["400003", "사할린한인 국내유족에 대한 지원 특별법안", "2638"], ["400004", "소방기본법 일부개정법률안", "2573"], ["400005", "보행안전 및 편의증진에 관한 법률 일부개정법률안", "2857"], ["400006", "지방세특례제한법 일부개정법률안", "2442"], ["400007", "자동차관리법 일부개정법률안 ", "2865"], ["400008", "국가인권위원회법 일부개정법률안", "2573"], ["400009", "근로기준법 일부개정법률안 ", "2573"], ["400010", "가정폭력범죄의 처벌 등에 관한 특례법 일부개정법률안", "2573"]] table = "proposal" table_column = "(proposalCd, proposalNm, proposerCd) " insert_tuple = "INSERT into " + table + table_column + "values " insert_query = insert_tuple + "(%s, %s, %s) ON DUPLICATE KEY UPDATE proposalCd = proposalCd;" for i, value in enumerate(array): cursor.execute(insert_query, (value[0], value[1], value[2])) cnx.commit() cnx.commit()
print("*-----------%7s에 소속한 국회의원 %2d명----------*" % (search_province_name, len(result_manNm))) for manNm in result_manNm: print("%12s %16s" % (manNm[0], manNm[1])) print() # 7. 국회의원 평가 def evaluate_assemblyman(): setevaluation.evaluation(cnx, cursor) pass if __name__ == '__main__': cnx, cursor = database_setting() while True: print_menu() menuNum = input(">> ") if menuNum == set_DB_using_openAPI: DB_setting(cnx, cursor) elif menuNum == search_assemblyman: assemblyman_searching() elif menuNum == search_party: party_searching() elif menuNum == search_committee: committee_searching() elif menuNum == search_proposal: proposal_searching() elif menuNum == search_province: province_searching()
def assemblyman_has_comitteeset(cnx, cursor): cnx, cursor = database_setting() array = [["130001", "2537"], ["130001", "2846"], ["130001", "2442"], ["130001", "2820"], ["130001", "2842"], ["130001", "2851"], ["130001", "2929"], ["130001", "2833"], ["130001", "2918"], ["130001", "2901"], ["130001", "2937"], ["130001", "2861"], ["130001", "2938"], ["130001", "2924"], ["130001", "2905"], ["130001", "2969"], ["130001", "2973"], ["130001", "131"], ["130001", "2920"], ["130001", "490"], ["130001", "493"], ["130001", "2951"], ["130001", "2913"], ["130001", "2896"], ["130001", "2457"], ["130001", "2975"], ["130001", "2568"], ["130001", "2841"], ["130002", "2747"], ["130002", "2928"], ["130002", "2607"], ["130002", "2831"], ["130002", "2851"], ["130002", "2833"], ["130002", "2915"], ["130002", "2871"], ["130002", "2731"], ["130002", "2869"], ["130002", "2834"], ["130002", "2587"], ["130002", "2639"], ["130002", "2711"], ["130002", "231"], ["130002", "2655"], ["130002", "2934"], ["130002", "2533"], ["130003", "110"], ["130003", "2963"], ["130003", "2919"], ["130003", "2820"], ["130003", "2952"], ["130003", "2929"], ["130003", "2885"], ["130003", "2683"], ["130003", "2850"], ["130003", "2613"], ["130003", "2930"], ["130003", "2874"], ["130003", "2705"], ["130003", "2911"], ["130003", "2689"], ["130003", "2199"], ["130003", "2640"], ["130003", "2920"], ["130003", "2942"], ["130003", "730"], ["130003", "2914"], ["130003", "2856"], ["130003", "2568"], ["130003", "2853"], ["130004", "2"], ["130004", "2866"], ["130004", "2868"], ["130004", "2704"], ["130004", "2892"], ["130004", "2606"], ["130004", "2836"], ["130004", "116"], ["130004", "2873"], ["130004", "2955"], ["130004", "91"], ["130004", "2573"], ["130004", "2522"], ["130004", "41"], ["130004", "2541"], ["130004", "2740"], ["130004", "131"], ["130004", "2819"], ["130004", "179"], ["130004", "2944"], ["130004", "49"], ["130004", "2547"], ["130004", "2877"], ["130004", "406"], ["130004", "2759"], ["130004", "102"], ["130005", "2540"], ["130005", "2872"], ["130005", "2543"], ["130005", "2908"], ["130005", "2925"], ["130005", "2918"], ["130005", "489"], ["130005", "2945"], ["130005", "2485"], ["130005", "2611"], ["130005", "2969"], ["130005", "491"], ["130005", "2847"], ["130005", "280"], ["130005", "2979"], ["130006", "133"], ["130006", "2875"], ["130006", "2821"], ["130006", "2921"], ["130006", "145"], ["130006", "222"], ["130006", "2823"], ["130006", "122"], ["130006", "342"], ["130006", "247"], ["130006", "2924"], ["130006", "2959"], ["130006", "147"], ["130006", "2768"], ["130006", "490"], ["130006", "2883"], ["130006", "2926"], ["130006", "2753"], ["130006", "2946"], ["130006", "2958"], ["130007", "2638"], ["130007", "2886"], ["130007", "2644"], ["130007", "234"], ["130007", "305"], ["130007", "211"], ["130007", "178"], ["130007", "202"], ["130007", "89"], ["130007", "281"], ["130007", "47"], ["130007", "746"], ["130007", "482"], ["130007", "2788"], ["130007", "454"], ["130007", "1804"], ["130007", "90"], ["130007", "147"], ["130007", "237"], ["130007", "190"], ["130007", "372"], ["130007", "2658"], ["130008", "2659"], ["130008", "2615"], ["130008", "2898"], ["130008", "2597"], ["130008", "2902"], ["130008", "2775"], ["130008", "2585"], ["130008", "2374"], ["130008", "2537"], ["130008", "2895"], ["130008", "249"], ["130008", "2957"], ["130008", "2632"], ["130008", "2962"], ["130008", "2839"], ["130008", "470"], ["130009", "2609"], ["130009", "2478"], ["130009", "2467"], ["130009", "2824"], ["130009", "155"], ["130009", "2846"], ["130009", "2576"], ["130009", "2860"], ["130009", "2865"], ["130009", "2932"], ["130009", "2863"], ["130009", "2848"], ["130009", "2732"], ["130009", "492"], ["130009", "2977"], ["130009", "2020"], ["130009", "2894"], ["130009", "2442"], ["130009", "2792"], ["130009", "2712"], ["130009", "2857"], ["130009", "2761"], ["130009", "2577"], ["130010", "105"], ["130010", "2937"], ["130010", "2592"], ["130010", "2912"], ["130010", "145"], ["130010", "222"], ["130010", "2453"], ["130010", "2676"], ["130010", "2907"], ["130010", "365"], ["130010", "148"], ["130010", "2544"], ["130010", "38"], ["130010", "2904"], ["130010", "390"], ["130010", "736"], ["130010", "2978"], ["130010", "2867"], ["130010", "2956"], ["130011", "2811"], ["130011", "2608"], ["130011", "2680"], ["130011", "2843"], ["130011", "2879"], ["130011", "128"], ["130011", "2940"], ["130011", "2901"], ["130011", "2840"], ["130011", "2938"], ["130011", "19"], ["130011", "2927"], ["130011", "2593"], ["130011", "2970"], ["130011", "2575"], ["130011", "2965"], ["130011", "493"], ["130011", "2870"], ["130011", "2953"], ["130012", "2782"], ["130012", "2675"], ["130012", "2826"], ["130012", "2842"], ["130012", "2887"], ["130012", "2580"], ["130012", "2844"], ["130012", "2692"], ["130012", "2972"], ["130012", "2861"], ["130012", "85"], ["130012", "2859"], ["130012", "2948"], ["130012", "2862"], ["130012", "2923"], ["130012", "2525"], ["130012", "2827"], ["130012", "2876"], ["130012", "2951"], ["130012", "382"], ["130012", "2896"], ["130012", "2956"], ["130012", "2457"], ["130012", "2975"], ["130012", "239"], ["130012", "2910"], ["130012", "153"], ["130013", "2764"], ["130013", "2968"], ["130013", "2450"], ["130013", "2949"], ["130013", "2760"], ["130013", "2618"], ["130013", "2888"], ["130013", "84"], ["130013", "2890"], ["130013", "1967"], ["130013", "2690"], ["130013", "2950"], ["130013", "2454"], ["130013", "2650"], ["130013", "2916"], ["130013", "2973"], ["130013", "2667"], ["130013", "2765"], ["130013", "2881"], ["130013", "2837"], ["130013", "2838"], ["130013", "2841"], ["130013", "2974"], ["130014", "2630"], ["130014", "2469"], ["130014", "2936"], ["130014", "165"], ["130014", "556"], ["130014", "488"], ["130014", "2882"], ["130014", "2941"], ["130014", "2922"], ["130014", "2665"], ["130014", "2852"], ["130014", "2897"], ["130014", "2893"], ["130014", "2594"], ["130014", "2903"], ["130014", "2966"], ["130015", "114"], ["130015", "2481"], ["130015", "2488"], ["130015", "52"], ["130015", "2855"], ["130015", "2971"], ["130015", "2939"], ["130015", "2967"], ["130015", "2943"], ["130015", "2767"], ["130015", "57"], ["130015", "2906"], ["130015", "2935"], ["130015", "2976"], ["130015", "2858"], ["130015", "2905"], ["130015", "2591"], ["130015", "2878"], ["130015", "2845"], ["130015", "2964"], ["130015", "2880"], ["130015", "2899"], ["130015", "2682"], ["130015", "2482"], ["130015", "2548"], ["130015", "2830"], ["130015", "2712"], ["130015", "2917"], ["130015", "358"], ["130015", "2835"], ["130016", "52"], ["130016", "2576"], ["130016", "2639"], ["130016", "2457"], ["130016", "556"], ["130016", "47"], ["130016", "2613"], ["130016", "2537"], ["130016", "2607"], ["130016", "131"], ["130016", "390"], ["130017", "2690"], ["130017", "2950"], ["130017", "490"], ["130017", "2867"], ["130017", "2866"], ["130017", "2928"], ["130017", "2882"], ["130017", "2930"], ["130017", "2834"], ["130017", "2916"], ["130017", "491"], ["130017", "2894"], ["130017", "2881"], ["130017", "2895"], ["130017", "2847"], ["130017", "2921"], ["130017", "2841"]] table = "assemblyman_has_comittee" table_column = "(assemblymanCd, comitteeCd) " insert_tuple = "INSERT into " + table + table_column + "values " insert_query = insert_tuple + "(%s, %s) ON DUPLICATE KEY UPDATE assemblymanCd = assemblymanCd, comitteeCd = comitteeCd;" for i, value in enumerate(array): cursor.execute(insert_query, (value[1], value[0])) cnx.commit() cnx.commit()
def origset(cnx, cursor): getlist = 'getMemberCurrStateList' # 국회의원 현황조회 method_num = 0 getdetail = 'getMemberDetailInfoList' # 국회의원 상세조회 method_num = 1 getjungdang = 'getJungDangMemberCurrStateList' # 소속정당별 국회의원 목록조회 method_num = 2 getcomm = 'getCommMemberCurrStateList' # 소속위원회별 국회의원 목록조회 method_num = 3 getmethod = 'getLoOrProporMemberCurrStateList' # 당선방법별 국회의원 목록 정보조회 method_num = 4 getlocalmen = 'getLocalMemberCurrStateList' # 지역별 국회의원 목록 정보조회 method_num = 5 getparty = 'getPolySearch' # 정당검색 method_num = 6 getlocal = 'getLocalSearch' # 지역검색 method_num = 7 getCd = 'getMemberNameInfoList' # 이름검색 servicekey = 'oXFNR8BgFm4XU8GWU9ipGvj20Y9fBuvytfINkjq6fASRin0xIYGyO3lUUYQiTMb4%2Fjuno0wZg7azEaby0ZnLag%3D%3D' url = 'http://apis.data.go.kr/9710000/NationalAssemblyInfoService/' cnx, cursor = database_setting() select_query = "select provinceCd from province;" cursor.execute(select_query) provinceList = cursor.fetchall() table = "runningarea" table_column = "(runningAreaCd, RunningAreaNm, provinceCd)" insert_query = "INSERT INTO " + table + table_column + " values(%s, %s, %s) ON DUPLICATE KEY UPDATE runningAreaCd = runningAreaCd;" for i, provinceCd in enumerate(provinceList): request = urllib.request.Request( url + getlocal + '?' + 'ServiceKey=oXFNR8BgFm4XU8GWU9ipGvj20Y9fBuvytfINkjq6fASRin0xIYGyO3lUUYQiTMb4%2Fjuno0wZg7azEaby0ZnLag%3D%3D' + "&" + "numOfRows=300" + "&" + "pageNo=1" + "&" + "up_orig_cd=" + provinceCd[0]) if provinceCd[0] == "021017": data_runningAreaCd = "240001" data_runningAreaNm = "비례대표" data_provinceCd = "021017" cursor.execute( insert_query, (data_runningAreaCd, data_runningAreaNm, data_provinceCd)) cnx.commit() elif provinceCd[0] == "021018": data_runningAreaCd = "180001" data_runningAreaNm = "세종특별자치시" data_provinceCd = "021018" cursor.execute( insert_query, (data_runningAreaCd, data_runningAreaNm, data_provinceCd)) cnx.commit() else: request.get_method = lambda: 'GET' response_body = urllib.request.urlopen(request).read().decode( 'utf8') dict_type = xmltodict.parse(response_body) json_type = json.dumps(dict_type) dict2_type = json.loads(json_type) items = dict2_type['response']['body']['items']['item'] #print(items) for n, value in enumerate(items): data_runningAreaCd = items[n]['origCd'] data_runningAreaNm = items[n]['origNm'] data_provinceCd = provinceCd[0] cursor.execute( insert_query, (data_runningAreaCd, data_runningAreaNm, data_provinceCd)) cnx.commit()