Python: Table to Script

JohnMay3 — Thu, 03 Jan 2019 16:10:22 GMT

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?

Re: Python: Table to Script

DanPatterson_Retired — Thu, 03 Jan 2019 16:39:00 GMT

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?

Re: Python: Table to Script

JohnMay3 — Thu, 03 Jan 2019 16:41:12 GMT

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.

Re: Python: Table to Script

DanPatterson_Retired — Thu, 03 Jan 2019 17:07:24 GMT

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.

Re: Python: Table to Script

RandyBurton — Sat, 11 Dec 2021 21:46:18 GMT

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.

Re: Python: Table to Script

JohnMay3 — Thu, 03 Jan 2019 18:21:54 GMT

Randy

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

Re: Python: Table to Script

DanPatterson_Retired — Thu, 03 Jan 2019 19:14:41 GMT

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

Re: Python: Table to Script

JohnMay3 — Thu, 03 Jan 2019 19:20:31 GMT

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

Re: Python: Table to Script

JoshuaBixby — Fri, 04 Jan 2019 05:39:26 GMT

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.

Re: Python: Table to Script

JohnMay3 — Fri, 04 Jan 2019 13:15:31 GMT

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.

topic Re: Python: Table to Script in Python Questions

Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script

Re: Python: Table to Script