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()