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

[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

[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]

[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 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]

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

[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]

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 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]

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")