python으로 excel 데이터 탭 별로 mysql DB로 이관 하기 (data migration)

python으로 excel 데이터 탭 별로 mysql DB로 이관 하기 (data migration)

python으로 excel 데이터 탭 별로 mysql DB로 이관 하기 (data migration)

오늘은 python을 이용하여 아주 간단하게 excel에 있는 데이터들을 DB로 이관 하는 방법에 대해서 확인 해보도록 하겠습니다. 우선, excel 파일을 하나 준비를 하고, mysql DB에 테이블을 생성 하는것부터 파이썬 코딩 까지 해보도록 하겠습니다. 따라오시죠!

엑셀 데이터 파일 준비

우선 nation.xlsx 라는 이름으로 엑셀 파일을 준비를 합니다.
그리고 텝을 두개를 만들겁니다.
다음과 같이 준비 해보시면 됩니다.

국가 탭

도시 탭

이렇게 엑셀 데이터 준비 하시고, 다음은 테이블을 생성 하겠습니다.

 

 

 

mysql DB 테이블 준비

위 두개의 탭, 국가와 도시 데이터를 넣을 테이블을 mysql DB에 생성 해줍니다.

테이블 생성

create table nation
(
    nation_cd      int          not null comment '국가코드',
    nation_name    varchar(100) not null comment '국가명',
    continent_name varchar(100) not null comment '대륙명',
    primary key (nation_cd)
) comment '국가';

create table city
(
    city_cd     int          not null comment '도시코드',
    city_name   varchar(100) not null comment '도시명',
    nation_name varchar(100) not null comment '국가명',
    primary key (city_cd)
) comment '도시';

위와 같이 생성을 했습니다.
테이블 생성 완료!

python 코딩하기

자, 모든게 준비가 되었으니 이젠 코딩만 하면 되겠습니다.
바로 시작할께요.

# Pandas
import pandas as pd

엑셀 파일을 불러와서 pandas를 이용하여 데이터를 읽을겁니다. import 하구요.

# db connect
import pymysql

conn = pymysql.connect(host='123.123.123.123', user='DB계정', password='비밀번호', db='excel')
curs = conn.cursor(pymysql.cursors.DictCursor)

그리고 mysql DB 연결을 위해서 위와 같이 pymysql 패키지를 import 해옵니다.
그리고 DB 서버의 IP, 계정, 비밀번호 등을 넣어주시면 됩니다.

# excel file load
excel_sheet_1 = pd.read_excel('D:\sTricky\excel_to_db\\nation.xlsx', sheet_name = '국가')
excel_sheet_2 = pd.read_excel('D:\sTricky\excel_to_db\\nation.xlsx', sheet_name = '도시')

 

 

 

엑셀 파일을 불러옵니다. 저 같은 경우는 nation 파일앞에 \ 가 붙을때 개행문자와 같기 때문에 \를 한번 더 넣어줍니다. 그리고, sheet_name 이라는 파라미터에 엑셀파일의 탭명을 써주시면 됩니다.

우리가 탭을 위에서 2개를 만들었기 때문에 excel_sheet_1, excel_sheet_2 로 pandas를 호출하여 데이터를 저장 하여 줍니다.

# db truncate tables
sql_truncate_1 = 'truncate table excel.nation'
sql_truncate_2 = 'truncate table excel.city'

curs.execute(sql_truncate_1)
curs.execute(sql_truncate_2)
conn.commit()

그리고, 데이터를 넣기전에 기존 데이터를 삭제 하고 넣어야 한다면 위와 같이 truncate table을 해줍니다. 만약, 계속 추가 하는 로직이라면 이 부분은 빠져도 되겠죠?

# data insert to db
sql_insert_1 = 'insert into excel.nation values(%s, %s, %s)'
for idx in range(len(excel_sheet_1)):
    	curs.execute(sql_insert_1, tuple(excel_sheet_1.values[idx]))
conn.commit()

sql_insert_2 = 'insert into excel.city values(%s, %s, %s)'
for idx in range(len(excel_sheet_2)):
    	curs.execute(sql_insert_2, tuple(excel_sheet_2.values[idx]))
conn.commit()

그리고, 두개의 insert 문을 만들어서 for loop를 이용해 위에서 만든 excel_sheet_1, excel_sheet_2 데이터를 읽어와 DB로 넣어줍니다. 이렇게 하나하나 insert 하는 방식도 있고, load data local infile 명령으로 한번에 밀어 넣을수도 있겠죠? 그건 다음에 다시 다루도록 하겠습니다.

자, 이렇게 오늘은 간단하게 엑셀에 있는 데이터를 mysql DB로 간단하게 넣는 방법을 알아보았습니다. 너무 간단하지 않나요? 할때마다 번거로웠던 작업을 이렇게 코딩을 통해서 쉽게 만들수 있습니다.

2021.06.30 - [DB엔지니어가 공부하는 python] - 네이버카페 파이썬 크롤링 데이터 간단한 분석 및 워드클라우드 예제

네이버카페 파이썬 크롤링 데이터 간단한 분석 및 워드클라우드 예제

2020.05.29 - [DB엔지니어가 공부하는 python] - 파이썬으로 GPS 두 위경도 좌표간 거리 구하기

파이썬으로 GPS 두 위경도 좌표간 거리 구하기

2021.07.06 - [DB엔지니어가 공부하는 python] - 구글 데이터 스튜디오 소개 및 mysql 연동 기본 사용방법 설명

구글 데이터 스튜디오 소개 및 mysql 연동 기본 사용방법 설명

2021.07.15 - [Database/mariaDB administrator] - mysql mariadb update join SQL 문법 쉬운 설명

mysql mariadb update join SQL 문법 쉬운 설명

2021.05.31 - [Database/mariaDB administrator] - mariadb mysql 특정 스키마 테이블 ddl 추출 프로그램 배포

mariadb mysql 특정 스키마 테이블 ddl 추출 프로그램 배포

다음에 더 좋은 내용으로 찾아 오겠습니다.

감사합니다.

by.sTricky