As nice as the photo station is inside of the QNAP, sometimes you just want to do your own thing with your photos and videos. But what if they are organized into albums on the QNAP? Couldn’t you just pull down the photos/videos in the album? Or, better, get local paths on your QNAP network mounts? All this, and more, is possible with the following script:

#!/usr/bin/python
import shlex
import subprocess
import os

parms = {}
parms['host'] = "qnaphostname"
parms['login'] = "admin"
parms['albumName'] ="Album1"
localBase = '/Volumes'

cmdBase = """ssh %(login)s@%(host)s "/usr/local/mariadb/bin/mysql -u read --password=read s01 -B  -S /tmp/mysql_mediadb.sock -e 'select cFileName, cFullPath   FROM pictureAlbumTable picAlbum JOIN pictureAlbumMapping picMap ON picMap.iPhotoAlbumId = picAlbum.iPhotoAlbumId JOIN %(mediaTable)s pic ON pic.%(mediaColumn)s = picMap.iMediaId JOIN dirTable ON dirTable.iDirId = pic.iDirId WHERE picAlbum.cAlbumTitle = \\"%(albumName)s\\" and type = %(type)s; ' " """

tables = []
tables.append({'mediaTable':'pictureTable','mediaColumn':'iPictureId','type':'1'} )
tables.append({'mediaTable':'videoTable','mediaColumn':'iVideoId','type':'2'} )

allFilenames = set()
for table in tables:
    parms.update(table)
    cmd = cmdBase % parms
    print cmd
    res = subprocess.check_output(cmd, shell=True)
    # Skip the first row, which has column headers
    hits = res.split("\n")[1:]
    print "Found %s hits" % len(hits)
    for f in hits:
        parts = f.split("\t")
        if len(parts) != 2:
            continue
        filename = os.path.join(localBase,parts[1],parts[0])
        allFilenames.add(filename)
open(parms['albumName'],'w').write("\n".join(allFilenames))

This grabs video and photos using ssh to run the mysql query remotely. QNAP references videos and photos from a very similar set of tables in a mysql database (called s01 on my box). The username and password for this database is read/read. The -B option makes the results tab-delimited, and thus more parseable.

Of course this isn’t super polished or anything; just modify the host and albumName parameters and you should be fine. That is, assuming you’re on OS X. If you are not, modify localBase to point to your mount location for the QNAP shared folder.

Update: I missed this originally, but you also have to pass in the type for each of the two queries. If you don’t do this you invariably get more videos and pictures than are actually in the album. The query is now updated above.