파파울프의 일상 웹 저장소

사진정리 sqlite3에서 mariaDB로 변경

ExifTool 설치 참고 블로그 : https://devst.tistory.com/108

common_utils.py

import os, datetime, sys
import exiftool
import hashlib
import json


# exiftool 사용 함수
def get_exif_info(file_path):

    #print("FILE LOCATION : " + file_path)
    create_date    = ""
    metadata       = {}
    format_str1    = '%Y:%m:%d %H:%M:%S'
    format_str2    = '%d/%m/%Y %H:%M'
    exif_date_str = ""
    file_date_str = ""
    hash_str      = ""
    file_size     = 0

    with exiftool.ExifTool() as et:
        metadata = et.get_metadata(file_path)

        #print(len(metadata))

        if len(metadata) > 0: 

            #print(metadata)

            # DB등록시 오류나는 항목 삭제 (특수기호, 깨지는 한글 등등)
            if 'EXIF:UserComment' in metadata:
                del metadata['EXIF:UserComment']
            if 'QuickTime:CompressorName' in metadata:
                del metadata['QuickTime:CompressorName']
            if 'ExifTool:Warning' in metadata:
                del metadata['ExifTool:Warning']
            if 'MakerNotes:AFPointsInFocus1D'  in metadata:
                del metadata['MakerNotes:AFPointsInFocus1D']

            for tag in metadata.keys():
                #print("Key: %s, value %s" % (tag, metadata[tag]))
                if tag in ('EXIF:DateTimeOriginal'): # 사진 촬영할 일자
                    #print("Key: %s, value %s" % (tag, metadata[tag]))
                    exif_date_str = str(metadata[tag])
                if tag in ('QuickTime:CreateDate'):  # 동영상 촬영 일자
                    #print("Key: %s, value %s" % (tag, metadata[tag]))
                    exif_date_str = str(metadata[tag])
                if tag in ('File:FileModifyDate'): # 카톡 등으로 받은 사진들
                    #print("Key: %s, value %s" % (tag, metadata[tag]))
                    file_date_str = str(metadata[tag])
                if tag in ('File:FileSize'):
                    file_size = metadata[tag]

            if (exif_date_str == '') and (file_date_str == ''):
                for tag in metadata.keys():
                    print("Key: %s, value %s" % (tag, metadata[tag]))
            else:
                #print("exif_date_str" + exif_date_str)
                #print("exif_date_str" + exif_date_str)
                if ((exif_date_str == '') or (exif_date_str == '0000:00:00 00:00:00')) and (file_date_str != ''):
                    exif_date_str = file_date_str[0:19]

                try:
                    exif_date = datetime.datetime.strptime(exif_date_str, format_str1)
                    file_date = datetime.datetime.strptime(file_date_str[0:19], format_str1)

                    if (exif_date < file_date):
                        create_date = exif_date.strftime('%Y-%m-%d')
                    else :
                        create_date = file_date.strftime('%Y-%m-%d')
                    #print(create_date)
                except ValueError as ve:
                    exif_date = datetime.datetime.strptime(exif_date_str, format_str2)
                    file_date = datetime.datetime.strptime(file_date_str[0:19], format_str1)

                    if (exif_date < file_date):
                        create_date = exif_date.strftime('%Y-%m-%d')
                    else :
                        create_date = file_date.strftime('%Y-%m-%d')
            # Dictionary -> JSON
            exif_str = json.dumps(metadata)

        if (file_size <= 1024000000):
            f = open(file_path, 'rb')
            data = f.read()
            f.close

            hash_str = hashlib.sha256(data).hexdigest()

    return create_date, exif_str, hash_str

photo_exif_copy.py

import os, datetime, shutil, pymysql, sys
import exiftool
import common_utils as ut
from logging.config import dictConfig
import logging

## 정리할 사진, 동영상 디렉토리
dir_path = '' 

# 정리된 파일을 모을 디렉토리
go_path     = "/volume2/백업할디렉토리/"   ## 하위에 yyyy / yyyy-mm-dd 순으로 백업됨
dest_path   = ''
target_date = ''
ext_list    = (".JPG", ".JPEG", ".PNG", ".NEF", ".HEIC", ".3GP", ".MOV", ".MP4", ".DNG", ".TIF", ".TIFF", ".CR2", ".CRW", ".RW2")

conn = pymysql.connect(host='localhost', user='유저명', password='비밀번호', db='디비명', charset='utf8', port=3307)

c = conn.cursor()
iCnt = 0

## 정리할 사진, 동영상 디렉토리 없을 경우 파라미터로 지정
if (dir_path == ''):
    dir_path = sys.argv[1]

