[반려동물 프로젝트] DB 구성 (JPA, Python)

쿼리해서 프론트로 보내주기만하면 되기 때문에

데이터만 잘 셋팅해두면 그 이후에는 간단할 거라고 생각한다.

(프론트 빼고...)

 

 

DTO 생성

 

가장 먼저 JPA 를 사용해 테이블을 생성한다. (참고)

테이블은 초기에 생각했던 테이블 구성으로 진행했다.

 

 

 

데이터 삽입

 

API가 업데이트되고 있는지 의심스럽기 때문에

일단은 파일을 다운로드해서 데이터를 넣기로 했다.

 

 

import pandas as pd
from sqlalchemy import create_engine

def get_engine(db_info):
    return create_engine(f"mysql+pymysql://{db_info['user']}:{db_info['password']}@{db_info['host']}:{db_info['port']}/{db_info['database']}?charset=utf8mb4")

path = "동물등록현황.csv"
csv = pd.read_csv(path, encoding='cp949')

# get database engine
engine = get_engine({
    'user': 'user',
    'password': 'user',
    'host': 'localhost',
    'port': 3306,
    'database': 'pet_stats',
})

# rename column
csv = csv.rename(columns={
    '시도명': 'city',
    '시군구명': 'province',
    '생년': 'birth_year',
    'RFID구분': 'rfid_type',
    '축종': 'species',
    '품종': 'animal_type',
    '마릿수': 'animal_count',
})

# fill na value
csv['animal_type'] = csv['animal_type'].fillna('알수없음')
csv['birth_year'] = csv['birth_year'].fillna(0)

# insert region, species, rfid_type
(csv[['city', 'province']]
 .drop_duplicates()
 .to_sql('region', con=engine, if_exists='append', index=False))
(csv[['species']]
 .drop_duplicates().rename(columns={'species': 'name'})
 .to_sql('species', con=engine, if_exists='append', index=False))
(csv[['rfid_type']]
 .rename(columns={'rfid_type': 'name'}).drop_duplicates()
 .to_sql('rfid_type', con=engine, if_exists='append', index=False))

# insert animal_type with species
species_df = pd.read_sql("SELECT id as species_id, name as species_name FROM species", con=engine)
csv_merged = pd.merge(csv, species_df, how='left', left_on=['species'], right_on=['species_name'])
csv_merged[['animal_type', 'species_id']].rename(columns={'animal_type': 'name'}).drop_duplicates().to_sql('animal_type', con=engine, if_exists='append', index=False)
print(f"1. len: {len(csv_merged)}")


# insert animal_stats with species, region, animal_type, rfid_type
region_df = pd.read_sql("SELECT id as region_id, province as region_province, city as region_city FROM region", con=engine)
csv_merged = pd.merge(csv_merged, region_df, how='left', left_on=['province', 'city'], right_on=['region_province', 'region_city'])
print(f"2. region merged - len: {len(csv_merged)}")

animal_type_df = pd.read_sql("SELECT a.id as animal_type_id, a.name as animal_type_name, b.name as species_name FROM animal_type a left join species b on a.species_id = b.id", con=engine)
csv_merged = pd.merge(csv_merged, animal_type_df, how='left', left_on=['animal_type', 'species'], right_on=['animal_type_name', 'species_name'])
print(f"3. animal_type merged - len: {len(csv_merged)}")

rfid_type_df = pd.read_sql("SELECT id as rfid_type_id, name as rfid_type_name FROM rfid_type", con=engine)
csv_merged = pd.merge(csv_merged, rfid_type_df, how='left', left_on=['rfid_type'], right_on=['rfid_type_name'])
print(f"4. rfid_type merged - len: {len(csv_merged)}")

# insert animal_stats
(csv_merged[['birth_year', 'animal_count', 'rfid_type_id', 'region_id', 'animal_type_id']].to_sql('animal_stats', con=engine, if_exists='append', index=False))

