[Python][SqlAlchemy][MariaDB] load data local infile - UUID 와 바이너리

해질녘·2022년 10월 20일

MariaDB SQLAlchemy python

SQL

목록 보기

7/9

[MariaDB] load data local infile - UUID 와 바이너리

문제

하루에 한 번 다음과 같은 작업을 수행하는 파이썬 코드를 작성하고자 한다.

db에 접근하여 UUID 정보를 가져온다.
파이썬 내부에서 OpenAPI를 호출하고 이를 파싱하고, 파이썬 내부에서도 UUID를 생성한다. pandas df 형태로 잠시 가지고 있는다.
UUID 여럿을 포함한 정보를 csv로 내보냈다가, 이 csv를 db에 적재한다.

이 과정에서 가장 문제가 되는 것은 UUID였다. 현재 DB에는 UUID가 binary(16) 형태로 저장이 되어 있다.

적절한 처리를 해 주지 않으면, 최종적으로 db에 적재되어 확인 가능한 UUID는 의도와 다르게 저장이 된다. (바이너리가 아닌 형태로 저장이 되는 것이다.)

해결

UUID 문제

UUID는 db에서 가져올 때 파이썬의 bytearray 형태로 가져오게 된다. 이를 hex 형태로 변형한 후, DB 적재 시 MariaDB의 UNHEX() 함수를 이용하여 다시 바이너리화 해준다.

# 파이썬 내부에서 생성한 UUID
import uuid

load_df['event_id'] = df['AA_YMD'].apply(lambda _: uuid.uuid4().hex)

파이썬 라이브러리인 uuid에서 uuid4를 이용하여 생성 후 hex화 하여 df에 저장

이때 df['AA_YMD'] 부분은 임의의 열 아무거나 잡아도 상관 없으며 데이터프레임에서 하나의 열에 대해 행을 반복해주기 위해 잡은 것.

# DB에 조회해서 가져온 UUID
load_df['teacher_id'] = teacher_id.hex()

이때의 teacher_id는 DB에서 불러온 결과물 (bytearray 타입)

바이트어레이의 내장 함수 .hex()를 불러서 헥스 형태로 저장

이렇게 하면 결과물 csv에는

c4aef7387ec54750aa3ff27c4f03fcb0

이런 문자열로 남게 된다.

LOAD DATA LOCAL INFILE

이제 csv 파일을 db에 데이터로서 적재해보자.

pandas에서 지원하는 csv_to_sql 함수로는 UNHEX()와 같은 데이터베이스 함수를 지원하지 않으므로 생 쿼리로 작성하기로 하였다.

쿼리

LOAD DATA LOCAL INFILE 'C:/Users/@@@@.csv'
REPLACE
INTO TABLE `dayevent`
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(@event_id, @start_date, @event_name)
SET event_id = UNHEX(@event_id),
    start_date = STR_TO_DATE(@start_date, '%Y-%m-%d %H:%i:%S'),
    event_type=@event_type

한줄씩 뜯어보면

LOAD DATA LOCAL INFILE
- 로컬 디렉토리 내의 파일. 파일 경로 문자열의 슬래시 형태에 주의 (윈도우)
REPLACE
- PK가 중복되는 칼럼이 이미 DB에 존재한다면 어떻게 할 것인가? replace를 쓰면 덮어쓰기 한다.
COLUMNS~
- csv니까 구분자가 , 이다
LINES TEMINATED BY
- 윈도우(CRLF)의 경우 \r\n 을 쓰고
- 리눅스 맥 등의 경우 (LF) \n이다.
- 로컬 개발 환경은 윈도우 였지만 이후 배포는 우분투에 했기 때문에 배포 시에는 \n으로 수정해주었다.
칼럼 선언
- 괄호 안에 칼럼과 변수를 선언함
SET
- 선언한 변수를 column 이름과 맞추어 대입해줌
- event_id = UNHEX(@event_id) 이렇게 해 주면 된다.
- STR_TO_DATE 부분은 별도 포스팅으로 남겼다.
- 일반 string은 단순 대입. 변수 선언 안 하고 그냥 둬도 되긴 할거

근데 난 쿼리를 SqlAlchemy 통해서 날릴 꺼야

from sqlalchemy import create_engine

db_conn_str = r'mysql+pymysql://' + USER + ':' + PASSWORD + '@' + HOST + r'/' + DATABASE + "?local_infile=1"
    engine = create_engine(db_conn_str)

DB 커넥션 스트링에 꼭 local_infile=1을 넣어주어야 한다. 이렇게 하지 않으면 DB에는 허용이 되어 있지 않아도 접속하는 클라이언트 쪽에서 디폴트로 차단이 된다.

전체 코드를 남겨 둔다.

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.orm import sessionmaker
import pymysql
import pandas as pd
from secret import HOST
from secret import USER
from secret import PASSWORD
from secret import DATABASE

def load_to_db(csv_path):
    
    # db 연결
    db_conn_str = r'mysql+pymysql://' + USER + ':' + PASSWORD + '@' + HOST + r'/' + DATABASE + "?local_infile=1"
    engine = create_engine(db_conn_str)
    
    Session = sessionmaker(engine)
    
    with Session() as session:
        session.execute("truncate dayevent;")
        session.commit()
        
        sql = r"LOAD DATA LOCAL INFILE '" + csv_path + r"' REPLACE INTO TABLE `dayevent` COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (@event_id, @start_date, @event_type, @teacher_id, @student_id, @student_name, @event_name) SET event_id = UNHEX(@event_id), teacher_id = UNHEX(@teacher_id), student_id = UNHEX(@student_id), lecture_id = UNHEX(@lecture_id), start_date = STR_TO_DATE(@start_date, '%Y-%m-%d %H-%i-%S'), event_type=@event_type, student_name=@student_name, event_name=@event_name"
        query = text(sql)
        
        print(query)
        session.execute(query)
        session.commit()
    
    engine.dispose()