Recursive Searching and MySql Comparison
Question: Good evening. I am looking at developing some code that will collect EXIF data
from JPEG images and store it in a MySQL database using Python v2.x The
stumbling block lies in the fact that the JPEGs are scattered in a number of
subdirectories and further subdirectories in a root folder so for example 200
JPEGs may be stored in root > subroot > subsubroot1 as well as a further 100
in root > subroot > subroot2. Once all images are identified, they will be
scanned and their respective EXIF data abstracted before being added to a
MySQL table.

At the moment I am just at the planning stage but I am just wondering, what
would be the most efficient and pythonic way to carry out the recursive
searching? I am looking to scan the root directory and append any new
identified subdirectories to a list and then scan all subdirectory paths in
the list for further subdirectories until I have a total list of all
directories. This just seems to be a clumsy way though IMHO and a bit
repetitive so I assume there may be a more OOP manner of carrying out this
function.

Similarly, I am only looking to add new info to my MySQL table and so what
would be the most efficient way to establish if an entry already exists? The
filename both in the table and the JPEG file name will be its MD5 hash values.
I was considering scanning through the table at the beginning of the code and
placing all filenames in a set and so, before scanning a new JPEG, if an entry
already exists in the set, there would be no need to extract the EXIF and move
onto the next picture. Is this an efficient method however or would it be
better to scan through the MySQL table when a new image is encountered? I
anticipate the set method may be the most efficient however the table may
potentially contain tens of millions of entries eventually and so to add the
filenames for these entries into a set (volatile memory) may not be the best
idea.

Thanks folks.


Answer: I would just write a function that scanned a directory for all files; if it's
a jpeg, add the full path name of the jpeg to the list of results. If it's a
directory, then immediately call the function with the newly discovered
directory as an argument. If it's another type of file, do nothing. This is a
classic recursive divide-and-conquer strategy. It will break if there are
loops in your directory path, for instance with symlinks -- if this is a
danger for you, then you'll have to make sure you don't traverse the same
directory twice by finding the "real" non-symlinked path of each directory and
recording it.

How to avoid duplicate entries is a trickier problem and you have to consider
whether you are tolerant of two differently-named files with the exact same
contents (and also consider the edge cases of symlinked or multiply-hard-
linked files), how new files appear in the directories you are scanning and
whether you have any control over that process. One idea to speed it up would
be to use
[os.path.getmtime()](http://docs.python.org/library/os.path.html#os.path.getmtime).
Record the moment you start the directory traversal process. Next time around,
have your recursive traversal process ignore any jpeg files with an mtime
older than your recorded time. This can't be your only method of keeping track
because files modified between the start and end times of your process may or
may not be recorded, so you will still have to check the database for those
records (for instance using the full path, a hash on the file info or a hash
on the data itself, depending on what kind of duplication you're intolerant
of) but used as a heuristic it should speed up the process greatly.

You could theoretically load all filenames (probably paths and not filenames)
from the database into memory to speed up comparison, but if there's any
danger of the table becoming very large it would be better to leave that info
in the database. For instance, you could create a hash from the filename, and
then simply add that to the database with a UNIQUE constraint -- the database
will reject any duplicate entries, you can catch the exception and go on your
way. This won't be slow if you use the aforementioned heuristic checking file
mtime.

Be sure you account for the possibility of files that may be only modified and
not newly created, if that's important for your application.

