Python: Table to Script

674
9
Jump to solution
01-03-2019 08:10 AM
JohnMay3
New Contributor III

Does anyone know of a python script that could be run against a table that would generate a python script that could be used to recreate the table in code?

1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

I like MySQL's SHOW CREATE TABLE, so I created something similar with arcpy.   One script reads the table and prints a list of field information that I can modify as desired and use to create a new feature or table.

import arcpy
from arcpy import env

# Set the current workspace
env.workspace = r"C:\Path\To\file.gdb"

print env.workspace
print

# Get the list of standalone tables in the geodatabase
#
tableList = arcpy.ListFeatureClasses() # for features
# tableList = arcpy.ListTables() # for tables

print tableList
for table in tableList:
    print
    print table
    dbTable = env.workspace + "\\" + table
    fieldList = arcpy.ListFields(dbTable)
    print '\t['
    for field in fieldList:
        # print("\t{0} is a type of {1} with a length of {2}"
        #     .format(field.name, field.type, field.length))  
        # Print field properties
        #
        # print("Name:  {0}".format(field.name))
        # print("\tBaseName:    {0}".format(field.baseName))
        # print("\tAlias:       {0}".format(field.aliasName))
        # print("\tLength:      {0}".format(field.length))
        # print("\tDomain:      {0}".format(field.domain))
        # print("\tType:        {0}".format(field.type))
        # print("\tIs Editable: {0}".format(field.editable)) 
        # print("\tIs Nullable: {0}".format(field.isNullable)) 
        # print("\tRequired:    {0}".format(field.required))
        # print("\tScale:       {0}".format(field.scale))
        # print("\tPrecision:   {0}".format(field.precision))
        print '\t["' + field.name + '",',
        if (field.type == "String"):
            print '"TEXT", ',
            print '"' + str(field.length) + '",',
        else:
            print ("\"{0}\", ".format(field.type).upper()),
            print '"#",',
        print '"' + field.aliasName + '",',
        if len(field.domain):
            print '"' + field.domain + '"],',
        else:
            print '"#",',
        print '"#"],' # Default
    print '\t]'



# Fields: [ 0:Name, 1:Type, 2:Size, 3:Alias, 4:Domain 5:Default ]  use "#" for blanks

# Field type is returned as:
# SmallInteger, Integer, Single, Double, String, Date, OID, Geometry, Blob
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This script produces something like:

businesses
     [
     ["OBJECTID", "OID",  "#", "OBJECTID", "#", "#"],
     ["Business", "TEXT",  "50", "Business Name", "#", "#"],
     ["Address", "TEXT",  "50", "Business Address", "#", "#"],
     ["City", "TEXT",  "20", "Business City", "#", "#"],
     ["Zip", "TEXT",  "10", "Business Zip", "#", "#"],
     ["Phone", "TEXT",  "16", "Business Phone", "#", "#"],
     ["GlobalID", "GLOBALID",  "#", "GlobalID", "#", "#"],
     ["CreationDate", "DATE",  "#", "CreationDate", "#", "#"],
     ["Creator", "TEXT",  "50", "Creator", "#", "#"],
     ["EditDate", "DATE",  "#", "EditDate", "#", "#"],
     ["Editor", "TEXT",  "50", "Editor", "#", "#"],
     ["Shape", "GEOMETRY",  "#", "Shape", "#", "#"],
     ["POINT_X", "DOUBLE",  "#", "POINT_X", "#", "#"],
     ["POINT_Y", "DOUBLE",  "#", "POINT_Y", "#", "#"],
     ]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I then modify the list by modifying/removing/adding fields.  From there, I recreate an empty table/feature using something like this (this is for a feature so there are some geometry functions added).

# Import system modules
import arcpy
from arcpy import env

# name of geodatabase
geoDB = r"C:\Path\To\file.gdb"

# set environment settings
env.workspace = geoDB

def new_feature(dbFeature, dbAttributes, geoDB):

    # create the table
    print "\nCreating feature: " + dbFeature

    # set local variables
    geometry_type = "POINT"
    template = "#"
    has_m = "DISABLED"
    has_z = "DISABLED"

    # Use SpatialReference to set object reference - system name, projection file or authority code
    spatial_reference = arcpy.SpatialReference("WGS 1984 Web Mercator (auxiliary sphere)")
    # spatial_reference = arcpy.SpatialReference("WGS 1984")

    # Execute CreateFeatureclass
    # CreateFeatureclass_management (out_path, out_name, {geometry_type}, {template}, {has_m}, {has_z}, {spatial_reference},
    #                                {config_keyword}, {spatial_grid_1}, {spatial_grid_2}, {spatial_grid_3})
    arcpy.CreateFeatureclass_management(geoDB, dbFeature, geometry_type, template, has_m, has_z, spatial_reference)

    # add the fields
    print "Adding attributes: "

    for new_field in dbAttributes:

        # add new field
        print "\t" + new_field[0]

        # all fields using domains are non-nullable
        if (new_field[4] == "#"):
            nullable = "NULLABLE"
        else:
            nullable = "NON_NULLABLE"

        # AddField_management (in_table, field_name, field_type, {field_precision}, {field_scale},
        #           {field_length}, {field_alias}, {field_is_nullable}, {field_is_required}, {field_domain})
        arcpy.AddField_management(dbFeature,new_field[0],new_field[1],"#","#",
                                  new_field[2],new_field[3],nullable,"NON_REQUIRED",new_field[4])

        # assign default value as specified
        if ( new_field[5] <> "#" ) :
            # AssignDefaultToField_management (in_table, field_name, default_value, {subtype_code})
            arcpy.AssignDefaultToField_management(dbFeature,new_field[0],new_field[5],"#")



