nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Looping thru Databases in order to List Versions

JasonWinoker

Hi. I am new to the Python scene, and just started learning about it. I am trying to figure out how to accomplish what I think is a simple task. Basically, I want to make a list of versions that are in our sde databases.

This is what I have written so far:

import arcpy
from arcpy import env
env.workspace = "Database Connections"
dbList = ["CIP", "Ems", "MMS", "Prod", "Storm", "Utils", "WCS"]

##sdeDB = "SQL" + str(dbList) + ".sde"
##The result of this line of code is: SQL[CIP, Ems, MMS, Prod, Storm, Utils, WCS].sde & it is not what I want

for list in dbList:
    list.append("SQL"+[dbList]+".sde")
print list

I know that it is primitive, and is probably wrong in so many ways. Having said that, this is what I am trying to do:

--Look in ???Database Connections??? at the individual databases
--Make a list
--Add ???SQL??? in front of each item in the list
--Add ???.sde??? to the end of each item on the list
     These are the sde database names

For the second part of the script, I???d like to:

--Find the versions in those databases
--See a list of the versions in each of the databases

Here is what I have drafted so far to see the list, but it is only for SQLUtils.sde:

versionList = arcpy.ListVersions(r"Database Connections\SQLUtils.sde")
for version in versionList:
   print version

This script works just fine, but only for SQLUtils.sde, but I want to get it to loop thru sde databases in Database Connections after it appends the ???SQL??? prefix and the ???.sde??? extension to the database name.

I know that I could simply type the "SQL" and ".sde" to the items in the list, but where is the challenge in that?

Any suggestions? I'd really appreciate your help.

Sincerely,

Jason

Vind meer berichten getagd met

Accepted answers

All comments

MathewCoyle

Using the following code, I get this error message:

"TypeError: ListVersions: Could not open SDE Workspace."

import arcpy,os
#path to database connections
path = "C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\"
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:
dbName = db.replace(path,"")
print(dbName)
#print a divider underneath the database Path
print("-" * len(dbName))
#for each SDE database, creates a list of its versions
versions = arcpy.ListVersions(db)
for version in versions:
print version
# create whitespace between each database and list of versions
print("\n")

Have some problems with your paths I would think. Us os.path functions to properly define paths in python. Changing the red bold parts worked for me.

import arcpy
import os
#path to database connections
path = os.path.join(os.getenv("APPDATA"), r"ESRI\Desktop10.0\ArcCatalog")
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(os.path.join(path, dir)) for dir in dirs if dir.endswith(".sde")]
for db in dbs:
    dbName = db.replace(path,"")
    print(dbName)
    #print a divider underneath the database Path
    print("-" * len(dbName))
    #for each SDE database, creates a list of its versions
    versions = arcpy.ListVersions(db)
    for version in versions:
        print version
    # create whitespace between each database and list of versions
    print("\n")

JasonWinoker

Using the following code, I get this error message:

"TypeError: ListVersions: Could not open SDE Workspace."

import arcpy,os
#path to database connections
path = "C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\"
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:
    dbName = db.replace(path,"")
    print(dbName)
    #print a divider underneath the database Path
    print("-" * len(dbName))
    #for each SDE database, creates a list of its versions
    versions = arcpy.ListVersions(db)
    for version in versions:
        print version
    # create whitespace between each database and list of versions
    print("\n")

MathewCoyle

Thanks mzcoyle for the clarification. Do you know off hand if sde connections are stored by default in the
"C:\\Documents and Settings\\user\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\" folder in XP?

Yes they should be.

BruceBacia

Thanks mzcoyle for the clarification. Do you know off hand if sde connections are stored by default in the
"C:\\Documents and Settings\\user\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\" folder in XP?

MathewCoyle

This will work regardless of windows version or settings.

path = os.path.join(os.getenv("APPDATA"), r"ESRI\Desktop10.0\ArcCatalog")

BruceBacia

I think the AppData folder might be a windows 7 thing. In windows xp are your sde connections stored in the "C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\" folder? If so, just set that to the value for the path variable. Hopefully this will work for you

import arcpy,os
#path to database connections
path = "C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\" 
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")] 
for db in dbs:
    dbName = db.replace(path,"")
    print(dbName)
    #print a divider underneath the database Path
    print("-" * len(dbName))
    #for each SDE database, creates a list of its versions
    versions = arcpy.ListVersions(db)
    for version in versions:
        print version
    # create whitespace between each database and list of versions
    print("\n")

