FactoryTalk Veiw Site Edition Alarm and Event SQL Server backup to SQLite

ftvse_db_backup.py

import sqlite3
import pyodbc

TABLES = {
"FTAEInstance": """
CREATE TABLE IF NOT EXISTS [FTAEInstance](
	[lFTAEInstanceId] [int] NOT NULL,
	[sProduct] [varchar](255) NOT NULL,
	[sCatalogNumber] [varchar](255) NULL,
	[lMajorVersion] [int] NOT NULL,
	[lMinorVersion] [int] NOT NULL,
	[lPatchVersion] [int] NOT NULL,
	[lBuildVersion] [int] NULL,
	[sVersionString] [varchar](20) NULL,
	[sComments] [varchar](255) NULL,
	[lUpdatedById] [int] NULL,
	[tEntered] [datetime] NOT NULL,
	[tModified] [datetime] NULL)
""",
"ConditionEvent":"""
CREATE TABLE IF NOT EXISTS [ConditionEvent](
	[EventID] [uniqueidentifier] NOT NULL,
	[SourceName] [nvarchar](200) NULL,
	[SourcePath] [nvarchar](512) NULL,
	[SourceID] [uniqueidentifier] NULL,
	[ServerName] [nvarchar](50) NULL,
	[TicksTimeStamp] [bigint] NULL,
	[EventTimeStamp] [datetime2](7) NULL,
	[EventCategory] [nvarchar](50) NULL,
	[Severity] [int] NULL,
	[Priority] [int] NULL,
	[Message] [nvarchar](512) NULL,
	[ConditionName] [nvarchar](50) NULL,
	[SubConditionName] [nvarchar](50) NULL,
	[AlarmClass] [nvarchar](40) NULL,
	[Active] [bit] NULL,
	[Acked] [bit] NULL,
	[EffDisabled] [bit] NULL,
	[Disabled] [bit] NULL,
	[EffSuppressed] [bit] NULL,
	[Suppressed] [bit] NULL,
	[PersonID] [nvarchar](50) NULL,
	[ChangeMask] [int] NULL,
	[InputValue] [float] NULL,
	[LimitValue] [float] NULL,
	[Quality] [int] NULL,
	[EventAssociationID] [uniqueidentifier] NULL,
	[UserComment] [nvarchar](512) NULL,
	[UserComputerID] [nvarchar](64) NULL,
	[Tag1Value] [nvarchar](128) NULL,
	[Tag2Value] [nvarchar](128) NULL,
	[Tag3Value] [nvarchar](128) NULL,
	[Tag4Value] [nvarchar](128) NULL,
	[Shelved] [bit] NULL,
	[AutoUnshelveTime] [datetime2](7) NULL,
	[GroupPath] [nvarchar](254) NULL)
""",
"SimpleEvent": """
CREATE TABLE IF NOT EXISTS [SimpleEvent](
	[EventID] [uniqueidentifier] NOT NULL,
	[SourceName] [nvarchar](200) NULL,
	[SourcePath] [nvarchar](512) NULL,
	[SourceID] [uniqueidentifier] NULL,
	[ServerName] [nvarchar](50) NULL,
	[TicksTimeStamp] [bigint] NULL,
	[EventTimeStamp] [datetime2](7) NULL,
	[EventCategory] [nvarchar](50) NULL,
	[Severity] [int] NULL,
	[Priority] [int] NULL,
	[Message] [nvarchar](512) NULL)
""",
"AllEvent": """
CREATE TABLE IF NOT EXISTS [AllEvent](
	[EventID] [uniqueidentifier] NOT NULL,
	[EventType] [int] NULL,
	[SourceName] [nvarchar](200) NULL,
	[SourcePath] [nvarchar](512) NULL,
	[SourceID] [uniqueidentifier] NULL,
	[ServerName] [nvarchar](50) NULL,
	[TicksTimeStamp] [bigint] NULL,
	[EventTimeStamp] [datetime2](7) NULL,
	[EventCategory] [nvarchar](50) NULL,
	[Severity] [int] NULL,
	[Priority] [int] NULL,
	[Message] [nvarchar](512) NULL,
	[ConditionName] [nvarchar](50) NULL,
	[SubConditionName] [nvarchar](50) NULL,
	[AlarmClass] [nvarchar](40) NULL,
	[Active] [bit] NULL,
	[Acked] [bit] NULL,
	[EffDisabled] [bit] NULL,
	[Disabled] [bit] NULL,
	[EffSuppressed] [bit] NULL,
	[Suppressed] [bit] NULL,
	[PersonID] [nvarchar](50) NULL,
	[ChangeMask] [int] NULL,
	[InputValue] [float] NULL,
	[LimitValue] [float] NULL,
	[Quality] [int] NULL,
	[EventAssociationID] [uniqueidentifier] NULL,
	[UserComment] [nvarchar](512) NULL,
	[ComputerID] [nvarchar](64) NULL,
	[Tag1Value] [nvarchar](128) NULL,
	[Tag2Value] [nvarchar](128) NULL,
	[Tag3Value] [nvarchar](128) NULL,
	[Tag4Value] [nvarchar](128) NULL,
	[Shelved] [bit] NULL,
	[AutoUnshelveTime] [datetime2](7) NULL,
	[GroupPath] [nvarchar](254) NULL)
""",
"TrackingEvent": """
CREATE TABLE IF NOT EXISTS [TrackingEvent](
	[EventID] [uniqueidentifier] NOT NULL,
	[SourceName] [nvarchar](200) NULL,
	[SourcePath] [nvarchar](512) NULL,
	[SourceID] [uniqueidentifier] NULL,
	[ServerName] [nvarchar](50) NULL,
	[TicksTimeStamp] [bigint] NULL,
	[EventTimeStamp] [datetime2](7) NULL,
	[EventCategory] [nvarchar](50) NULL,
	[Severity] [int] NULL,
	[Priority] [int] NULL,
	[Message] [nvarchar](512) NULL,
	[PersonID] [nvarchar](50) NULL,
	[UserComment] [nvarchar](512) NULL,
	[ComputerID] [nvarchar](64) NULL)
"""}

cnxn = pyodbc.connect(Trusted_Connection='yes', username= "Python", password="python", driver = '{SQL Server}',server = 'FTVSE01,50167', database = 'FTAE')
cursor = cnxn.cursor()

conn = sqlite3.connect('backup.db')
c = conn.cursor()
for table in TABLES:
  c.execute(TABLES[table])
  cursor.execute("SELECT * FROM {}".format(table))
  rows = cursor.fetchall()
  total_rows = len(rows)
  rows_written = 0
  next_update = 5
  print("Inserting {} rows into Table: {}".format(total_rows, table))
  for row in rows:
      c.execute("INSERT INTO {} VALUES ({})".format(table,",".join(["?"]*len(row))), row)
      rows_written += 1
      percent_done = ((float(rows_written)/float(total_rows)) * 100)
      if percent_done >= next_update:
          print("{:3.1f}%".format(percent_done))
          next_update += 5
conn.commit()
conn.close()

cnxn.close()