RCS Zetta External ID Finder

ZettaExternalIDs.py

"""
Zetta External ID Finder

This script is designed to find a selection of usable numeric External IDs in Zetta.
It only looks for five-digit codes. It will output these to a file.

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

import os
import sys
import pypyodbc 
import io
import time
import datetime

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

def listUsedExternalIDs():
    # Find External IDs that have already been used
	usedExternalIDsQuery = '''
	SELECT ThirdPartyID
	  FROM [ZettaDB].[dbo].[Asset]
	  WHERE ThirdPartyID IS NOT NULL
	  AND ThirdPartyID != ''
	  ORDER BY ThirdPartyID DESC
	'''
	
	cur.execute(usedExternalIDsQuery)
	
	usedExternalIDs = []
	
	for d in cur:
		usedExternalIDs.append(str(d[0]))
	
	return usedExternalIDs

def listUsableExternalIDs(limit = 500):
    # Find five-digit IDs that haven't been used
	dontUseTheseIDs = listUsedExternalIDs()
	useTheseIDs = []
	
	#This is our starting point for IDs:
	count = 20000
	
	while len(useTheseIDs) < limit:
		count = count + 1
		if str(count) not in dontUseTheseIDs:
			useTheseIDs.append(str(count))
	
	return useTheseIDs

def writeIDsToFile(filename, IDs):
    # Write some useable External IDs to a text file
	f = open(filename, "w")
	
	count = 0
	
	for x in IDs:
		if count == 0:
			f.write("MUSIC -----------------------------------------\r\n\r\n")
		elif count == 30:
			f.write("\r\n\r\n\r\n\r\nLINKS -----------------------------------------\r\n\r\n")
		elif count == 60:
			f.write("\r\n\r\n\r\n\r\nSPOTS -----------------------------------------\r\n\r\n")

		f.write(x + "\r\n")
		count += 1
	
	f.write("\r\nAt the time of generation, it is " + datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S') + "\r\n")
	
	f.close()

writeIDsToFile("C:\\SCRIPTS\SpareExternalIDs.txt", listUsableExternalIDs(90))