'2024/01'에 해당되는 글 1건

  1. 2024.01.12 사진 카메라별 촬영일별 정리 by Python + ExifTool + MariaDB

백업 디렉토리에 /카메라기종/YYYY/YYYY-MM-DD 로 파일이 복사됨.

카메라기종을 Metadata에서 추출 못 하면 /YYYY/YYYY-MM-DD 로 파일이 복사됨.

시놀로지에서 장시간 작업이라 /제어판/작업 스케쥴러 에 등록해서 사용

 

common_utils.py

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

def json_default(value):
    if isinstance(value, datetime.date):
        return value.strftime('%Y:%m:%d')

    raise TypeError('not JSON serializable')


# 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
    exif_model    = ""

    #with open(file_path, 'rb') as f:
    with exiftool.ExifToolHelper() as et:
        #metadata = exifread.process_file(f)
        array = et.get_metadata(file_path)

        metadata = json.dumps(array[0])
        exif_str = json.loads(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']
            # if 'JPEGThumbnail'  in metadata:
            #     del metadata['JPEGThumbnail']

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

            if "EXIF:DateTimeOriginal" in exif_str:                     # 사진 촬영할 일자
                #print(exif_str["EXIF:DateTimeOriginal"])
                exif_date_str = exif_str["EXIF:DateTimeOriginal"]
            if "QuickTime:CreateDate" in exif_str:                      # 동영상 촬영할 일자
                #print(exif_str["QuickTime:CreateDate"])
                exif_date_str = exif_str["QuickTime:CreateDate"]
            if "File:FileModifyDate" in exif_str:                       # 파일 수정 일자
                #print(exif_str["File:FileModifyDate"])
                file_date_str = exif_str["File:FileModifyDate"]
            if "File:FileSize" in exif_str:                             # 파일 사이즈
                #print(exif_str["File:FileSize"])
                file_size = exif_str["File:FileSize"]
            if "EXIF:Model" in exif_str:                                # 사진 촬영 기종
                #print(exif_str["EXIF:Model"])
                exif_model = exif_str["EXIF:Model"]
            if "QuickTime:Model" in exif_str:                           # 동영상 촬영할 일자
                #print(exif_str["QuickTime:Model"])
                exif_model = exif_str["QuickTime:Model"]

            if (exif_date_str == '') and (file_date_str == ''):
                print(exif_str)
            else:
                #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

        # 중복제거용으로(사이즈 큰 파일을 읽으면 수행속도저하)
        if (file_size <= 1024000000):
            f = open(file_path, 'rb')
            data = f.read()
            f.close

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

        exif_str = json.dumps(metadata, indent="\t", default=json_default)

    return create_date, exif_str, hash_str, exif_model

 

photo_copy_by_camera_date.py

###############################################################################
## PHOTO COPY BY META DATA
## PARAM1 : SKIP COUNT (오류시 재작업할 경우 처리된 건수 제외용)
## PARAM2 : 사진을 정리할 디렉토리
## 
###############################################################################

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/backup_photo/" # 복사할 디렉토리
dest_path        = ''
target_date      = ''
target_file_name = ''
ext_list         = (".JPG", ".JPEG", ".PNG", ".NEF", ".HEIC", ".3GP", ".MOV", ".MP4", ".DNG", ".TIF", ".TIFF", ".CR2", ".CRW", ".RW2")
skip_count       = 0

conn = pymysql.connect(host='db호스트명', user='db유저명', password='db비밀번호', db='db명', charset='utf8', port=db포트)


c = conn.cursor()
iCnt = 0

## 제외처리할 카운드 (오류났을때 건너띄기용)
if (sys.argv[1] != ''):
    skip_count = int(sys.argv[1])

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

print("#######################################")
print(" Source Directory : " + dir_path        )
print(" Target Directory : " + go_path         )
print(" Skip Count       : %d" % skip_count    )
print("#######################################")

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)
                target_file_name    = file_name.upper()

                #print("FILE LOCATION [ %d ]: %s"  % (iCnt, file_path))
                #print("FILE     SIZE [ %d ]: %d " % (iCnt, file_size))

                if (skip_count > iCnt):
                    skip_yn = 'Y'
                    iCnt = iCnt + 1
                else:
                    skip_yn = 'N'

                if (skip_yn == 'N'):
                    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   = ''
                    exif_model  = ''

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

                            #print(exif_str)

                            if (len(target_date) == 10):
                                if (len(exif_model) > 0):
                                    dest_path = go_path + "/" + exif_model + "/" + target_date[0:4] + "/" + target_date+  "/"
                                else:
                                    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)

                                # 복사할 파일이 존재할 경우
                                if (os.path.exists(dest_path + target_file_name)):

                                    target_size = os.path.getsize(dest_path + target_file_name)

                                    if (target_size == file_size):
                                        #print("### 동일한 파일이 존재하므로 SKIP!! :" + file_path)
                                        copy_str = 'S'
                                    elif (target_size > file_size):
                                        #print("### 사이즈 큰 파일이 존재하므로 존재하므로 SKIP!! :" + file_path)
                                        copy_str = 'O'
                                    else:
                                        shutil.copy2(file_path, dest_path + target_file_name)
                                        copy_str = 'Y'
                                else:
                                    shutil.copy2(file_path, dest_path + target_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 + target_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 + target_file_name)
                            copy_str = 'N'

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

                        param2 = (file_path, target_file_name, copy_str, dest_path + target_file_name, file_size, hash_str, exif_str.replace("'", ""))
                        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;
Posted by 파파울프™
,