일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
- Flutter
- configmap
- HLS
- docker
- namespace
- golang
- wireshark
- macos
- Windows10
- aws
- aws cli
- Python
- service
- ffmpeg
- Java
- RTMP
- ebpf
- kubectl
- nginx-media-server
- VSCode
- deployment
- Shell script
- Sysinternals
- 행정구역분류
- Kubernetes
- Pod
- spring cloud config
- Android
- android studio
- dart
- Today
- Total
woonizzooni
[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();
결과
[참고]
https://xlrd.readthedocs.io/en/latest/api.html
https://openpyxl.readthedocs.io/en/stable/tutorial.html#values-only
'Programming > Python' 카테고리의 다른 글
locust 실행시 jinja2.ext.do의 KeyError: 'extensions' 에러 (0) | 2021.05.12 |
---|---|
[Python] dict -> ['(key, value)'] sorted array 만들기 (value로 > 그 이후 key로) (0) | 2020.10.10 |
PyQt5 설치 (Windows 환경에서) (0) | 2019.08.24 |
대한민국 행정동 경계 좌표 추출 #2 - python > GeoJSON (1) | 2019.08.23 |
[PyProj] Proj executable not found. Please set PROJ_DIR variable. (0) | 2019.08.22 |