When using ListTables() or ListFeatureClasses() with an enterprise geodatabase, the returned list will include the database views. In many cases, users name views with a unique prefix or suffix for distinction (i.e. VW_PARCELOWNERS or PARCELOWNERS_VW). For views with such a naming convention, you can get a list of them by using a list comprehension:
arcpy.env.workspace = r"c:\data\CityOfRedlands.sde"
views = [v for v in arcpy.ListTables() if v.endswith("_VW")]
If views have no specific naming convention or you want to ensure all the views are returned, you can use the ArcSDESQLExecute class.
For SQL Server:
sql_execute = arcpy.ArcSDESQLExecute(r'Database Connections\SQLSERVERDB.sde')
views = sql_execute.execute("select name from sys.objects where type = 'V'")
sql_execute = arcpy.ArcSDESQLExecute(r'Database Connections\MYORACLEDB.sde')
# List user views
views = sql_execute.execute("select view_name from user_views")
For other database types, be sure to provide the correct SQL expression.
At the Esri International User Conference this year, an attendee came to the analysis island to ask “how do I create a rank field?”. They had run the Generate Near Table geoprocessing tool (see illustration of table below) and were looking for a way to further rank the distances. Ideally, the table would be updated to include a RANK field starting at ‘1’ for the smallest distance for each PATIENT and increasing sequentially based on the distance. The rank could then be used to facilitate further review and reporting. We were able to come up with the addRanks function below in a few minutes automating a key missing piece of the user’s workflow.
Table after running addRanks function.
def addRanks(table, sort_fields, category_field, rank_field='RANK'):
"""Use sort_fields and category_field to apply a ranking to the table.
sort_fields: list | tuple of strings
The field(s) on which the table will be sorted.
All records with a common value in the category_field
will be ranked independently.
The new rank field name to be added.
# add rank field if it does not already exist
if not arcpy.ListFields(table, rank_field):
arcpy.AddField_management(table, rank_field, "SHORT")
sort_sql = ', '.join(['ORDER BY ' + category_field] + sort_fields)
query_fields = [category_field, rank_field] + sort_fields
with arcpy.da.UpdateCursor(table, query_fields,
sql_clause=(None, sort_sql)) as cur:
category_field_val = None
i = 0
for row in cur:
if category_field_val == row:
i += 1
category_field_val = row
i = 1
row = i
if __name__ == '__main__':
['distance'], 'patient', 'rank')
Note: dBase (and shapefiles) does not support ORDER BY as used above by arcpy.da.UpdateCursor’s sql_clause argument.