[Python] 대한민국 행정동 데이터 MySQL DB 테이블 생성 예시

 

이전글 에서 설명한 '행정구역분류' 데이터는 엑셀 문서 형식인데, 이를 SQL DB로 가져오는 예제.

 

o 준비사항 

 1) 엑셀 파일 : 아래 둘 중 하나, 혹은 둘다. 

   - 행정안전부 엑셀 파일  ex) jscode20190701.zip의 KIKmix.20190701.xlsx

   - 통계분류포털 엑셀 파일 : 한국행정구역분류_2019.7.1.기준(F)_추가반영.xls

 2) mysql 실행 환경 : 이전글 참고.

   - 접속 계정 정보 (username / password)

   - db or schema 생성 (mysql에서는 동일 의미)

   - 테이블 생성 : 미리 엑셀 문서 보고, 희망하는 구조로 테이블 생성. 귀찮으면 아래 코드 주석 참고.

 3) python 실행 환경 :  이전글 참고.

   - 개인 환경에 따라

     파일 전체 경로, db접속계정정보, db명, 테이블명 수정 필수.

 

o 행정구역분류 데이터 > DB 삽입

  1) 행정안전부 (https://www.mois.go.kr) 데이터

    xlsx파일이니 openpyxl, mysql에 insert하기 위해 pymysql이용.

 

      $ pip install openpyxl

      $ pip install pymysql

 

 ** 2개 함수로 구분해서 시간복잡도가 O(n2)인데, 2개의 함수를 하나로 병합해서 O(n)으로 줄일 수 있음.

   일부러 구분했으니(DB종류, Table구조 등 고려) 참고 ** 

# -*- coding: utf-8 -*- 
from openpyxl import load_workbook
import pymysql

mydict = {}

def import_xlsxfile(filename):
    load_wb = load_workbook(filename, data_only=True)
    load_ws = load_wb['KIKmix']

    head = True
    for row in load_ws.rows:
        if head is True:
            head = False
            continue

        if row[2].value is None or row[3].value is None:
            continue

        if row[5].value is not None:
            dong_cd     = row[0].value  # A = row[0] 행정동코드 
            sido_nm     = row[1].value  # B = row[1] 시도명
            sigungu_nm  = row[2].value  # C = row[2] 시군구명
            dong_nm     = row[3].value  # D = row[3] 읍면동명
            dong_cd2    = row[4].value  # E = row[4] 법정동코드   
            dong_nm2    = row[5].value  # F = row[5] 동리명    
            base_year   = row[6].value  # G = row[6] 생성일자   
            #None       = row[7].value  # H = row[7] 말소일자
            mydict[dong_cd] = [sido_nm, sigungu_nm, dong_nm, dong_cd2, dong_nm2, base_year]
        
def insert_into_mysql():
    '''
    +------------+-------------+------+-----+---------+-------+
    | Field      | Type        | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+-------+
    | dong_cd    | varchar(11) | NO   | PRI | NULL    |       |
    | sido_nm    | varchar(45) | NO   |     | NULL    |       |
    | sigungu_nm | varchar(45) | NO   |     | NULL    |       |
    | dong_nm    | varchar(45) | NO   |     | NULL    |       |
    | dong_cd2   | varchar(11) | NO   |     | NULL    |       |
    | dong_nm2   | varchar(45) | NO   |     | NULL    |       |
    | base_year  | varchar(8)  | NO   |     | NULL    |       |
    +------------+-------------+------+-----+---------+-------+
    7 rows in set (0.00 sec)
    '''
    conn = pymysql.connect(host='주소', user='사용자명', password='암호', db='DB명', charset='utf8')
    curs = conn.cursor()

    for key in mydict:
        arr = mydict[key]
        sql = "INSERT INTO korea_dong_detail_tb VALUES(\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\");"\
            % (key, arr[0], arr[1], arr[2], arr[3], arr[4], arr[5])
        curs.execute(sql)

    conn.commit()
    conn.close()

import_xlsxfile("{파일경로}/KIKmix.20190701.xlsx")
insert_into_mysql()

 

결과

 

 

  2) 통계분류포털 : 행정구역분류 자료실의 첨부파일

    한국행정구역분류_2019.7.1.기준(F)_추가반영.xls

    아래 2개 sheet에서 데이터 추출

      - 1. 총괄표(현황) : 기본 데이터 추출

      - 법정동코드 연계 자료분석용 : 행정기관코드 추출

 

    대략 아래의 데이터를 뽑아본다.

행정구역코드 | 시도 | 시군구 | 읍면동 | 시도(영) | 시군구(영) | 읍면동(영) |시도(중) | 시군구(중) | 읍면동(중) | 대분류 | 중분류 | 영무행정기관코드

 

    xls파일이니 xlrd, mysql에 insert하기 위해 pymysql이용.

      $ pip install xlrd

      $ pip install pymysql

from xlrd import open_workbook
import pymysql

mydict = {}

