by
by
Rebecca Strauch, GISP & Larry Adgate
There are a few variables at the beginning that you will need to tailor to your environment:
editDB = (your database connection)
sqlsvrname = (name of your sql server instance)
sqldbname = (name of your sql server db within your instance)
There are several other functions and cursors that will need to have my parameters replaced with your own. It should be pretty clear where those are though.
__author__ = 'wilson'
# //////////////////////////////////////////////////////////////////////////////////////////////////////
# Script Title: Rec_Post_GEONET
# Created by: Cort Wilson, dcortwilson@gmail.com
# Created Date: 6/1/17
# Description: Blocks connections, disconnects users, reconcile/post versions, delete versions, unregisters tables and feature classes as versioned,
# compresses database, registers tables and features classes as versioned, creates versions,
# rebuild indexes, analyze datasets, set the database to allow connections.
#
# pyodbc is used to log captured errors or success message into a sql table.
#
#
# ***VERSION TREE DIAGRAM***
#
# DEFAULT
# |
# |
# QAQC
# / | \
# / | \
# / | \
# MICHAEL COLLECTOR KAREN
#
#
#
#
# Requirements
# pyodbc for SQL connection and cursor to SQL log table
#
#//////////////////////////////////////////////////////////////////////////////////////////////////////
import arcpy
import sys
import os
import time
import pyodbc
import traceback
# \\\\Constant Vars\\\\
# Database Connection
editDB ="Database Connections/DBO@CityworksGIS.sde"
# Current Day
Day = time.strftime("%m-%d-%Y", time.localtime())
# Current Time
Time = time.strftime("%I:%M:%S %p", time.localtime())
# \\\\pyodbc.connect vars\\\\
sqlsvrname = 'gis'
sqldbname = 'CityworksGIS'
# Set workspace
workspace = editDB
# Set the workspace environment
arcpy.env.workspace = workspace
arcpy.env.overwriteOutput = True
try:
# Start Time
print 'Process Started at ' + str(Day) + " " + str(Time)
# block new connections to the working and prod database.
print "Blocking Connections..."
arcpy.AcceptConnections(editDB, False)
# disconnect all users from the working and prod database.
print "Disconnecting Users..."
arcpy.DisconnectUser(editDB, "ALL")
# Get a list of all versions besides QAQC and DEFAULT to pass into the ReconcileVersions tool.
ver1List = [ver1.name for ver1 in arcpy.da.ListVersions(editDB) if ver1.name != 'DBO.QAQC' and ver1.name != 'dbo.DEFAULT']
# Execute the ReconcileVersions tool with QAQC Target Version then delete all versions besides QAQC
print "Reconcile/post versions to QAQC...."
arcpy.ReconcileVersions_management(editDB, "ALL_VERSIONS", "DBO.QAQC", ver1List, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION")
# Extract QAQC version from the list of versions to pass to ReconcileVersions tool.
ver2List = [ver2.name for ver2 in arcpy.da.ListVersions(editDB) if ver2.name == 'DBO.QAQC']
# Execute the ReconcileVersions tool with DEFAULT Target Version and delete QAQC version
print "Reconcile/post QAQC to DEFAULT..."
arcpy.ReconcileVersions_management(editDB, "ALL_VERSIONS", "dbo.DEFAULT", ver2List, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION")
print("==============================")
print ("Unregister Tables Started")
print("==============================")
# **** wildcards for tables I don't want to include ****
for tbs in [tb for tb in arcpy.ListTables() if '_log' not in tb and 'GenerateId' not in tb and 'PYTHON_SCRIPT' not in tb]:
print tbs
arcpy.UnregisterAsVersioned_management(tbs,"NO_KEEP_EDIT","COMPRESS_DEFAULT")
# If you want to include all tables in the database use this instead:
# for tbs in arcpy.ListTables():
# print tbs
# arcpy.UnregisterAsVersioned_management(tbs,"NO_KEEP_EDIT","COMPRESS_DEFAULT")
print("==============================")
print ("Unregister Feature Classes is started")
print("==============================")
datasets = arcpy.ListDatasets("*", "Feature")
for dataset in datasets:
fcList = arcpy.ListFeatureClasses("*","",dataset)
for fc in fcList:
print fc
arcpy.UnregisterAsVersioned_management(fc,"NO_KEEP_EDIT","COMPRESS_DEFAULT")
# Run the compress tool.
print "Compressing database..."
arcpy.Compress_management(editDB)
# \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ script initiation, Rec/Post process error handling \\\\\\\\\\\\\\\\\\\\\\\\\\\\\
except:
print 'An error occured'
failMsg = '\nSCRIPT FAILURE IN SCRIPT INITIATION OR RECONCILE-POST PROCESS, \n'
failMsg += 'Most recent GP messages below.\n'
failMsg += arcpy.GetMessages() +'\n'
failMsg += '\nTraceback messages below.\n'
failMsg += traceback.format_exc().splitlines()[-1]
print failMsg
#write error log info
print 'Logging error to database....'
# THIS SECTION FOR LOGGING ERRORS TO A DB TABLE
conn = pyodbc.connect('Trusted_Connection=yes', driver='{SQL Server}', server=sqlsvrname, database=sqldbname)
cursor = conn.cursor()
# You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor
cursor.execute("""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Failure',(?)); """,failMsg)
conn.commit()
conn.close()
sys.exit()
# ////////////////////////////////////// REGISTER AS VERSIONED AND RE-CREATE VERSIONS /////////////////////////////////
try:
print("==============================")
print ("register tables is started")
print("==============================")
# **** wildcards for tables I don't want to version ****
for tbs in [tb for tb in arcpy.ListTables() if '_log' not in tb and 'GenerateId' not in tb and 'PYTHON_SCRIPT' not in tb]:
print tbs
arcpy.RegisterAsVersioned_management(tbs, "NO_EDITS_TO_BASE")
# If you want to include all tables in the database use this:
# for tbs in arcpy.ListTables():
# print tbs
# arcpy.RegisterAsVersioned_management(tbs, "NO_EDITS_TO_BASE")
print("==============================")
print ("register Feature Class is started")
print("==============================")
datasets = arcpy.ListDatasets("*", "Feature")
for dataset in datasets:
fcList = arcpy.ListFeatureClasses("*","",dataset)
for fc in fcList:
print fc
arcpy.RegisterAsVersioned_management(fc, "NO_EDITS_TO_BASE")
# create versions
print "Creating Versions..."
arcpy.CreateVersion_management(editDB, 'dbo.DEFAULT', 'QAQC', 'PRIVATE')
arcpy.CreateVersion_management(editDB, 'DBO.QAQC', 'MICHAEL', 'PUBLIC')
arcpy.CreateVersion_management(editDB, 'DBO.QAQC', 'KAREN', 'PUBLIC')
arcpy.CreateVersion_management(editDB, 'DBO.QAQC', 'COLLECTOR', 'PUBLIC')
# /////////////////////////////////// ANALYZE DATASETS AND CALC STATISTICS /////////////////////////////////////
# NOTE: Rebuild indexes can accept a Python list of datasets.
# Get a list of all the datasets the user has access to.
# First, get all the stand alone tables, feature classes and rasters.
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters()
# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets("*", "Feature"):
arcpy.env.workspace = os.path.join(workspace, dataset)
dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()
# reset the workspace
arcpy.env.workspace = workspace
# Concatenate all datasets into a list
datasetList = [ds for ds in dataList]
print "rebuilding indexes"
# Execute rebuild indexes
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.RebuildIndexes_management(workspace, "SYSTEM", datasetList, "ALL")
print('Rebuild Complete')
print "analyzing datasets"
arcpy.AnalyzeDatasets_management(workspace, "SYSTEM", datasetList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
print "analysis complete"
#Allow the database to begin accepting connections again
print "Set databases to allow connections..."
arcpy.AcceptConnections(editDB, True)
# \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ If Script Successful, log into database \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
print 'Data has been synched succesfully and logged in the tracking database'
conn = pyodbc.connect('Trusted_Connection=yes', driver='{SQL Server}', server=sqlsvrname, database=sqldbname)
cursor = conn.cursor()
# You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor
cursor.execute("""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Success','All Steps Completed Without Error'); """)
conn.commit()
del cursor
conn.close()
print 'Script is finished'
# /////////////////////////////////Register as Versioned Error handling area////////////////////////////////////////////
except:
print 'An error occured'
failMsg = '\nSCRIPT FAILURE IN VERSIONING PROCESS\n'
failMsg += 'Most recent GP messages below.\n'
failMsg += arcpy.GetMessages() +'\n'
failMsg += '\nTraceback messages below.\n'
failMsg += traceback.format_exc().splitlines()[-1]
print failMsg
#write error log info
print 'Logging error to database....'
# THIS SECTION FOR LOGGING ERRORS TO A DB TABLE
conn = pyodbc.connect('Trusted_Connection=yes', driver='{SQL Server}', server=sqlsvrname, database=sqldbname)
cursor = conn.cursor()
# You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor
cursor.execute("""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Failure',(?)); """,failMsg)
conn.commit()
conn.close()
sys.exit()Feel free to let me know if you have any questions or find room for improvement! 
My email is under the author portion of the script if you need it.
by
by
by
by
