topic replaceDataSource connecting to SDE Instance in Geoprocessing Questions

replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Wed, 01 Feb 2012 12:15:59 GMT

Good morning! I am trying to create a re-pathing script in python but am running into a few kinks. I am hoping that someone can offer some assistance 🙂

What I have right now is ...

import arcpy

mxd = arcpy.mapping.MapDocument("C:\TEMP\TEST.mxd")
for lyr in arcpy.mapping.ListLayers(mxd):
    if lyr.dataSource == "Database Connections\\SDEConnection.sde\PTD.AIRPORTS":
            lyr.replaceDataSource("Database Connections\\SDEConnection.sde\PTD.ap", "SDE_WORKSPACE", "Airports")
mxd.saveACopy(r"C:\TEMP\NEWTEMP.mxd")

I've tried multiple versions in here with varying levels of success. Some don't change anything and just save a new MXD with a broken link. This one is actually changing the path but it's changing it to
C:\Users\NAME\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog\SDEConnection.sde\PTD.ap

Close .. but I'm trying to connect to our ArcGIS Server instance and NOT a local copy on my machine.

Ultimately I am wanting the script to run through a list of layers so it can update multiple ones within the same MXD, all who will have new feature class names, which is why I am using the if lyr.dataSource = section...

Does anyone know what I'm missing? I've been reading through the online documentation but it is very vague when it comes to replaceDataSource ..

Thank you!

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Wed, 01 Feb 2012 13:29:59 GMT

Ok .. I'm editing this ... I thought I had this resolved but now I don't.

I started using the script in the link below. It works fine for the first layer but doesn't process the rest.

Any ideas? I'm still green-ish with python so it's a bit harder for me to find the quirks.

http://forums.arcgis.com/threads/21423-replace-data-sources-script-not-returning-to-for-loop?p=70347&viewfull=1#post70347

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Fri, 10 Dec 2021 21:13:15 GMT

Ok. I've been trying to work with the script (below) but having a few dilemmas that I am hoping someone can help me with.

(1) The text file needs the Database Connection name, but it's the 'friendly' name that we use in ArcCatalog, which could be different for every user. Is there a way to instead reference the Server, Service, and Database?

(2) I ran a test where I was trying to replace the contours for a new version. We have contours for multiple counties. What happened was that it ran through the list and only looked for the feature class name and not a match with the owner and feature class name.

i.e. I was trying to change PTD.CONTOURS_JEFF to PTD.cn but when it scanned through the connection the script came across BULLITT.cn and assigned it instead of looking for a full match.

(3) Is there a way to tell the script to keep the python / command window open after it processes so we can make sure everything processed ok? When it errors out it closes in a snap and there is no way to see what the message said.

This script is much more in depth to Python than I have gotten so it's taking me a while to troubleshoot this. Any help would be HUGE!!! THANK YOU for reading this and any assistance you can give me!

Here is the script:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# for test.txt file
# Layername or Source, Layername or SourcePath, Connect To, Featureclass


__doc__ = """ """
import os, sys
import arcpy
from arcpy import env
from optparse import OptionParser

def get_all_files(options, startpath, filter):
  """ Build a list of files in a directory, recursively
      (optionally) as default. """
  files = []
  for filename in os.listdir(startpath):
    f = os.path.join(startpath,filename)
    if os.path.isdir(f) and not options.norecurse:
      #print "Directory found: %s" % (f)
      result = get_all_files(options, f, filter)
      if result is not None:
        files += result
    if f.upper().endswith(filter.upper()):
      files.append(f.upper())
  if files is not None and len(files) > 0:
    files.sort()
  return files

def get_files(options):
  """ Get files from command line """
  files = options.filelist.upper().split(",")
  return files