def import_xlsfile(xlsfilename):
    wb = open_workbook(filename=xlsfilename)
    sheet = wb.sheet_by_name('1. 총괄표(현행)')

    sido_nm_cn = ''
    sido_nm_eng = ''
    sigungu_nm_cn = ''
    sigungu_nm_eng = ''
    for i in range(sheet.nrows):
        row = sheet.row(i)

        if row[3].value == '':
            sido_nm_eng = row[7].value
            sido_nm_cn  = row[8].value
            continue

        if row[5].value == '':
            sigungu_nm_eng = row[7].value
            sigungu_nm_cn  = row[8].value
            continue
        
        if sido_nm_eng == '':
            continue

        if row[5].value is not None:
            # None                                   # A = row[0]
            sido_cd        = str(row[1].value)[:-2]  # B = row[1] 대분류 ex) 11.0
            sido_nm        = row[2].value            # C = row[2] 시도   ex) 서울특별시
            sigungu_cd     = str(row[3].value)[:-2]  # D = row[3] 중분류    
            sigungu_nm     = row[4].value            # E = row[4] 시군구    
            adm_dr_cd      = str(row[5].value)[:-2]  # F = row[5] 소분류 == 행정구역코드 ex) 1101053.0
            adm_dr_nm      = row[6].value            # G = row[6] 읍면동     
            adm_dr_nm_eng  = row[7].value            # H = row[7] 영문표기
            adm_dr_nm_cn   = row[8].value            # I = row[8] 한자표기
            mydict[adm_dr_cd] = [sido_nm, sigungu_nm, adm_dr_nm, sido_nm_eng, sigungu_nm_eng, adm_dr_nm_eng, sido_nm_cn, sigungu_nm_cn, adm_dr_nm_cn, sido_cd, sigungu_cd]

    sheet = wb.sheet_by_name('법정동코드 연계 자료분석용')
    for i in range(sheet.nrows):
        row = sheet.row(i)
        # A = row[0]  시도
        # B = row[1]  시군구
        # C = row[2]  행정구역명
        # D = row[3]  행정동(행정기관명)
        # E = row[4]  법정동
        # F = row[5]  행정구역코드    <-- adm_dr_cd
        # G = row[6]  행정기관코드
        # H = row[7]  행정기관 생성일
        # I = row[8]  법정동 코드
        # J = row[9]  관할지역
        # K = row[10] 행정동 영문명칭
        # L = row[11] 비고
        try:
            adr_dr_arr = mydict[str(row[5].value)[:-2]]
            if len(adr_dr_arr) > 11:
                continue
            else:
                adr_dr_arr.append(str(row[6].value)[:-2])
        except IndexError: # list index out of range
            pass
        except KeyError:
            pass
        
def insert_into_mysql():
    '''
    mysql> desc korea_dong_pg_tbl;
    +----------------+-------------+------+-----+---------+-------+
    | Field          | Type        | Null | Key | Default | Extra |
    +----------------+-------------+------+-----+---------+-------+
    | adm_dr_cd      | varchar(7)  | NO   | PRI | NULL    |       |
    | sido_nm        | varchar(45) | NO   |     | NULL    |       |
    | sigungu_nm     | varchar(45) | NO   |     | NULL    |       |
    | adm_dr_nm      | varchar(45) | NO   |     | NULL    |       |
    | sido_nm_eng    | varchar(45) | YES  |     | NULL    |       |
    | sigungu_nm_eng | varchar(45) | YES  |     | NULL    |       |
    | adm_dr_nm_eng  | varchar(45) | YES  |     | NULL    |       |
    | sido_nm_cn     | varchar(45) | YES  |     | NULL    |       |
    | sigungu_nm_cn  | varchar(45) | YES  |     | NULL    |       |
    | adm_dr_nm_cn   | varchar(45) | YES  |     | NULL    |       |
    | sido_cd        | varchar(2)  | NO   |     | NULL    |       |
    | sigungu_cd     | varchar(5)  | NO   |     | NULL    |       |
    | adm_dr_cd2     | varchar(11) | NO   |     | NULL    |       |
    | geom           | geometry    | YES  |     | NULL    |       |
    | updated        | datetime    | NO   |     | NULL    |       |
    +----------------+-------------+------+-----+---------+-------+
    '''
    conn = pymysql.connect(host='주소', user='사용자명', password='암호', db='DB명', charset='utf8')
    curs = conn.cursor()

    for key in mydict:
        arr = mydict[key]
        korea_nm  = "\"%s\", \"%s\", \"%s\"" % (arr[0], arr[1], arr[2])
        eng_nm    = "\"%s\", \"%s\", \"%s\"" % (arr[3], arr[4], arr[5])
        cn_nm     = "\"%s\", \"%s\", \"%s\"" % (arr[6], arr[7], arr[8])
        codes     = "\"%s\", \"%s\", \"%s\"" % (arr[9], arr[10], arr[11])
        sql = "INSERT INTO korea_dong_pg_tbl VALUES(\"%s\", %s, %s, %s, %s, NULL, now());"\
            % (key, korea_nm, eng_nm, cn_nm, codes)
        #print(sql)
        curs.execute(sql)

    conn.commit()
    conn.close()


import_xlsfile("{파일경로}/한국행정구역분류_2019.7.1.기준(F)_추가반영.xls")
insert_into_mysql();

 

결과

 

 

[참고]

http://www.python-excel.org/

https://xlrd.readthedocs.io/en/latest/api.html

https://openpyxl.readthedocs.io/en/stable/tutorial.html#values-only