Submitted for your perusal...

[Daley, MarkX]

Hmmm, methinks my attachment got away.  I will put the code directly in the
message this time.  Please forgive the text wrapping.

-Mark


# Data Collection Script  v. 1.03
# 4/18/00
# Mark Daley
# Python 1.5.2
# Feel free to use and distribute as you see fit.  No guarantees on
operability!  It works ok the way it is.

import dbi, odbc
import time
time1 = [957081600.0] 		# Sun 4/30/00 00:00:00 = 957081600.0
time2 = [time.time()]

def timer():
	"Timer that constantly checks against a set date and executes a
function after set length of time."
	while time.time() > 0:
		while time.time()-time1[0] < 604800:  		# 1 hour =
3600, 1 day = 86400
			pass
		time2 = [time.time()]
		collect()
		time1[0] = time2[0]
		
def collect():
	"Routine to query all data collection servers for all family names
for time period defined in timer."
	servername = ['ServerA', 'ServerB', 'ServerC']
	sql_date = time.strftime('%Y'+"-"+'%m'+"-"+'%d
%H'+":"+'%M'+":"+'%S', time.localtime(time1[0]))
	current_date = time.strftime('%Y'+"-"+'%m'+"-"+'%d
%H'+":"+'%M'+":"+'%S', time.localtime(time2[0]))
	file_date = time.strftime('%m'+"_"+'%d'+"_"+'%Y',
time.localtime(time.time()))
	for index in servername:
		#try:
			familyname = ['name1', 'name2', 'name3', 'name4',
'name5', 'name6', 'name7', 'name8', 'name9', 'name10', 'name11', 'name12',
'name13']
			dbc = odbc.odbc(index+'/username/password')
			crsr = dbc.cursor()
			for name in familyname:
				crsr.execute("""select * from subtestlist\n\
where (errorcode<>'null'\n\
AND familyname='"""+name+"""'\n\
AND Packagestarttime>'"""+sql_date+"""'\n\
AND Packagestarttime<='"""+current_date+"""');""")
				result = crsr.fetchall()
				f =
open('/data/'+name+'_'+index+'_'+file_date+'.txt', 'a')
			# Headers for import to Excel	
	
f.write("modulename,subtestnum,subtestname,familyname,releasenum,productcode
,serialnum,servername,id,subtestendtime,packagestarttime,opid,errorcode,erro
rmessage,isfinalsubtest,duration,firstsn" + '\n')
				z = 0
			# Date converter for readability/consistency
				for item in range(len(result)):
					modulename, subtestnum, subtestname,
familyname, releasenum, productcode, serialnum, servername, id,
subtestendtime, packagestarttime, opid, errorcode, errormessage,
isfinalsubtest, duration, firstsn = result[item]
					subtestendtime =
time.ctime(subtestendtime)
					packagestarttime =
time.ctime(packagestarttime)
					f.write(`modulename` + ',' +
`subtestnum` + ',' + `subtestname` + ',' + `familyname` + ',' + `releasenum`
+ ',' + `productcode` + ',' + `serialnum` + ',' + `servername` + ',' + `id`
+ ',' + `subtestendtime` + ',' + `packagestarttime` + ',' + `opid` + ',' +
`errorcode` + ',' + `errormessage` + ',' + `isfinalsubtest` + ',' +
`duration` + ',' + `firstsn` + '\n')
					z = z + 1
				f.write('\n' + "Total error count = " + `z`)
				f.close()
		#except dbi.opError:
			#servername.append(index)

# Reference code; unable to invoke as function in collect()

def subtestdateconv():
	for item in range(len(result)):
		modulename, subtestnum, subtestname, familyname, releasenum,
productcode, serialnum, servername, id, subtestendtime, packagestarttime,
opid, errorcode, errormessage, isfinalsubtest, duration, firstsn =
result[item]
		subtestendtime = time.ctime(subtestendtime)
		packagestarttime = time.ctime(packagestarttime)
		result[item] = (modulename, subtestnum, subtestname,
familyname, releasenum, productcode, serialnum, servername, id,
subtestendtime, packagestarttime, opid, errorcode, errormessage,
isfinalsubtest, duration, firstsn)

timer()

# v 1.0   Initial release.

# v 1.01  Added counter to provide total number of errors at end of file.
Added Koa to familyname.

# v 1.02  Changed how data files are written; easier to import to Excel.

# v 1.03  Added error trapping for failed server connection attempts.  Still
needs work.

-----Original Message-----
From: Daley, MarkX 
Sent: Tuesday, May 02, 2000 11:03 AM
To: 'python-list at python.org'
Subject: Submitted for your perusal...


All,

Many thanks to those of you who have helped me to develop this little
script.  Find attached the first reasonably working copy of my data
collection routine.  There are a couple of points to make.

1) The try:/except: portion is pounded out because I am having difficulty
manipulating the servername list to append the current server name in the
event of a failed connection.  The idea is, if the connection is lost or
fails, the name is appended to the end of the list, to be repeated ad
infinitum until data is successfully retrieved.

2) I'm sure some of the coding techniques I'm using are cumbersome, but this
is my first attempt.  Please be patient, and any and all input is
appreciated.

3) Feel free to adapt this to your own uses.  I don't know how handy this
might be, but it has improved data retrieval in my workplace tremendously.

Thanks again.

- Mark