1. 테스트 데이터가 필요하게 된 계기
국비수업에서 팀원들과 상의하여 학습 목적으로 호텔 예약 웹 애플리케이션을 구현하기로 했다. 프로젝트 기간이 짧기 때문에 사업자가 업장을 등록하는 기능은 고려하지 않았다. 그래서 숙박업소 테스트 데이터를 가져오기로 했다. 공공데이터포털에서 지역별 숙박업소 총 71개의 csv 파일, 총 15,591개의 데이터를 받았다.
2. mysql setting
우선 값을 저장할 table의 구조와 제약조건을 정의한다.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| latitude | double | YES | NULL | ||
| longitude | double | YES | NULL | ||
| category_id | bigint | NO | MUL | NULL | |
| grade | bigint | YES | MUL | NULL | |
| id | bigint | NO | PRI | NULL | auto_increment |
| address | varchar(255) | YES | NULL | ||
| name | varchar(255) | YES | MUL | NULL |
3. python으로 데이터 입력
import pandas as pd
import mysql.connector
# 데이터베이스 연결 설정
conn = mysql.connector.connect(
host='${MYSQL_HOST}',
port=${MYSQL_PORT},
user='${MYSQL_USER}',
password='${MYSQL_PASSWORD}',
database='${MYSQL_DATABASE}'
)
cursor = conn.cursor(buffered=True)
# CSV 파일 로드
df = pd.read_csv('${CSV_PATH}')
for index, row in df.iterrows():
name = row['명칭']
address = str(row['시도명']) + " " + str(row['시군구명']) + " " + str(row['도로명']) + " " + str(row['건물번호'])
latitude = row['위도']
longitude = row['경도']
# 중복 검사 쿼리
cursor.execute("SELECT COUNT(*) FROM accomodation WHERE name = %s AND address = %s", (name, address))
if cursor.fetchone()[0] == 0: # 중복이 없으면 삽입
# 데이터 삽입
cursor.execute("INSERT INTO accomodation (name, address, latitude, longitude) VALUES (%s, %s, %s, %s)", (name, address, latitude, longitude))
else:
print(f"Duplicate entry not inserted: {name}, {address}")
conn.commit()
cursor.close()
conn.close()
우선 conn 변수에 mysql을 연결, 데이터베이스에서 SQL문을 실행하고 결과를 가져올 수 있는 cursor 객체를 생성. buffered=True로 설정하면, 커서가 모든 결과를 클라이언트 측에서 버퍼링한다. 즉, 쿼리를 실행한 후 전체 결과 집합을 메모리에 로드한 다음, 한 번에 가져올 수 있다.
이렇게 하면 결과를 하나씩 페치하지 않고, 결과 집합을 반복문을 사용해 여러 번 가져와야 하는 경우 발생할 수 있는 문제를 방지할 수 있다.
pandas로 csv파일에 있는 값을 읽어서 가져온 후 mysql에 값을 저장할 조건이 충족되면 저장하도록 한다.
참고로 csv의 컬럼명은 파일마다 다르기 때문에 파일에 맞는 컬럼명으로 바꿔줘야한다.
4. Spring boot에 연결
이 후 MySQL Server를 spring boot와 연결하면 끝이다.
5. 문제점
해당 데이터는 집에 있는 윈도우 컴퓨터 mysql 서버에 저장하였다. DDNS 및 포트포워딩 설정을 하여 Spring boot와 연동하여 사용하는 중이었는데 몇가지 문제점이 발생했다.
1. 팀원들과 작업시간이 겹치면 작업 충돌이 일어나 데이터 무결성을 위반한다.
2. Spring boot MySQL Connector 설정에 따라 database가 초기화될 수 있다. 물론 백업은 해두었지만 매번 해주는 것은 번거롭다.
3. 집 컴퓨터를 종료하면 팀원들이 개발할 수 없다. 이를 위해 24시간 컴퓨터를 켜두긴 했지만, 가끔씩은 꺼주어야하기 때문에 개발하기엔 부적절한 환경이다.
6. 해결책
1. docker, docker-compose를 활용해서 개발환경을 분리.
2. initializer를 사용해서 테스트 데이터 생성.
다음 글에서 설명하겠다.
Server Developer