def changeDataSource(options, filelist):
  env.workspace = options.workspace
  if filelist is not None:
    for file in filelist:
      print "Processing %s" % (file)
      changed = False
      mxd = arcpy.mapping.MapDocument(file)
      layerList = arcpy.mapping.ListLayers(mxd)
      for layer in layerList:
        if layer.supports("DATASOURCE"):
          if options.listonly:
            print "\tLayer %s connects to %s" % (layer.name, layer.dataSource)
          else:
            checkIt = inList(layer.name, layer.dataSource)
            if checkIt is not None:
              print "\tMatch found for %s" % (layer.name)
              try:
                #print os.path.join(env.workspace,checkIt[1][0])
                if options.force:
                  # will change the document without workspace/featureclass checks
                  layer.replaceDataSource(checkIt[1][0],options.workspacetype,checkIt[1][1],False)
                else:
                  # will only work if a valid workspace
                  layer.replaceDataSource(checkIt[1][0],options.workspacetype,checkIt[1][1],True)
                changed = True
                print "\tDatasource for %s channged to %s" % (layer.name,checkIt[1][1])
              except Exception as e:
                print "\tCound not change datasource in %s for %s.\n\t%s\n\n\tIf no error number returned, the most likely\n" \
                      "\tcause is an invalid workspace." % (file,layer.name,e)
                if not options.force:
                  print "\tYou can force it to change with --force option.\n"
      if not options.listonly and changed:
        mxd.save()
  else:
    print "Empty file list."
  return

def inList(layername, layersource):
  global filecontents
  for i in filecontents['layer']:
    if i[0].upper() == layername.upper():
      return i
  for i in filecontents['source']:
    if i[0].upper() == layersource.upper():
      return i
  return None

def readfile(file):
  layer = []
  source = []
  linecnt = 0
  contents = open(file,"r")
  for line in contents:
    if line[0:1] != "#":
      linecnt += 1
      parts = line.split(",")
      x = []
      xx = []
      xx.append(parts[2].strip())
      xx.append(parts[3].strip())
      x.append(parts[1].strip())
      x.append(xx)
      if parts[0].upper().strip() == "LAYERNAME":
        layer.append(x)
      elif parts[0].upper().strip() == "SOURCE":
        source.append(x)
      else:
        print "Invalid entry: %s" % (parts[0].upper().strip())
      del xx
      del x
  contents.close()
  print "Read %i lines. (excluding comments)" % (linecnt)
  return {'layer':layer,'source':source}

def formatstring(string):
  layer = []
  source = []
  parts = string.split(",")
  x = []
  xx = []
  xx.append(parts[2].strip())
  xx.append(parts[3].strip())
  x.append(parts[1].strip())
  x.append(xx)
  if parts[0].upper().strip() == "LAYERNAME":
    layer.append(x)
  elif parts[0].upper().strip() == "SOURCE":
    source.append(x)
  else:
    print "Invalid entry: %s" % (parts[0].upper().strip())
  del xx
  del x
  return {'layer':layer,'source':source}

if __name__ == '__main__':
  parser = OptionParser(usage='usage: %prog [options]', version='0.1')
  parser.add_option('--filelist', action='store', dest='filelist', default=None,
                    type='string', help='List of files to process.')
  parser.add_option('--dirpath', action='store', dest='dirpath', default='C:\\',
                    type='string', help='Path to walk.')
  parser.add_option('--filter', action='store', dest='filter', default='.MXD',
                    type='string', help='File filter. ie. .MXD')
  parser.add_option('--workspace', action='store', dest='workspace', default=None,
                    type='string', help='Path to the workspace.')

  parser.add_option('--workspacetype', action='store', dest='workspacetype', default="SDE_WORKSPACE",
                    type='string', help='Workspace type. Default "SDE_WORKSPACE".')

  parser.add_option('--infile', action='store', dest='infile', default=None,
                    type='string', help='Input pivot file.')

  parser.add_option('--dest', action='store', dest='dest', default=None,
                    type='string', help='Reassignment string.\n\t' \
                    'ie. "Layername,Artwork,Database Connections\PublicUser.sde,SDE.Artwork"')

  #boolean
  parser.add_option('--force', action='store_true', dest='force', default=False,
                    help='Force document update without checking if valid.')
  parser.add_option('--listonly', action='store_true', dest='listonly', default=False,
                    help='Only list the sources found. Do not replace.')
  parser.add_option('--norecurse', action='store_true', dest='norecurse', default=False,
                    help='Do not recurse directories.')

  (options, args) = parser.parse_args()

  # this really isn't necessary i do not believe.
  if options.workspace is None:
    options.workspace = os.environ['APPDATA']+"\ESRI\Desktop10.0\ArcCatalog"

  files = get_all_files(options, options.dirpath, options.filter) if options.filelist is None else get_files(options)

  if options.infile is not None or options.dest is not None:
    if options.infile is not None:
      filecontents = readfile(options.infile)
    else:
      filecontents = formatstring(options.dest)

  changeDataSource(options, files)

