[Python] Python을 활용한 서로 다른 데이터베이스 쿼리 결과 비교 및 엑셀 매핑

데이터 분석이나 시스템 통합 작업에서는 서로 다른 데이터베이스에 저장된 정보를 통합하고 비교해야 하는 경우가 많습니다. 이번 글에서는 서로 다른 PostgreSQL 데이터베이스 쿼리 결과를 Python으로 가져와 비교하고, 엑셀 파일의 정보를 매핑하여 유사도를 계산하는 전체 과정을 설명합니다.

문제 정의

1. 두 개의 PostgreSQL 데이터베이스에서 각각 데이터를 쿼리합니다.
2. 쿼리 결과를 조인하여 하나의 데이터프레임으로 병합합니다.
3. 병합된 데이터프레임을 엑셀 파일과 비교하여 태그 정보를 매핑합니다.
4. 엑셀 파일에 있는 details 컬럼과 병합된 데이터의 description을 비교하여 유사도를 계산합니다.

데이터 처리 단계

1. PostgreSQL 데이터베이스 연결 및 쿼리 실행

서로 다른 데이터베이스에서 쿼리 결과를 가져오기 위해 psycopg2 라이브러리를 사용합니다.

import pandas as pd
import psycopg2

# 첫 번째 데이터베이스 연결
conn_db1 = psycopg2.connect(
    host='db1_host',
    database='db1_name',
    user='db1_user',
    password='db1_password'
)

# 두 번째 데이터베이스 연결
conn_db2 = psycopg2.connect(
    host='db2_host',
    database='db2_name',
    user='db2_user',
    password='db2_password'
)

# 첫 번째 데이터베이스에서 쿼리 실행
df_asset = pd.read_sql_query('SELECT asset_id, physical_name FROM asset', conn_db1)

# 두 번째 데이터베이스에서 쿼리 실행
df_parameter = pd.read_sql_query('SELECT id, physical_name FROM parameter', conn_db2)
df_model = pd.read_sql_query('SELECT asset_id, parameter_id FROM model', conn_db2)

2. 데이터 매핑 및 병합

asset_id와 parameter_id를 기준으로 데이터프레임을 병합합니다.

# 모델과 자산 데이터 병합
df_result = pd.merge(df_model, df_asset, how='left', on='asset_id')

# 매핑된 결과에 파라미터 데이터 병합
df_result = pd.merge(df_result, df_parameter, how='left', left_on='parameter_id', right_on='id')

3. 엑셀 파일 매핑

엑셀 파일에 있는 태그 정보를 가져와 병합된 데이터와 매핑합니다.

# 엑셀 파일 로드
df_excel = pd.read_excel('tag_name_file.xlsx')

# 엑셀 파일의 태그 이름과 병합된 데이터의 파라미터 이름을 기준으로 매핑
df_final = pd.merge(df_result, df_excel, how='left', left_on='physical_name_y', right_on='tag_name')

4. 문장 유사도 비교

엑셀 파일의 description과 병합된 데이터의 description 컬럼 간 유사도를 계산합니다. 문장 유사도 비교를 위해 문자 단위 N-gram 벡터화 방식을 사용합니다.

from sklearn.feature_extraction.text import CountVectorizer
from sklearn.metrics.pairwise import cosine_similarity

# 빈 값 처리
df_final['description_x'] = df_final['description_x'].fillna('')
df_final['description_y'] = df_final['description_y'].fillna('')

# 문자 단위 N-gram 벡터화
vectorizer = CountVectorizer(analyzer='char', ngram_range=(2, 4))
vectorized_data_x = vectorizer.fit_transform(df_final['description_x'].tolist())
vectorized_data_y = vectorizer.fit_transform(df_final['description_y'].tolist())

# 유사도 계산
similarity_matrix = cosine_similarity(vectorized_data_x, vectorized_data_y)

5. 최종 결과 저장

최종 결과는 device_name, metric_name, description, 유사 description, 유사도 컬럼으로 구성된 엑셀 파일로 저장합니다.

# 유사도 컬럼 추가
df_final['similarity'] = similarity_matrix.diagonal()

# 최종 결과 저장
df_final.to_excel('final_result.xlsx', index=False)

핵심 요약

• 서로 다른 데이터베이스의 쿼리 결과를 Python으로 가져와 병합합니다.
• 엑셀 파일의 태그 정보와 병합된 데이터를 매핑하여 추가 정보를 연결합니다.
• 문자 단위 N-gram 벡터화 방식을 활용하여 description 컬럼 간 유사도를 비교하고, 최종 결과를 엑셀 파일로 저장합니다.