# 중복 데이터 확인
# csv_merged[csv_merged.duplicated(subset=['birth_year', 'animal_count', 'rfid_type_id', 'region_id', 'animal_type_id'], keep=False)]

csv_merged

외래키 의존 순서대로 가공해서 넣어줬다.

잘못 조인해서 데이터가 늘어나는 문제가 있어서, print 로 데이터 개수 로깅하면서 진행함.

 

 

 

테이블에 데이터 넣으면서 DTO 구성 시 누락한 사항은 아래와 같다.

• 테이블 설정 누락
  • 유니크키
  • id auto_increment 설정

JPA 가 익숙치 않아 테이블에 필요한 설정들을 누락하게되는 듯하다.

 

 

 

select s.id, r.province, r.city, s.birth_year, rt.name as rfid_type, sp.name as species, at.name as animal_type, s.animal_count
from animal_stats s
left join animal_type at
    on s.animal_type_id = at.id
left join region r
    on s.region_id = r.id
left join rfid_type rt
    on s.rfid_type_id = rt.id
left join species sp
    on at.species_id = sp.id

전체 데이터 조인은 위와같이 하면 된다.

 

 

 

쿼리 조인은 어떻게?

문득 테이블을 나누긴 했는데, JPA 에서 쿼리 조인이 많을 경우 어떻게하면 좋을지 고민이 됐다.

기존에 MyBatis 사용할 땐 위에처럼 바로 쿼리 짜면 됐는데, JPA 는 알아보니 가독성이 별로인 것 같다.

 

1. JPQL + @Query

@Query("""
SELECT new com.example.dto.RfidBreedStatDto(
    rt.name,
    at.name,
    SUM(s.animalCount)
)
FROM AnimalStats s
JOIN s.rfidType rt
JOIN s.animalType at
JOIN at.species sp
WHERE s.birthYear BETWEEN :startYear AND :endYear
  AND sp.name = :speciesName
GROUP BY rt.name, at.name
ORDER BY rt.name, SUM(s.animalCount) DESC
""")
List<RfidBreedStatDto> findRfidBreedStatsByFilter(
    @Param("startYear") Integer startYear,
    @Param("endYear") Integer endYear,
    @Param("speciesName") String speciesName
);

쿼리 조인이 많으면 이 방식을 사용하는 것 같다.

하지만 MyBatis 가 익숙한 나한테는 쿼리 관리하는게 어려워 보인다.

일단 이 방법은 파라미터 유무에 따른 동적쿼리 작성이 안된다.

 

2. QueryDSL

public List<RfidBreedStatDto> findRfidBreedStatsByDynamicCondition(
        Integer birthYear,
        String speciesName
) {
    QAnimalStats stats = QAnimalStats.animalStats;
    QAnimalType animalType = QAnimalType.animalType;
    QRfidType rfidType = QRfidType.rfidType;
    QSpecies species = QSpecies.species;

    return queryFactory
            .select(Projections.constructor(RfidBreedStatDto.class,
                    rfidType.name,
                    animalType.name,
                    stats.animalCount.sum()
            ))
            .from(stats)
            .join(stats.rfidType, rfidType)
            .join(stats.animalType, animalType)
            .join(animalType.species, species)
            .where(
                birthYear != null ? stats.birthYear.eq(birthYear) : null,
                speciesName != null ? species.name.eq(speciesName) : null
            )
            .groupBy(rfidType.name, animalType.name)
            .orderBy(rfidType.name.asc(), stats.animalCount.sum().desc())
            .fetch();
}

동적쿼리를 사용하려면 이렇게 해야하는데

내 기준 쿼리로 한눈에 파악이 안돼서 가독성은 별로이나, 가장 유지보수가 좋아보인다.

또한 문법오류 같은 경우, @Query 는 검사가 안되는데 QueryDSL은 컴파일 과정에서 잡히기 때문에 오류 파악이 쉽다.

 

 

아마 QueryDSL 로 진행할 것 같긴한데, 이거는 패키지를 어떻게 구성해야하는지 찾아봐야겠다.