JasonWinoker

Bruce,

Thanks again for your input. I was just trying something out when I switched versionList = arcpy.ListVersions(db) to versionList = arcpy.ListVersions(path).

I am a bit confused by this path:

path = "C:\\Users\\jwinoker\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\"

as I was using:

"C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\"

and I get output ( list of versions within a defined .sde) if I specify an .sde in the code and comment everything out except for the "C:\\Documents and Settings\\jwinoker..." path and just run this part of the script:

versions = arcpy.ListVersions(path)
for version in versions:
print version.

It seems that I want the list to show versions within the databases.

Given that, it makes sense to me that the db.replace should look thru each database and print out a list of versions within the database.

So, for SQLUtils,sde for example, I want to see a list with current versions in it, including:

DBO.UtilQC
"BCC\ACOLEMAN".Ann
"BCC\JWINOKER".Jason

I did not find an AppData, nor a Roaming, directory on my computer. I am a peion and do not have Administator priveledges. WHat is the problem?

BruceBacia

In your code you have:

versionList = arcpy.ListVersions(path)

You should change it to:

versionList = arcpy.ListVersions(db)

This is trying to list versions of "C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\", of which there are none. Also, you won't need to set the workspace here. Try this below

import arcpy,os
#path to database connections
path = "C:\\Users\\jwinoker\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\" 
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")] 
for db in dbs:
    dbName = db.replace(path,"")
    print(dbName)
    #print a divider underneath the database Path
    print("-" * len(dbName))
    #for each SDE database, creates a list of its versions
    versions = arcpy.ListVersions(db)
    for version in versions:
        print version
    # create whitespace between each database and list of versions
    print("\n")

JasonWinoker

Bruce,

Thanks for your help.

Here is the script that I used:

import arcpy,os
arcpy.env.workspace = path

#path to database connections
path = ("C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\")
dirs = os.listdir(path)

#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:
    dbName = db.replace(path, "")
    print(dbName)

#print a divider underneath the database Path
    print("-" * len(dbName))

#for each SDE database, creates a list of its versions
versionList = arcpy.ListVersions(path)
for version in versionList:
    print version

#create whitespace between each database and list of versions
    print("\n")

This is the result in the Interactive Window in PythonWin:

Connection to SQL Server.sde
----------------------------
SQLCIP.sde
----------
SQLEms.sde
----------
SQLMMS.sde
----------
SQLProd.sde
-----------
SQLStorm.sde
------------
SQLUtils.sde
------------
SQLWCS.sde
----------
Traceback (most recent call last):
File "C:\Documents and Settings\All Users\Start Menu\Programs\Python2.6\pythonwin\lib\site-packages\Pythonwin\pywin\framework\scriptutils.py", line 312, in RunScript
    exec codeObject in __main__.__dict__
File "C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\MyScripts\TESTListVersionsLOOP.py", line 18, in <module>
    versionList = arcpy.ListVersions(path)
File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\__init__.py", line 750, in ListVersions
    return gp.listVersions(*args)
File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\geoprocessing\_base.py", line 337, in listVersions
    self._gp.ListVersions(*gp_fixargs(args)))
ValueError: ListVersions: Not a valid SDE workspace.

When I alter the code to specify a path where I have an SDE connection, like:

import arcpy,os

#for each SDE database, creates a list of its versions
versionList = arcpy.ListVersions("C:\\Documents and Settings\\jwinoker\\Application Data\\ESRI\\Desktop10.0\\ArcCatalog\\SQLStorm.sde")
for version in versionList:
    print version

I see this:

DBO.StormQA
DBO.Mobile
"BCC\PLOOMIS".Paul
"BCC\JWINOKER".Jason

It also works for versions in the SQLUtils.sde if I specify everything. It lists the databases, no problem, but it only works if I specify the path and my sde version, but it does not seem to go thru all of the databases, and find my versions.

BruceBacia

Oops... for some reason I put "SDE." instead of "SQL." in the replace. You can just swap out the text

BruceBacia

I tested this one. It should work for you. I added the "." after the "SDE" to make it more readable.

