Create a database of exif data

Create a database of exif data from photos using pyexiv2 and save it in a sqlite database for futher query:

#!/usr/bin/env python
 
import os, sys, pyexiv2, sqlite3
 
thumbFormats = ('JPG', 'JPEG')
rawFormats = ('ARW', 'CR2')
formats = thumbFormats + rawFormats
tags = ['Exif.Image.LensInfo']
dbFile = 'metadata.db'
 
conn = None
cursor = None
 
def parse(dir):
    cnt = 0
    for root, dirs, files in os.walk(dir, topdown=False, followlinks=True):
        refFiles = map(str.upper, files)
        for file in files:
            fullPath = os.path.join(root, file)
            if not os.path.islink(fullPath):
                refFile = file.upper()
                if refFile.endswith(formats):
                    #skip jpgs if raw exists
                    skip = False
                    if refFile.endswith(thumbFormats):
                        refFileBase = '.'.join(refFile.split('.')[:-1])
                        for r in rawFormats:
                            rFile = '%s.%s' % (refFileBase, r)
                            if rFile in refFiles:
                                skip = True
                                print('Skipping %s. %s exists!' % (fullPath, r))
                                break
                    if not skip:
                        global cursor
                        cursor.execute("insert into photo_file(name, path) values (?, ?)", (file, fullPath))
                        meta = processFileMeta(fullPath, cursor.lastrowid)
                        cursor.executemany("insert into metadata(photo_file_fk, tag, value) values (?, ?, ?)", meta)
                        cnt = cnt + 1
                        if cnt > 50:
                            conn.commit()
                            cnt = 0
            else:
                print('Link %s' % fullPath)
 
 
def processFileMeta(fullPath, fid):
    meta = []
    print('Processing %s' % fullPath)
    metadata = pyexiv2.ImageMetadata(fullPath)
    metadata.read()
    for tag in metadata.exif_keys:
        meta.append((fid, tag, metadata[tag].raw_value))
    return meta
 
def initDb():
    global cursor
    cursor = conn.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS PHOTO_FILE(id INTEGER PRIMARY KEY, name TEXT, path TEXT)""")
    cursor.execute("""CREATE TABLE IF NOT EXISTS METADATA(tag TEXT, value TEXT, PHOTO_FILE_FK INTEGER, FOREIGN KEY(PHOTO_FILE_FK) REFERENCES PHOTO_FILE(id))""")
    conn.commit()
 
if __name__ == "__main__":
    conn = sqlite3.connect(dbFile)
    initDb()
    parse(sys.argv[1])
    conn.commit()
    conn.close()
 
"""
Example query: which type of camera I used most?
select count(m.value), m.value from photo_file p, metadata m where m.photo_file_fk = p.id  and m.tag =  'Exif.Image.Model' group by m.value;
"""

Leave a Reply

*