dictConfig({
    'version': 1,
    'formatters': {
        'default': {
            'format': '[%(asctime)s] %(message)s',
        }
    },
    'handlers': {
        'file': {
            'level': 'DEBUG',
            'class': 'logging.FileHandler',
            'filename': 'debug.log',
            'formatter': 'default',
        },
    },
    'root': {
        'level': 'DEBUG',
        'handlers': ['file']
    }
})


try:
    # 디렉토리 구조 돌면서 처리
    for (root, directories, files) in os.walk(dir_path) :
        for d in directories :
            d_path = os.path.join(root, d)
        #print(d_path)

        for file in files :
            file_path = os.path.join(root, file)
            #print(file_path)

            file_size = 0

            if "@eaDir" not in file_path:
                file_dir, file_name = os.path.split(file_path)
                file_dir, file_ext  = os.path.splitext(file_path)
                file_size = os.path.getsize(file_path)

                #print("FILE LOCATION : " + file_path)

                sql = "SELECT idx, file_loc, file_name, copy_yn, copy_loc, reg_date, mod_date FROM photo WHERE file_loc = '%s';" % (file_path)
                #print(sql)
                c.execute(sql)

                data1 = c.fetchone()

                copy_yn = 'N'
                copy_str = 'N'

                #print(data1)
                if (data1 == None):
                    copy_yn = 'N'

                elif (data1[3] == 'Y'):
                    update_sql = "UPDATE photo SET mod_date = NOW() WHERE idx = '%s';" % data1[0]
                    print(update_sql)
                    c.execute(update_sql)
                    conn.commit

                    copy_yn = 'Y'
                    print(file_path + " Already Done!")
                elif (data1[3] == 'N'):
                    copy_yn = 'U'

                target_date = ''
                exif_str    = {}
                hash_str    = ''
                dest_path   = ''

                # 파일 복사 대상이면 처리
                if (copy_yn != 'Y'):
                    # 정리할 파일 확장자 정의
                    if (file_ext.upper() in ext_list):
                        #target_date = get_exif_info(file_path)
                        target_date, exif_str, hash_str = ut.get_exif_info(file_path)

                        #print(exif_str)

                        if (len(target_date) == 10):
                            dest_path = go_path + "/" + target_date[0:4] + "/" + target_date+  "/"
                            #print(dest_path + " : " + str(len(target_date)))
                            if (os.path.isdir(dest_path) == False):
                                os.makedirs(dest_path)
                            shutil.copy2(file_path, dest_path + file_name)

                            copy_str = 'Y'
                        else:
                            dest_path = go_path + "/ERROR/"

                            if (os.path.isdir(dest_path) == False):
                                os.makedirs(dest_path)

                            shutil.copy2(file_path, dest_path + file_name)
                            copy_str = ''
                    else:
                        dest_path = go_path + "/ERROR/"

                        if (os.path.isdir(dest_path) == False):
                            os.makedirs(dest_path)

                        shutil.copy2(file_path, dest_path + file_name)
                        copy_str = 'N'

                    now = datetime.datetime.now()
                    nowStr = now.strftime('%Y-%m-%d %H:%M:%S')

                    param2 = (file_path, file_name, copy_str, dest_path + file_name, file_size, hash_str, exif_str)
                    insert_sql = "INSERT INTO photo (file_loc, file_name, copy_yn, copy_loc, file_size, hash, exif) VALUES ('%s', '%s', '%s', '%s', %s, '%s', '%s');" % param2
                    #print(insert_sql)
                    c.execute(insert_sql)

                #print("FILE LOCATION : " + file_path + " / " + dest_path + " / " + str(file_size))
                logging.debug("FILE LOCATION : " + file_path + " / " + dest_path + " / " + str(file_size))

                iCnt = iCnt + 1

                if (iCnt % 10) == 0:
                    conn.commit()
                    print("#######################################")
                    print(" %d : Commit!!!" % iCnt)
                    print("#######################################")

    conn.commit()

    print("#######################################")
    print(" %d : Commit!!!" % iCnt)
    print("#######################################")

except Exception as inst:
    print(insert_sql)
    logging.debug(inst)
    logging.debug("exif_str : " + exif_str)
    logging.debug("insert_sql : " + insert_sql)
finally:

    conn.close()

테이블 생성 SQL

CREATE TABLE `photo` (
  `IDX` int(10) NOT NULL,
  `FILE_LOC` varchar(300) DEFAULT NULL,
  `FILE_NAME` varchar(100) DEFAULT NULL,
  `COPY_YN` varchar(1) DEFAULT NULL,
  `COPY_LOC` varchar(300) DEFAULT NULL,
  `FILE_SIZE` bigint(20) DEFAULT NULL,
  `hash` varchar(100) DEFAULT NULL,
  `exif` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `REG_DATE` datetime DEFAULT current_timestamp(),
  `MOD_DATE` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;