Validating table and field names in Python

Validating table names

Geodatabases use various relational database management systems (RDBMS) to maintain the many tables that comprise a geodatabase. All tables in a geodatabase must have a valid name, so a mechanism for checking whether a table name is valid is essential when creating data in a geodatabase. Using the ValidateTableName() function, a script can determine whether a specific name is valid to a specific workspace.

The following are table name errors that will be validated:

• The table shares its name with a word reserved by the data source (for example, Table).
• The table contains an invalid character.
• The table has an invalid starting character (for example, using a number as the first character).

Note:

The ValidateTableName function does not determine that the specified name is unique to the specified workspace. The Exists function can check to see whether the table name is unique for a given workspace.

ValidateTableName function

Function	Explanation
ValidateTableName(name, {workspace})	Takes a table name and a workspace path and returns a valid table name for the workspace

Specifying the workspace as a parameter allows ArcPy to check all the existing table names and determine whether there are naming restrictions imposed by the output workspace. If the output workspace is an RDBMS, it may have reserved words that may not be used in a table name. It may also have invalid characters that cannot be used in a table or field name. All invalid characters are replaced with an underscore (_). ValidateTableName returns a string representing a valid table name that may be the same as the input name if the input name is valid. The example below guarantees that the new output feature class created by the Copy Features tool has a unique name that is valid in any geodatabase:

"""Move all shapefiles from a folder into a geodatabase"""
import arcpy

# Set the workspace. List all of the shapefiles
arcpy.env.workspace = "d:/St_Johns"
fcs = arcpy.ListFeatureClasses("*")

# Set the workspace to SDE for ValidateTableName
arcpy.env.workspace = "Database Connections/Bluestar.sde"

# For each feature class name
for fc in fcs: 
    # Validate the output name so it is valid
    outfc = arcpy.ValidateTableName(fc)

    # Copy the features from the workspace to a geodatabase
    arcpy.CopyFeatures_management(fc, outfc)

Validating field names

Each database can have naming restrictions for field names in a table. Objects such as feature classes or relationship classes are stored as tables in an RDBMS, so these restrictions affect more than just stand-alone tables. These restrictions may or may not be common among various database systems, so scripts should check all new field names to ensure that a tool does not fail during execution.

The following are the field name errors that will be validated:

• The field shares its name with a word reserved by the data source (for example, Table).
• The field shares its name with a previously defined field.
• The field contains an invalid character (for example, *).
• The field name exceeds the data source's maximum length for field names.

ValidateFieldName function

Function	Explanation
ValidateFieldName(name, {workspace})	Takes a string (field name) and a workspace path and returns a valid field name based on name restrictions in the output geodatabase

The example below ensures that a field is added, regardless of the input name, using the ValidateFieldName function:

"""
  Create a new numeric field containing the ratio of polygon area to
  polygon perimeter. Two arguments, a feature class and field name,
  are expected.
"""

# Define a pair of simple exceptions for error handling
class ShapeError(Exception):
    pass

class FieldError(Exception):
    pass

import arcpy
import os

try:
    # Get the input feature class and make sure it contains polygons
    input = arcpy.GetParameterAsText(0)
    desc = arcpy.Describe(input)
    if desc.shapeType.lower() != "polygon":
        raise ShapeError

    # Get the new field name and validate it
    fieldname = arcpy.GetParameterAsText(1)
    fieldname = arcpy.ValidateFieldName(fieldname, os.path.dirname(input))

    # Make sure shape_length and shape_area fields exist
    if len(arcpy.ListFields(input, "Shape_area")) > 0 and \
        len(arcpy.ListFields(input, "Shape_length")) > 0:

        # Add the new field and calculate the value
        #
        arcpy.AddField_management(input, fieldname, "double")
        arcpy.CalculateField_management(input, fieldname,
                                        "[Shape_area] / [Shape_length]")
    else:
        raise FieldError

except ShapeError:
    print("Input does not contain polygons")

except FieldError:
    print("Input does not contain shape area and length fields")

except arcpy.ExecuteError:
    print(arcpy.GetMessages(2))