Loop through database and run shell commands with Python and exiftool

Briefly, I'm looking at getting the code below to execute faster. I have 100k images to go through. I'm running a query against MySQL, looping through results and then running exiftool against an image, then moving it.

I started running it and it quickly became evident it wouldn't be a quick thing :-(

import mysql.connector
import os

cnx = mysql.connector.connect(user='root',database='database', password='password')
cursor = cnx.cursor()

query = ("SELECT post_title,Event,File,Name from a order by File")

cursor.execute(query)

def shellquote(s):
    return s.replace("'", "")

for (post_title, Event,File,Name) in cursor:
    olddir = r'/home/alan/Downloads/OLD/'
    newdir = r'/home/alan/Downloads/NEW/' + post_title

    oldfile = olddir + File
    newfile = newdir + "/"+File

    if not os.path.exists(newfile):
        os.makedirs(newfile)
    if os.path.isfile(oldfile): 
        print " > PROCESSING: " + oldfile
        os.system("exiftool -q "+shellquote(oldfile)+" -xmp:title='"+shellquote(post_title)+"'")
        os.system("exiftool -q "+shellquote(oldfile)+" -xmp:description='"+shellquote(Name)+" courtesy of https://www.festivalflyer.com'")
        os.system("exiftool -q "+shellquote(oldfile)+" -description='"+shellquote(Name)+" courtesy of https://www.festivalflyer.com'")
        os.rename(oldfile, newfile)
cursor.close()
cnx.close()

I tried using subprocess but for whatever reason, I didn't get it to run. Any advice is welcome.

I suppose I could move the 3 lines of exiftool commands to just one and pass multiple arguments. I also saw -stay_open as an option to exiftool but not sure how to apply it

• Close your connection to your database, even if there's an error. Use a try-finally to do this.

• Make some functions, moving the database stuff into it's own function makes it much easier to read.

• From os.system docs:

The subprocess module provides more powerful facilities for spawning new processes and retrieving their results; using that module is preferable to using this function. See the Replacing Older Functions with the subprocess Module section in the subprocess documentation for some helpful recipes.

• You may want to use os.path for various file related things, such as os.path.join to join path sections.

• print is slow. Try removing it for a massive speed up.

And so you may want to start changing your code to look like:

import mysql.connector
import os
import subprocess

def read_database():
    cnx = mysql.connector.connect(user='root', database='database', password='password')
    cursor = cnx.cursor()
    try:
        query = ("SELECT post_title,Event,File,Name from a order by File")
        cursor.execute(query)
        for item in cursor:
            yield item
    finally:
        cursor.close()
        cnx.close()

def main():
    path = os.path
    old_dir = r'/home/alan/Downloads/OLD/'
    new_dir = r'/home/alan/Downloads/NEW/'
    for (post_title, event, file_name, name) in read_database():
        old_file = path.join(old_dir, file_name)
        new_file = path.join(new_dir, post_title, file_name)
        if not path.exists(new_file):
            os.makedirs(new_file)
        if path.isfile(old_file):
            subprocess.call(["exiftool", "-q", old_file, "-xmp:title='" + post_title.replace("'", "") + "'"])
            subprocess.call(["exiftool", "-q", old_file, "-xmp:description='" + name.replace("'", "") + " courtesy of https://www.festivalflyer.com'"])
            subprocess.call(["exiftool", "-q", old_file, "-description='" + name.replace("'", "") + " courtesy of https://www.festivalflyer.com'"])
            os.rename(old_file, new_file)

if __name__ == '__main__':
    main()

StackExchange Code Review Q#161178, answer score: 4