RCS Zetta: Batch Export Audio Files

ZettaBatchExport.py

"""
Zetta Batch Audio Exporter
Given a list of External IDs, find each asset's file in the Zetta Content Store, and copy it to a separate folder.

Written by Anthony Eden (https://mediarealm.com.au)
"""

from shutil import copyfile
import os
import pypyodbc
import re


# The Zetta Content Store location:
CONFIG_StorageLocation = "\\\\zettaaudio1\\ZAUDIO\\"

# The output location:
CONFIG_OutputStorage = "C:\\SCRIPTS\\ZettaBatchExport\\"

# Paste a list of External IDs here (one per line)
CONFIG_AssetList = """
12345
67890
"""





# Setup Zetta SQL DB Connection:
SQLConnectionString = 'DSN=ZettaDB;'
dbConn = pypyodbc.connect(SQLConnectionString)
cur = dbConn.cursor()

def findAssetByExternalID(ExternalID):
    assetQuery = '''
    SELECT
        Asset.ThirdPartyID,
        Asset.Title,
        Resource.StorageFile,
        AutoGen_AssetReadonlyReferences.ArtistID_1,
        Resource.ResourceID
	FROM
      Asset,
	    Resource,
	    AssetToResource,
	    AutoGen_AssetReadonlyReferences
	WHERE
        Asset.AssetID = AssetToResource.AssetID
        AND AssetToResource.isPrimaryResource = 1
        AND AssetToResource.ResourceID = Resource.ResourceID
        AND Asset.AssetID = AutoGen_AssetReadonlyReferences.AssetID
        AND ThirdPartyID = ?
    '''
	
    cur.execute(assetQuery, [str(ExternalID)])
	
    assetList = []
	
    for d in cur:
        assetList.append({
            "ExternalID": str(d[0]),
            "Filename": d[2],
            "Title": str(d[1]),
            "Artist": getArtistName(d[3]),
            "FileFolder": getStorageFolderFromFilename(d[4]),
            "FileExtension": getExtensionFromFilename(d[2]),
            "ResourceID": str(d[4])
        })
	
    return assetList

def getArtistName(artistId):
    # Take the Artist ID and return the Artist's name

    try:
        artistId = int(artistId)
    except:
        return None

    artistNameQuery = '''
    SELECT Artist.Name
    FROM Artist
    WHERE ArtistID = ?'''
    
    cur.execute(artistNameQuery, [artistId])
    
    for d in cur:
        return d[0]
    
    return ""

def getStorageFolderFromFilename(filename):
    # Zetta stores 1000 items in each folder (e.g. from 1000 to 1999)
    # This is based on the ResourceID
    
    folder = str(filename).split("~")[0][:-3]

    if folder == "":
        folder = 0

    return int(folder)

def getExtensionFromFilename(fullname):
    # Returns the file's extension name

    filename, file_extension = os.path.splitext(fullname)
    return file_extension


if __name__ == "__main__":

    IDs = CONFIG_AssetList.split()

    for thisExternalID in IDs:
        # Loop over every specified External ID
        
        asset = findAssetByExternalID(thisExternalID)[0]

        assetTitle = re.sub(r'\W+', ' ', asset['Title']).replace("  ", " ").strip()
        assetArtist = re.sub(r'\W+', ' ', asset['Artist']).replace("  ", " ").strip()
        
        srcfile = CONFIG_StorageLocation + str(asset['FileFolder']) + "\\" + asset['Filename']
        dstfile = CONFIG_OutputStorage + assetArtist + " - " + assetTitle + asset['FileExtension']

    
        if os.path.isfile(dstfile):
            print "DST FILE ALREADY EXISTS: ", dstfile
        elif os.path.isfile(srcfile):
            # Do the copy
            print asset['ExternalID'], "||", dstfile
            copyfile(srcfile, dstfile)
        else:
            print "SRC FILE DOESN'T EXIST: ", srcfile