if __name__ == "__main__": 
    # Fields: [ 0:Name, 1:Type, 2:Size, 3:Alias, 4:Domain, 5:Default]  use "#" for blanks

    dbFeature = new_business"
    dbAttributes = [
     ["Business", "TEXT",  "50", "Business Name", "#", "#"],
     ["Address", "TEXT",  "50", "Business Address", "#", "#"],
     ["City", "TEXT",  "20", "Business City", "#", "#"],
     ["Zip", "TEXT",  "10", "Business Zip", "#", "#"],
     ["Phone", "TEXT",  "16", "Business Phone", "#", "#"],
     ["Licensed","TEXT","1","Business Licensed","YN","?"], # added domain choices: ?, Y, N
     ]
    new_feature(dbFeature, dbAttributes, geoDB)

    print
    print "Processing complete."
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This gives you the basic workings of the process.  Except for the geometry, creating a data table is similar. You can capture more of the field's properties in the first script to use in the second (field.editable, field.precision, etc.). Hope this helps.

View solution in original post

9 Replies
DanPatterson_Retired
MVP Esteemed Contributor

What do you mean by 'recreate the table'...

Make all the fields? add the content?

What would be the source of the inputs if it wasn't the table itself?

A concrete example?

0 Kudos
JohnMay3
New Contributor III

Dan

The tool would take an input table and create a script that could be run to recreate the table, i.e. add fields, field types, etc. No data would be copied just the the table layout.

DanPatterson_Retired
MVP Esteemed Contributor

much functionality is with the arcpy.da.Describe section of arcpy

Describe—ArcPy Functions | ArcGIS Desktop 

specific properties can be once you have retrieved the table

Table properties—ArcPy Functions | ArcGIS Desktop 

Then it is a matter of cycling through the Field objects and retrieving their properties.

Field—ArcPy classes | ArcGIS Desktop 

FieldInfo—ArcPy classes | ArcGIS Desktop 

The assembled information can be put back together to create a new table object with a new name.

RandyBurton
MVP Regular Contributor

I like MySQL's SHOW CREATE TABLE, so I created something similar with arcpy.   One script reads the table and prints a list of field information that I can modify as desired and use to create a new feature or table.

import arcpy
from arcpy import env

# Set the current workspace
env.workspace = r"C:\Path\To\file.gdb"

print env.workspace
print

# Get the list of standalone tables in the geodatabase
#
tableList = arcpy.ListFeatureClasses() # for features
# tableList = arcpy.ListTables() # for tables

print tableList
for table in tableList:
    print
    print table
    dbTable = env.workspace + "\\" + table
    fieldList = arcpy.ListFields(dbTable)
    print '\t['
    for field in fieldList:
        # print("\t{0} is a type of {1} with a length of {2}"
        #     .format(field.name, field.type, field.length))  
        # Print field properties
        #
        # print("Name:  {0}".format(field.name))
        # print("\tBaseName:    {0}".format(field.baseName))
        # print("\tAlias:       {0}".format(field.aliasName))
        # print("\tLength:      {0}".format(field.length))
        # print("\tDomain:      {0}".format(field.domain))
        # print("\tType:        {0}".format(field.type))
        # print("\tIs Editable: {0}".format(field.editable)) 
        # print("\tIs Nullable: {0}".format(field.isNullable)) 
        # print("\tRequired:    {0}".format(field.required))
        # print("\tScale:       {0}".format(field.scale))
        # print("\tPrecision:   {0}".format(field.precision))
        print '\t["' + field.name + '",',
        if (field.type == "String"):
            print '"TEXT", ',
            print '"' + str(field.length) + '",',
        else:
            print ("\"{0}\", ".format(field.type).upper()),
            print '"#",',
        print '"' + field.aliasName + '",',
        if len(field.domain):
            print '"' + field.domain + '"],',
        else:
            print '"#",',
        print '"#"],' # Default
    print '\t]'



# Fields: [ 0:Name, 1:Type, 2:Size, 3:Alias, 4:Domain 5:Default ]  use "#" for blanks

# Field type is returned as:
# SmallInteger, Integer, Single, Double, String, Date, OID, Geometry, Blob
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This script produces something like:

businesses
     [
     ["OBJECTID", "OID",  "#", "OBJECTID", "#", "#"],
     ["Business", "TEXT",  "50", "Business Name", "#", "#"],
     ["Address", "TEXT",  "50", "Business Address", "#", "#"],
     ["City", "TEXT",  "20", "Business City", "#", "#"],
     ["Zip", "TEXT",  "10", "Business Zip", "#", "#"],
     ["Phone", "TEXT",  "16", "Business Phone", "#", "#"],
     ["GlobalID", "GLOBALID",  "#", "GlobalID", "#", "#"],
     ["CreationDate", "DATE",  "#", "CreationDate", "#", "#"],
     ["Creator", "TEXT",  "50", "Creator", "#", "#"],
     ["EditDate", "DATE",  "#", "EditDate", "#", "#"],
     ["Editor", "TEXT",  "50", "Editor", "#", "#"],
     ["Shape", "GEOMETRY",  "#", "Shape", "#", "#"],
     ["POINT_X", "DOUBLE",  "#", "POINT_X", "#", "#"],
     ["POINT_Y", "DOUBLE",  "#", "POINT_Y", "#", "#"],
     ]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I then modify the list by modifying/removing/adding fields.  From there, I recreate an empty table/feature using something like this (this is for a feature so there are some geometry functions added).

# Import system modules
import arcpy
from arcpy import env

# name of geodatabase
geoDB = r"C:\Path\To\file.gdb"

# set environment settings
env.workspace = geoDB

def new_feature(dbFeature, dbAttributes, geoDB):

    # create the table
    print "\nCreating feature: " + dbFeature

    # set local variables
    geometry_type = "POINT"
    template = "#"
    has_m = "DISABLED"
    has_z = "DISABLED"

    # Use SpatialReference to set object reference - system name, projection file or authority code
    spatial_reference = arcpy.SpatialReference("WGS 1984 Web Mercator (auxiliary sphere)")
    # spatial_reference = arcpy.SpatialReference("WGS 1984")

    # Execute CreateFeatureclass
    # CreateFeatureclass_management (out_path, out_name, {geometry_type}, {template}, {has_m}, {has_z}, {spatial_reference},
    #                                {config_keyword}, {spatial_grid_1}, {spatial_grid_2}, {spatial_grid_3})
    arcpy.CreateFeatureclass_management(geoDB, dbFeature, geometry_type, template, has_m, has_z, spatial_reference)

    # add the fields
    print "Adding attributes: "

    for new_field in dbAttributes:

        # add new field
        print "\t" + new_field[0]

        # all fields using domains are non-nullable
        if (new_field[4] == "#"):
            nullable = "NULLABLE"
        else:
            nullable = "NON_NULLABLE"

        # AddField_management (in_table, field_name, field_type, {field_precision}, {field_scale},
        #           {field_length}, {field_alias}, {field_is_nullable}, {field_is_required}, {field_domain})
        arcpy.AddField_management(dbFeature,new_field[0],new_field[1],"#","#",
                                  new_field[2],new_field[3],nullable,"NON_REQUIRED",new_field[4])

        # assign default value as specified
        if ( new_field[5] <> "#" ) :
            # AssignDefaultToField_management (in_table, field_name, default_value, {subtype_code})
            arcpy.AssignDefaultToField_management(dbFeature,new_field[0],new_field[5],"#")



if __name__ == "__main__": 
    # Fields: [ 0:Name, 1:Type, 2:Size, 3:Alias, 4:Domain, 5:Default]  use "#" for blanks

    dbFeature = new_business"
    dbAttributes = [
     ["Business", "TEXT",  "50", "Business Name", "#", "#"],
     ["Address", "TEXT",  "50", "Business Address", "#", "#"],
     ["City", "TEXT",  "20", "Business City", "#", "#"],
     ["Zip", "TEXT",  "10", "Business Zip", "#", "#"],
     ["Phone", "TEXT",  "16", "Business Phone", "#", "#"],
     ["Licensed","TEXT","1","Business Licensed","YN","?"], # added domain choices: ?, Y, N
     ]
    new_feature(dbFeature, dbAttributes, geoDB)

    print
    print "Processing complete."
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This gives you the basic workings of the process.  Except for the geometry, creating a data table is similar. You can capture more of the field's properties in the first script to use in the second (field.editable, field.precision, etc.). Hope this helps.

JohnMay3
New Contributor III

Randy

A MILLION thanks. You just saved me a day of coding.

DanPatterson_Retired
MVP Esteemed Contributor

Then i will unmark you answer as correct and provide the proper attribution to Randy

JohnMay3
New Contributor III

Oops, yet strange cause I marked his answer as correct. Thanks

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

If your intent is to create a new feature class with the same schema as another, then I suggest looking at Export XML Workspace Document—Data Management toolbox | ArcGIS Desktop.  Since the Describe Field object properties and Add Field parameters don't line up cleanly, you have to spend time massaging the output before using it to create a new feature class.  Using Export XML Workspace Document, which can be used for individual feature classes, a fully-specified schema is created that can be used directly to create another feature class.

JohnMay3
New Contributor III

Joshua

Thanks for the suggestion however it won't work for what I need. In the end I need to develop python scripts to create table documents, relationship classes, etc. and T-SQL to update data, create triggers, etc. in a MS Azure environment as the final app requires a substantial amount of database automation that cannot be accomplished with ESRI only toolsets.

0 Kudos