import arcpy,os
#path to database connections
path = "C:\\Users\\YourUserName\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\"
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:[INDENT]dbName = db.replace(path,"SDE.")[/INDENT]
[INDENT]print(dbName)[/INDENT]
[INDENT]#print a divider underneath the database Path[/INDENT]
[INDENT]print("-" * len(dbName)) [/INDENT]
[INDENT]#for each SDE database, creates a list of its versions[/INDENT]
[INDENT]versions = arcpy.ListVersions(db)[/INDENT]
[INDENT]for version in versions:[/INDENT]
[INDENT=2]print version[/INDENT]
[INDENT]# create whitespace between each database and list of versions[/INDENT]
[INDENT]print("\n")[/INDENT]

BruceBacia

I forgot -- with the first script you could you the replace method to add the "SQL".

import arcpy,os
#path to database connections
path = "C:\\Users\\YourUserName\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\"
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:[INDENT]print(db.replace(path,"SQL"))
[/INDENT]
[INDENT]#print a divider underneath the database Path[/INDENT]
[INDENT]print("-" * len(db))
[/INDENT]
[INDENT]#for each SDE database, creates a list of its versions
versions = arcpy.ListVersions(db)
for version in versions:
[/INDENT]
[INDENT=2]print version[/INDENT]
[INDENT=2]# create whitespace between each database and list of versions
[/INDENT]
[INDENT]print("\n")

[/INDENT]

With the second script, I'm not completely sure of the output of the ListWorkspaces function, but you would probably
just add the desired text.

import arcpy
#path to database connections
path = "C:\\Users\\YourUserName\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog"
arcpy.env.workspace = path
#lists the SDE databases in Database Connections
dbs = arcpy.ListWorkspaces("*","SDE")
for db in dbs:
[INDENT]print("SQL" + db + ".sde")
[/INDENT]
[INDENT]#print a divider underneath the database Path[/INDENT]
[INDENT]print("-" * len(db))
[/INDENT]
[INDENT]#for each SDE database, creates a list of its versions
[/INDENT]
[INDENT]versions = arcpy.ListVersions(db)[/INDENT]
[INDENT]for version in versions:
[/INDENT]
[INDENT=2]print version[/INDENT]
[INDENT=2]# create whitespace between each database and list of versions
[/INDENT]
[INDENT]print("\n")[/INDENT]

BruceBacia

I haven't actually tested this, because I'm at home. You might need to play around with it a little. You will probably need to run this from an admin account (From ESRI: Only those versions the connected user has permissions to use will be included in the list returned by the function.)

import arcpy,os
#path to database connections
path = "C:\\Users\\YourUserName\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\"
dirs = os.listdir(path)
#list of the full paths of SDE databases in Database Connections
dbs = [(path + dir) for dir in dirs if dir.endswith(".sde")]
for db in dbs:[INDENT]print(db)
    #print a divider underneath the database Path[/INDENT]
[INDENT]print("-" * len(db))
#for each SDE database, creates a list of its versions
versions = arcpy.ListVersions(db) [/INDENT]
[INDENT]for version in versions:
[/INDENT]
[INDENT=2]print version   [/INDENT]
[INDENT]# create whitespace between each database and list of versions
print("\n")
[/INDENT]

an alternate version might also work:

import arcpy
#path to database connections
path = "C:\\Users\\YourUserName\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog"
arcpy.env.workspace = path
#lists the SDE databases in Database Connections
dbs = arcpy.ListWorkspaces("*","SDE")
for db in dbs:[INDENT]print(db)
    #print a divider underneath the database Path[/INDENT]
[INDENT]print("-" * len(db))
#for each SDE database, creates a list of its versions
versions = arcpy.ListVersions(db) [/INDENT]
[INDENT]for version in versions:
[/INDENT]
[INDENT=2]print version   [/INDENT]
[INDENT]# create whitespace between each database and list of versions
print("\n") [/INDENT]

MathewCoyle

To get a list of sde instances you could do something like this. Not sure if there is a better way.

sde_dir =  os.path.join(os.getenv("APPDATA"), r"ESRI\Desktop10.0\ArcCatalog")
sde_list = os.listdir(sde_dir)
    if sde.endswith(".sde"):
        print sde

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Alle Gemeenschappen
Recente Berichten
Gevolgde Inhoud
Community Hulp

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.