This is what I've been experimenting with, but the "GIS_LOJIC.sde" is just how I have it referenced on my machine.

Source,Database Connections\GIS_LOJIC.sde\PTD.AIRPORTS_JEFF,Database Connections\GIS_LOJIC.sde,PTD.ap
Source,Database Connections\GIS_LOJIC.sde\PTD.CONTOURS_JEFF,Database Connections\GIS_LOJIC.sde,PTD.cn
Source,Database Connections\GIS_LOJIC.sde\PTD.HL_LINE_JEFF,Database Connections\GIS_LOJIC.sde,PTD.hl_line

Re: replaceDataSource connecting to SDE Instance

JakeSkinner — Fri, 10 Dec 2021 21:13:18 GMT

Hi Christina,

Ultimately, what are you trying to do? From your first post it looks like you are trying to replace the feature class 'Airports' in your MXD with another feature class called 'AP'. You can do this with the following code:

import arcpy
from arcpy import env
env.overwriteOutput = True

mxd = arcpy.mapping.MapDocument("C:\TEMP\Airports.mxd")
for lyr in arcpy.mapping.ListLayers(mxd):
    if lyr.dataSource == "Database Connections\\SDEConnection.sde\\LocalData.VECTOR.Airports":
            lyr.replaceDataSource("Database Connections\\SDEConnection.sde", "SDE_WORKSPACE", "ap")
            lyr.name = "AP"
mxd.saveACopy(r"C:\TEMP\NEW_Airports.mxd")

del mxd

The above code is written for ArcSDE for SQL Server. It will find the layer that is in the LocalData database, owned by the user Vector, and is named "Airports". This is the line: if lyr.dataSource == "Database Connections\\SDEConnection.sde\\LocalData.VECTOR.Airports":

Next, the replaceDataSource method updates this to the other feature class. I specify the Database Connection (you will not want to reference the new feature class name, only the SDE connection), the workspace type, and the new feature class to change the Airports feature class to.

The next line: lyr.name = "AP" is simply renaming the layer in the table of contents. Then finally I am saving the MXD to a new MXD.

Also, when specifying the SDE connection file, be sure to use '\\' instead of '\'. If you use '\', you will need to specify an 'r' in front of the path:

r"Database Connections\SDEConnection.sde\LocalData.VECTOR.Airports"

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Fri, 10 Dec 2021 21:13:20 GMT

Ok. That's the route I started down. The // fixed the Database Connection but I'm still having an issue where it's grabbing the wrong data.

As an example our data has BULLITT.bg and PTD.bg .. which are different counties. When the script runs it should be grabbing the PTD.bg but it's seeing the BULLITT.bg first, grabbing that and stopping. It's doing this with all the layers that we have different ones for the different counties.

Do you know of any way to get past that? It was doing the same thing on the other script too.

Thank you!!

If this helps, here is what I have so far.

import arcpy
from arcpy import env
env.overwriteOutput = True

mxd = arcpy.mapping.MapDocument("C:\TEMP\TEST.mxd")
for lyr in arcpy.mapping.ListLayers(mxd):
    if lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.AIRPORTS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.ap")
            lyr.name = "Airports"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.BG_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.bg")
            lyr.name = "Buildings - Jeff Co"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.CONTOURS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.cn")
            lyr.name = "Contours - Jeff Co"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.CNANNOL_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.cnanno")
            lyr.name = "Contour Labels - Jeff Co"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.HYDRO_LINES_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.hl")
            lyr.name = "Water Polygons"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.HL_LINE_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.hl_line")
            lyr.name = "Water Lines"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.MISC_STRUC_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.ms")
            lyr.name = "Miscellaneous Structures"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.MISC_TRANS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.mt")
            lyr.name = "Miscellaneous Transportation"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.ROADS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.rd")
            lyr.name = "Miscellaneous Structures"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.RAIL_ROADS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.rr")
            lyr.name = "Railroads - Jeff Co"
    elif lyr.dataSource == "Database Connections\\GIS_LOJIC.sde\\PTD.SPOT_HEIGHTS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.sh")
            lyr.name = "Spot Elevations - Jeff Co"

mxd.saveACopy(r"C:\TEMP\NEWTEST.mxd")

Re: replaceDataSource connecting to SDE Instance

