Use geoprocessing tools, Python scripts, or geodatabase administration tools in ArcGIS for Desktop to perform geodatabase administration and data management that you formerly performed with ArcSDE administration commands.
The following sections list how to accomplish common geodatabase administration and data management tasks for enterprise geodatabases.
Geodatabase administration
Some administration tasks require you to connect as the geodatabase administrator; some require you to connect as the database administrator. In many cases, if you will be scripting administration tasks, you'll need to create a database connection file (.sde) that connects as the required user. Use the Create Database Connection geoprocessing tool or the Database Connection dialog box in ArcGIS for Desktop to create the file.
Create a geodatabase | Tool |
---|---|
Create a geodatabase in Microsoft SQL Server, Oracle, or PostgreSQL | Create Enterprise Geodatabase geoprocessing tool or Enable Enterprise Geodatabase geoprocessing tool |
Create a geodatabase in IBM DB2 or IBM Informix | Enable Enterprise Geodatabase geoprocessing tool |
Control geodatabase configuration | Tool |
---|---|
Add configuration keywords and edit parameters in DBTUNE | The Export Geodatabase Configuration Keywords and Import Geodatabase Configuration Keywords geoprocessing tools |
Change log file table configuration | Configure Geodatabase Log File Tables geoprocessing tool |
Delete a schema geodatabase from Oracle | Delete Schema Geodatabase geoprocessing tool |
Upgrade a geodatabase | Upgrade Geodatabase geoprocessing tool |
Manage versioned geodatabases | Tool |
---|---|
Create a geodatabase version | Create Version geoprocessing tool or in the Version Manager dialog box in ArcGIS for Desktop |
Reconcile a version | Reconcile Versions geoprocessing tool or using the Reconcile function in ArcMap |
Post changes to parent version | Post Version geoprocessing tool or using the Post function in ArcMap |
Compress a versioned geodatabase | Compress geoprocessing tool or the Compress Database function in ArcGIS for Desktop |
Delete a geodatabase version | Delete Version geoprocessing tool or in the Version Manager dialog box in ArcGIS for Desktop |
Diagnose and repair version metadata and system tables | Diagnose Version Metadata, Repair Version Metadata, Diagnose Version Tables, and Repair Version Tables geoprocessing tools |
Update indexes on version system tables | Rebuild Indexes geoprocessing tool |
Update statistics on version system tables | Analyze Datasets geoprocessing tool |
Manage connections | Tool |
---|---|
Get a list of current geodatabase connections | In the Connections tab of the Geodatabase Administration dialog box in ArcGIS for Desktop or the ListUser Python function You can also see locks held by connections from the Locks tab of the Geodatabase Administration dialog box. |
Drop connections from the geodatabase | In the Connections tab of the Geodatabase Administration dialog box in ArcGIS for Desktop or the DisconnectUser Python function |
Temporarily block geodatabase connections and reenable connections | In the Connections tab of the Database Properties dialog box in ArcGIS for Desktop or the AcceptConnections Python function |
Data management
When loading data into an enterprise geodatabase, the user you connect as will own the data in the geodatabase. Some geodatabase management tasks, such as granting privileges or rebuilding indexes, can only be performed by the data owner. Be sure to create a database connection file (.sde), connecting as the correct user before performing these tasks.
In most cases, ArcSDE administration commands were used to script data loading or management; therefore, the data management tasks listed in the following sections include sample scripts.
If you want create the database connection file as part of each script, use CreateDatabaseConnection_management.
Export geodatabase contents to a portable file
Description: Export the geodatabase schema, relationships, behavior, and data to an XML workspace documents to share it with others. You can also export a subset of the geodatabase's contents to an XML workspace document.
Tool to use: Export XML Workspace Document
Sample script:
""" Export geodatabase contents to a portable file """
import arcpy
import sys
def ExportXMLWorkspaceDocument(in_data, out_file, export_type=None, storage_type=None, export_metadata=None):
""" Export geodatabase contents to a portable file """
try:
arcpy.ExportXMLWorkspaceDocument_management(in_data,
out_file,
export_type,
storage_type,
export_metadata)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ExportXMLWorkspaceDocument(*arguments)
Import new geodatabase contents
Description: XML workspace documents include geodatabase schema, relationships, and behavior, and can contain data as well. You might import an XML workspace document that contains just geodatabase schema, then load your own data into the schema. Or you might create a template geodatabase, export the schema and relationships defined in that geodatabase to an XML workspace document, and import that document to a new geodatabase.
Tool to use: Import XML Workspace Document
Sample script:
""" Import new gdb contents """
import arcpy
import sys
def ImportXMLWorkspaceDocument(target_geodatabase, in_file, import_type=None, config_keywords=None):
""" Import new gdb contents """
try:
arcpy.ImportXMLWorkspaceDocument_management(target_geodatabase,
in_file,
import_type,
config_keywords)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ImportXMLWorkspaceDocument(*arguments)
Import contents to overwrite old contents
Description: XML workspace documents include geodatabase schema, relationships, and behavior, and can contain data as well. You can import an XML workspace document containing schema, relationships, behavior, and data and overwrite the contents of an existing geodatabase. You might do this if you receive project updates from another agency or contractor, and you want all the latest data and definitions.
Tools to use: Delete plus Import XML Workspace Document
Sample script:
""" Imports contents to overwrite old contents """
import arcpy
import sys
from xml.etree import ElementTree
import re
def FindDatasets(xml_file):
""" Open xml file and identify datasets """
xmldoc = ElementTree.ElementTree()
xmldoc.parse(xml_file)
root = xmldoc.getroot()
contents = []
for i in root.findall('WorkspaceDefinition/DatasetDefinitions/DataElement'):
contents.append(i.findtext('Name'))
for j in i.findall('./Children/DataElement/CatalogPath'):
contents.append('/'.join(re.split(r'/[a-z|A-Z]*=', j.text)[1:]))
contents.reverse()
return contents
def DeleteDatasets(database, datasets):
""" Delete dataset in database """
for ds in datasets:
try:
arcpy.Delete_management(os.path.join(database, datasets))
except:
print('Unable to delete {}'.format(ds))
def ImportXML(database, xml_file):
""" Import XML workspace document """
arcpy.ImportXMLWorkspaceDocument_management(database, xml_file)
if __name__ == "__main__":
xml_file = sys.argv[1]
database = sys.argv[2]
datasets = FindDatasets(xml_file)
DeleteDatasets(database, datasets)
ImportXML(database, xml_file)
arcpy.ImportXMLWorkspaceDocument_management(database, xml_file)
Import single shapefile or coverage and map fields
Description: Specify which fields from the shapefile or coverage you import map to which fields in the resultant feature class in the geodatabase.
Tools to use: ListFields plus FeatureClassToFeatureClass_conversion plus FieldMappings
Sample script:
import arcpy
import os
arcpy.env.overwriteOutput = True
input_features = r'C:\Users\davi4075\Documents\ArcGIS\lakes.shp'
out_fc = r'C:\Users\davi4075\Documents\ArcGIS\mexico.gdb\out_lakes'
fms = arcpy.FieldMappings()
fms.fieldValidationWorkspace = os.path.dirname(out_fc)
# Cycle through input field to transfer
for field in arcpy.ListFields(input_features):
# Exclude geometry and objectid fields
if field.type not in ['Geometry', 'OID']:
fm = arcpy.FieldMap()
fm.addInputField(input_features, field.name)
# if altering field properties, need to push updated field back
# to FieldMap
field.name = field.name.upper()
fm.outputField = field
# Insert FieldMap into FieldMappings
fms.addFieldMap(fm)
arcpy.FeatureClassToFeatureClass_conversion(
input_features, os.path.dirname(out_fc), os.path.basename(out_fc),
field_mapping=fms)
Import multiple shapefiles or coverages at once
Description: Import multiple shapefiles or coverages at one time.
Tool to use: Feature Class To Geodatabase
Sample script:
""" Import multiple shapefiles or coverages at one time """
import arcpy
import sys
def ImportMultiple(input_features, output_database):
"""
Import multiple shapefiles or coverages at one time
input_features -- semi-colon delimited string of shapefiles and/or coverages
"""
try:
arcpy.FeatureClassToGeodatabase_conversion(input_features, output_database)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ImportMultiple(*arguments)
Import multiple shapefiles or coverages, and grant privileges on new feature classes to existing database groups
Description: Import multiple shapefiles or coverages at one time and grant privileges on the resultant feature classes to existing database groups so members of the groups will have access to the new feature classes.
Tools to use: Feature Class To Geodatabase plus Change Privileges
Sample script:
"""
Import multiple shapefiles or coverages and grant privileges on new feature
classes to existing groups or users
"""
import arcpy
import sys
def ImportMultipleAndGrantPrivileges(input_features, output_database, user, view=None, edit=None):
"""
Import multiple shapefiles or coverages and grant privileges on new feature
classes to existing groups or users
"""
try:
feature_classes = input_features.split(';')
for fc in feature_classes:
out_fc = arcpy.CreateUniqueName(fc, output_database)
arcpy.FeatureClassToFeatureClass_conversion(fc, out_fc)
arcpy.ChangePrivileges_management(out_fc, user, view, edit)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ImportMultipleAndGrantPrivileges(*arguments)
Combine multiple shapefiles into one feature class
Description: If you have shapefiles that contain the same type of data, you can combine them in one feature class in your geodatabase. For example, if you get point shapefiles representing schools from multiple school districts, youcould import those shapefiles into a single schools feature class in your geodatabase.
Tools to use: Append plus Merge
Sample script:
""" Combine multiple shapefiles into one feature class """
import arcpy
import sys
def CombineMultipleFeatureClasses(input_features, output_or_target):
""" Combine multiple shapefiles into one feature class """
try:
feature_classes = input_features.split(';')
if arcpy.Exists(output_or_target):
arcpy.Append_management(feature_classes, output_or_target)
else:
arcpy.Merge_management(feature_classes, output_or_target)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
CombineMultipleFeatureClasses(*arguments)
Import multiple tables
Description: You can import multiple dBASE, INFO, VPF, OLE DB or geodatabase tables to your enterprise geodatabase at one time.
Tool to use: Table To Geodatabase
Sample script:
""" Import multiple nonspatial tables """
import arcpy
import sys
def ImportTables(in_tables, target_database):
"""
Export data to a shapefile
in_tables -- semi-colon delimited string of tables
target_database -- the database were tables will be added
"""
try:
tables = in_tables.split(";")
arcpy.TableToGeodatabase_conversion(tables, target_database)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ImportTables(*arguments)
Truncate a table or feature class and append records
Description: If you receive data updates from another agency or outside party, you can preserve your existing table and, therefore, the privileges defined for the table by truncating the table and appending the data updates.
Tools to use: Truncate Table plus Append
Sample script:
""" Truncate table or feature class and append records """
import arcpy
import sys
def TruncateTableOrFeatureClass(in_table, target_table):
""" Truncate table or feature class and append records """
try:
arcpy.TruncateTable_management(target_table)
arcpy.Append_management(in_table, target_table, schema_type="NO_TEST")
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
TruncateTableOrFeatureClass(*arguments)
Truncate a table or feature class, append records, and update database statistics
Description: If you receive data updates from another agency or outside party, you can preserve your existing table and, therefore, the privileges defined for the table by truncating the table and appending the data updates. If the table contains a large number of records, you will likely want to update database statics on the newly updated table.
Tools to use: Truncate Table plus Append plus Analyze Datasets
Sample script:
"""
Truncate table or feature class, append records, and update
database statistics
"""
import arcpy
import sys
import os
def TruncateTableOrFeatureClass(in_table, target_table):
"""
Truncate table or feature class, append records, and update
database statistics
"""
try:
arcpy.TruncateTable_management(target_table)
arcpy.Append_management(in_table, target_table)
descr = arcpy.Describe(os.path.dirname(target_table))
if getattr(descr, 'connectionProperties', None):
conn = os.path.dirname(target_table)
else:
conn = os.path.dirname(os.path.dirname(target_table))
arcpy.AnalyzeDatasets_management(conn)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
TruncateTableOrFeatureClass(*arguments)
Create a nonspatial database view
Description: A database view can combine data from multiple tables and restrict the attributes that are returned from source tables.
Tool to use: Create Database View
Sample script:
""" Create nonspatial view """
import arcpy
import sys
def CreateNonspatialView(input_database, view_name, view_definition):
""" Create nonspatial view """
try:
arcpy.CreateDatabaseView_management(input_database, view_name, view_definition)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
CreateNonspatialView(*arguments)
Create a database view containing an SQL geometry type column
Description: A database view can combine data from multiple tables or feature class, and restrict the attributes that are returned from source tables or feature classes. You can include a single SQL geometry type column in the view, and the spatial data can be displayed in ArcGIS.
Tool to use: Create Database View
Sample script:
""" Create view containing SQL geometry type column """
import arcpy
import sys
def CreateSpatialView(input_database, view_name, view_definition):
""" Create view containing SQL geometry type column """
try:
arcpy.CreateDatabaseView_management(input_database, view_name, view_definition)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
CreateSpatialView(*arguments)
Delete tables
Description: You can delete multiple tables from the geodatabase at one time. This could be useful in workflows where data is temporary, such as project specific data, or workflows in which you receive replacement data from an outside party.
Tool to use: Delete
Sample script:
""" Delete nonspatial tables """
import arcpy
import sys
def DeleteNonspatialTables(in_data):
"""
Delete nonspatial tables
in_data -- semi-colon delimited string of tables
"""
try:
tables = in_data.split(';')
for table in tables:
arcpy.Delete_management(table)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
DeleteNonspatialTables(sys.argv[1])
Delete feature classes
Description: You can delete multiple feature classes from the geodatabase at one time. This could be useful in workflows where data is temporary, such as project specific data, or workflows in which you receive replacement data from an outside party.
Tool to use: Delete
Sample script:
""" Delete feature classes """
import arcpy
import sys
def DeleteFeatureClasses(in_data):
"""
Delete feature classes
in_data -- semi-colon delimited string of feature classes
"""
try:
feature_classes = in_data.split(';')
for fc in feature_classes:
arcpy.Delete_management(fc)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
DeleteFeatureClasses(sys.argv[1])
Export data to a shapefile
Description: Export a feature class to a shapefile.
Tool to use: Feature Class To Shapefile
Sample script:
""" Export data to a shapefile """
import arcpy
import sys
def ExportToAShapefile(in_table, target_shp):
""" Export data to a shapefile """
try:
if arcpy.Exists(target_shp):
arcpy.Append_management(in_table, target_shp, schema_type="NO_TEST")
else:
arcpy.CopyRows_management(in_table, target_shp)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ExportToAShapefile(*arguments)
Reconcile, post, and compress a versioned geodatabase
Description: You version data and your geodatabase to facilitate editing workflows that require multiple people editing data at the same time over long periods of time. To allow for this, some maintenance to the geodatabase is required. This includes reconciling versioned data with an ancestor version to pull in changes made to the ancestor version, posting edits from the child version to the ancestor version, and compressing the geodatabase to remove unneeded version states.
Tools to use: Reconcile Versions plus Compress
Sample script:
""" Reconcile, post, and compress versioned geodatabase """
import arcpy
import sys
def ReconcilePostAndCompress(input_database, reconcile_mode):
"""
Reconcile, post, and compress versioned geodatabase
"""
try:
arcpy.ReconcileVersions_management(input_database, reconcile_mode)
arcpy.Compress_management(input_database)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
ReconcilePostAndCompress(*arguments)
Also see Using Python scripting to batch reconcile and post versions.
Version tables or feature classes, add global IDs to them, and create a replica
Description: Tables or feature classes must be registered as versioned and contain a global ID column before you can replicate them.
Tools to use: Register As Versioned plus Add GlobalIDs plus Create Replica
Sample script:
""" Version tables, add global IDs to tables, and create a replica """
import arcpy
import sys
def VersionTablesEtc(in_tables, in_type=None, out_geodatabase=None, out_name=None):
""" Version tables, add global IDs to tables, and create a replica """
try:
tables = in_tables.split(';')
for table in tables:
arcpy.RegisterAsVersioned_management(table)
arcpy.AddGlobalIDs_management(tables)
if 'CreateReplica' in dir(arcpy.management):
arcpy.CreateReplica_management(tables, in_type, out_geodatabase, out_name)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
arguments = sys.argv[1:]
VersionTablesEtc(*arguments)
Register a third-party table with the geodatabase
Description: If you use third-party applications or SQL to create tables (both spatial and nonspatial) in the database where your geodatabase is stored, you can register the tables with the geodatabase. This adds information about the tables to the geodatabase system tables, and the tables can then participate in geodatabase functionality.
Tool to use: Register With Geodatabase
Sample script:
""" Register third-party tables with the geodatabase """
import arcpy
import sys
def RegisterThirdPartyTables(in_tables):
"""
Register third-party tables with the geodatabase
in_tables -- semi-colon delimited string of tables
"""
try:
tables = in_tables.split(';')
for table in tables:
arcpy.RegisterWithGeodatabase_management(table)
except arcpy.ExecuteError:
print(arcpy.GetMessages(2))
if __name__ == "__main__":
RegisterThirdPartyTables(sys.argv[1])