python script to compute alarm duration in FactoryTalk View SE database and update the Tag1Value column of the "out of alarm" row

alarm_duration.py

import pyodbc

TICKS_PER_SEC = 10000000.0 # Factorytalk Alarm and Event ticks / sec

server = 'localhost\SQLEXPRESS' 
database = 'FTVSE_AE' 
username =  'python' # using SQL credentials
password = 'python_pw' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password+';Trusted_Connection=no;')
cursor = cnxn.cursor()


#get out-of-alarm time for all cleared alarms
cursor.execute("SELECT [EventID], [TicksTimeStamp], [EventAssociationID] FROM [FTVSE_AE].[dbo].[AllEvent] WHERE Active = 0 AND Acked = 0 AND Tag1Value = '';") 
row = cursor.fetchone()
results = []
while row: 
    results.append(row)
    row = cursor.fetchone()

rows_updated = 0
for result in results:
    #get in-alarm time for each
    cursor.execute(f"SELECT [EventID], [TicksTimeStamp] FROM [FTVSE_AE].[dbo].[AllEvent] WHERE Active = 1 AND Acked = 0 AND [EventAssociationID] = '{result[2]}';") 
    row = cursor.fetchone()
    if row:
        time_out = result[1]
        time_in = row[1]
        duration = (time_out - time_in) / TICKS_PER_SEC
        #update the Tag1Value column to hold the duartion time that the alarm was in.
        count = cursor.execute(f"UPDATE [FTVSE_AE].[dbo].[AllEvent] SET Tag1Value={duration} WHERE [EventID]='{result[0]}'").rowcount
        rows_updated += count    
cnxn.commit()
print(f'{rows_updated} rows updated.')

"""
/****** SQL for getting alarm durations for today once updated  ******/
SELECT [EventTimeStamp] AS [AlarmClearedTime], [Message], [Tag1Value] AS [AlarmDurationSeconds]
  FROM [FTVSE_AE].[dbo].[AllEvent] WHERE [Tag1Value] != '' AND [EventTimeStamp] > CAST(GETDATE() AS DATE) ORDER BY [EventTimeStamp] DESC;
"""