JakeSkinner — Fri, 03 Feb 2012 15:35:41 GMT

I could not reproduce where the script is grabbing the incorrect data. I tried different scenarios (i.e. connected to the database as a user that has read privileges to all the feature class). Do you have service pack 3 installed for ArcGIS 10?

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Fri, 03 Feb 2012 16:58:43 GMT

Yes, I have Service Pack 3 on here. The login I'm using for this is the master account too so it has all privileges. It's very weird .. and frustrating 😕

I could not reproduce where the script is grabbing the incorrect data. I tried different scenarios (i.e. connected to the database as a user that has read privileges to all the feature class). Do you have service pack 3 installed for ArcGIS 10?

Re: replaceDataSource connecting to SDE Instance

JakeSkinner — Fri, 03 Feb 2012 17:57:42 GMT

Change your database connection to the PTD user and then execute your script to see if that makes a difference.

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Mon, 06 Feb 2012 14:14:27 GMT

You mean change the SDE connection username to PTD? I don't not believe that it is an actively used username here .. we actually pull the data from an outside source and through the transfer process the files are given names / owners to be consistent with what they are using. I'm thinking that if it were only pulling based on the owner then it would not have correctly selected PTD.xxx on the other attempts (it only grabbed the wrong data when we had multiple features with the same feature class name and different owners).

I'll check to see if it's an actual created account but even if it is that will make this difficult to implement on multiple machines with unique logins on each.

Is there a way to create a temporary login through the script (if it exists) and then refer back to the previous one afterwards?

Re: replaceDataSource connecting to SDE Instance

MichaelVolz — Mon, 13 Feb 2012 13:21:58 GMT

Hi Christina:

In your python script, maybe you can use the datasetname to get the full name of the SDE feature class so it differentiates between BULLITT.bg and PTD.bg. You can then use If Then statements to make sure it gets resourced to the correct data.

In addition, do you plan on running this script on many mxds at once in a loop? I have been writing similar python scripts where I resource SDE data and it appears there is a memory leak as the memory usage for the script keeps going up until it locks up the computer. I am currently working with ESRI Technical Support to resolve this particular issue.

Please let me know if you need any clarification or additional information from my response. Thank you.

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Fri, 17 Feb 2012 13:13:07 GMT

Thanks for the suggestion!!! I am going to try to look into this today. I've been detoured temporarily. I'll have to look at the comparison in Python between SDE feature class and datasetname .. I'm still learning some of the conventions within Python.

I guess once I get the script up and running I may survey the users to see how many scripts they have that will need altering. I haven't actually gotten that far down the list, however if it's a memory issue then I may just keep it simple.

I also tested doing this imported as a model and did not have any better success in that route. It seems like I always want to write the scripts that have one impossible hang up ...

Hi Christina:

In your python script, maybe you can use the datasetname to get the full name of the SDE feature class so it differentiates between BULLITT.bg and PTD.bg. You can then use If Then statements to make sure it gets resourced to the correct data.

In addition, do you plan on running this script on many mxds at once in a loop? I have been writing similar python scripts where I resource SDE data and it appears there is a memory leak as the memory usage for the script keeps going up until it locks up the computer. I am currently working with ESRI Technical Support to resolve this particular issue.

Please let me know if you need any clarification or additional information from my response. Thank you.

Re: replaceDataSource connecting to SDE Instance

ChristinaGnadinger — Fri, 10 Dec 2021 21:13:23 GMT

This is what I was trying the last time I worked on it, but this did not work.

When you mention using If Then are you thinking that it should be before or after the replace data? I was working on the hopes that if it mis-assigned it to BULLITT.cn in the replacedatasource I could change it afterwards, which did not work. I figured it was a long shot but worth trying!

import arcpy, os
from arcpy import env
from arcpy import mapping
env.overwriteOutput = True

mxd = arcpy.mapping.MapDocument("C:\TEMP\TEST.mxd")

for lyr in arcpy.mapping.ListLayers(mxd):

      if lyr.datasetName == "PTD.CONTOURS_JEFF":
            lyr.replaceDataSource("Database Connections\\GIS_LOJIC.sde", "SDE_WORKSPACE", "PTD.cn")
            if "BULLITT" in lyr.name:
                        lyr.name = "PTD" + lyr.name.split(".")[2]

           
mxd.saveACopy(r"C:\TEMP\NEWTEST.mxd")