How can I batch add keywords (metadata) to JPGs, with matching?

Question

I have a weird situation in on my hands and hope to find some direction/hints...

The ultimate goal is to have a Lightroom catalog with about 30 000 keyworded images in it.

The images are all JPEG, inside a single folder, each with unique numerical name, like 123456789.jpg.

The keywords are inside a mysql database and I can dump them and/or process the list in whatever format needed (for example tab/space separated "filename"-"keywords" value).

I know I can import keywords from text files in LR, but that won't match them to the images, AFAIK. So I presume I have to first run some kind of EXIF batch to match filename to keyword set and insert the keywords in JPEG files. After that I hope LR will recognize the EXIF during import (I've tested that and it seems to work).

So that's about it. Any help appreciated!

Answer

You could export the tags as a batch script that runs exiftool to add the keywords to the JPEG files:

exiftool -keywords+=banana 111.jpg
exiftool -keywords+=fruit 111.jpg
exiftool -keywords+=tree 112.jpg
...

with a query resembling this:

select concat('exiftool -keywords+=',KEYWORD,' ',FILE_NUMBER,'.jpg') from IMAGE

and then

• backup your images
• run the generated batch script
• import the images into Lightroom

Check more discussion of this question.