topic Re: Using a Python Script to Reconcile and post in Python Questions

Using a Python Script to Reconcile and post

LarryAdgate — Thu, 11 May 2017 23:29:35 GMT

We are a large Water Utility Company with 38 Enterprise Geodatabases and most of them employ a versioning setup. As A workflow, I am considering the use of a Python Script to automate the Reconcile and posting process. Can someone share with me there experience using this kind of workflow and is there a down side.

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Fri, 12 May 2017 08:37:24 GMT

Tagging a few groups that may have more experiences with Water Utilities specifically ArcGIS for Water Utilities‌ Utilities and Communications‌

i am not in a water utility group, but do use Enterprise Geodatabases with versioning and topology. Our data is not in any logical network or tightly structured system.that is, our boundaries are typically based on drainages or sometimes seemingly random political boundaries. I mention that since, if a mistake was made but it didn't violate and topology rules or cause a conflict, if it was an automated process, we may not catch it. Luckily we only have two editors (me being the 2nd, and only on occasion), so manually doing a R&P it not a problem. ...and the control freak part of me likes to know what is being changed. With that said, I think there are many that probably do automate the process (and probably have many more editors).

Re: Using a Python Script to Reconcile and post

MitchHolley1 — Fri, 12 May 2017 14:09:16 GMT

A good place to start: Reconcile Versions—Data Management toolbox | ArcGIS Desktop

Re: Using a Python Script to Reconcile and post

BlakeTerhune — Fri, 12 May 2017 16:07:30 GMT

If you're automatically posting the versions, keep in mind that there is no opportunity to review conflicts and that someone's unfinished edits might get posted and interpreted as final.

Re: Using a Python Script to Reconcile and post

LarryAdgate — Fri, 12 May 2017 16:16:09 GMT

Hi Blake, Thank You for your comments,

Larry

Re: Using a Python Script to Reconcile and post

CortWilson — Wed, 31 May 2017 20:39:30 GMT

A little late to your post, but figured I'd put in my $0.02. Hopefully you've found a solution that will work for you!

We are a small-ish sized water utility: 27,000 service connections on over 600 miles of water main. I am using python to automate the reconcile-post process. It works well for us because there are only 3 of us editing at one time and rarely are there any conflicts. I perform QAQC edits in the DEFAULT version and favor all conflicts towards that version, and that's good enough for me. If the automated conflict resolution is a concern, you can set a parameter in the script process to abort if any conflicts are found, review the conflicts, then re-run the script. You might want to re-search the implications of aborting the process when a conflict is found. One problem of course is that usually you would want to run the script after-hours or on the weekends and now that possibly leaves you attached to reviewing conflicts after hours.

I built in error handling throughout the script and have it write to a SQL Server logging table the specific error that was found or if the script was run successfully. A SQL Server trigger on the logging table or function in python to send an email after an error is raised would take it to the next level; I have yet to do that....

In general, I have found automating the process to be a big help. There are many errors that can be introduced when doing this process manually, and hard-coding in all the steps in the right order has reduced a lot of headaches for me.

Re: Using a Python Script to Reconcile and post

LarryAdgate — Wed, 31 May 2017 21:22:00 GMT

Thank You Cort for your comments,

Larry

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Wed, 31 May 2017 21:25:18 GMT

dcortwilsonpud Do you have any sample code that you can share?

Re: Using a Python Script to Reconcile and post

LarryAdgate — Wed, 31 May 2017 21:54:26 GMT

I also echo Rebecca's request,

Larry

Re: Using a Python Script to Reconcile and post

CortWilson — Wed, 31 May 2017 22:21:37 GMT

LarryAdgate‌ & rastrauch‌

Sure! Give me a bit to strip out any sensitive information from the script and add in some more descriptive comments.

If you don't hear from me within a few days give me a shout.

Re: Using a Python Script to Reconcile and post

CortWilson — Fri, 02 Jun 2017 17:50:46 GMT

LarryAdgate‌ & rastrauch‌

What's the best way to share this script? Can't seem to figure out how to attach to this post....

I could email it if that works?

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Fri, 02 Jun 2017 18:06:46 GMT

Probably as a .zip or a .txt file. You probably can't do it as a response to an email or from the geonet inbox, so you will want to open the thread in a new brower tab. I'm not seeing the "attach" button right now either (hmmm), but if the code is not too long, use the ... At the top and the more but to pull up the syntax highlighter. Select the type and paste. A better explaination of this is https://community.esri.com/people/curtvprice/blog/2014/09/25/posting-code-blocks-in-the-new-geonet?sr=search&searchId=972c3550-f984-4493-9b0b-628d734d41b7&searchIndex=9‌

edit: you could also post as a new document (which allows you to show versions if/when you update) or in a blog post in you space. Then include the link here so others can find it.

Re: Using a Python Script to Reconcile and post

CortWilson — Sat, 11 Dec 2021 16:33:22 GMT

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.

Re: Using a Python Script to Reconcile and post

LarryAdgate — Fri, 02 Jun 2017 21:00:30 GMT

Thank You Cort, Are you going to ESRI Conference in San Diego?

Larry Adgate

Re: Using a Python Script to Reconcile and post

CortWilson — Fri, 02 Jun 2017 21:48:18 GMT

Yes, I will be going!

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Mon, 05 Jun 2017 15:46:46 GMT

Thanks Cort. I'll take a look at it and see if it will work in our shop. Right now, only two of us ever edit our master file, so since we have tight control and usually know of any conflict status, this might be what we need. Anything that can speed up a routine step is very nice.

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Mon, 05 Jun 2017 20:23:52 GMT

dcortwilsonpud I'm wondering if there is a reason why you have to "UnregisterAsVersioned" ? I know when I compress manually, I don't usually need to do this. I do try to make sure that I am the only one connected and/or there are no locks.

Are you doing this as a way to delete the versions so you can do a clean recreation of the versions? I'm not saying that is wrong (and may be a good thing??), but I don't think I'll need to do that. But wanted to ask before I skip over that.

We only have two editors, but it's has a pretty complicated set of topology rules with many "exceptions". Not that un-registering would cause a issue, but don't want to introduce that if I don't need to. We occasionally manually delete a version, compress, and re-create. That is mainly because I like to get it back to State 0. Maybe that is the purpose?

Re: Using a Python Script to Reconcile and post

CortWilson — Mon, 05 Jun 2017 20:31:30 GMT

Yep, you have it exactly right. I should have mentioned that.

You don't have to unregister, delete, and re-create. I do it to get a clean compress back to state 0, and because we were having some weird issues happening with our versioning at one time, and ESRI support recommended that we implement this full process to eliminate any issues.

Re: Using a Python Script to Reconcile and post

RebeccaStrauch__GISP — Mon, 05 Jun 2017 20:37:40 GMT

Thanks. I'll have to play around with whether I want to do this every time or more on-demand for our use. In any case, this will same me time on working all the logic out!

Can you share a snapshot of the SQL file structure you use for the log file?

Re: Using a Python Script to Reconcile and post

CortWilson — Sat, 11 Dec 2021 16:33:25 GMT

I'm on SQL Server 2008. I would think this query should work on later versions too...

USE [YOUR DB NAME HERE]
GO

/****** Object:  Table [dbo].[PYTHON_SCRIPT_TRACKER]    Script Date: 06/05/2017 13:46:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PYTHON_SCRIPT_TRACKER](
     [ScriptName] [varchar](255) NULL,
     [RunDate] [date] NULL,
     [Status] [varchar](255) NULL,
     [ErrorCode] [varchar](255) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