JSONtoFeature File gdb vs SDE Geodatabase

130
11
Jump to solution
08-09-2022 08:26 AM
jaykapalczynski
Frequent Contributor

I have been successful in writing to a File Geodatabase using JSONtoFeature in python

But now I am trying to write to an Enterprise GDB and having issues.

I added the Connection File that has Admin Rights for this test.  So should be good to go there.

 

Thoughts?

 

Noting that just before this I am trying to DELETE the existing FC using this

fc_Delete = ["Data_Staging"]
for fc in fc_Delete:
   fc_path = os.path.join(cws, fc)
   if arcpy.Exists(fc_path):
      arcpy.Delete_management(fc_path)

 

FILE GEODATABASE - this works

dataOutput = r"C:/Users/t/Desktop/JSONSamples/INPUTJSONFILE.json"
output_fc = r'C:\\Users\\t\\Desktop\\JSONSamples\\outgdb.gdb\test'
arcpy.JSONToFeatures_conversion(dataInput, output_fc)

 

ENTERPRISE GEODATABASE - not working

dataInput = r"E:/ArcGIS_Server/ArcGIS/Trail/INPUTJSONFILE.json"
output_fc = r"E:/ArcGIS_Server/ArcGIS/Trail/ConnectionFile.sde/Data_Staging"
arcpy.JSONToFeatures_conversion(dataInput, output_fc)

 

ERROR:

Traceback (most recent call last):
File "E:\ArcGIS_Server_aws\ArcGIS_Published_MXDs\BirdingTrail\AddPoints_w_changeFieldTypes3.py", line 395, in <module>
main()
File "E:\ArcGIS_Server_aws\ArcGIS_Published_MXDs\BirdingTrail\AddPoints_w_changeFieldTypes3.py", line 43, in main
deleteExistingFC()
File "E:\ArcGIS_Server_aws\ArcGIS_Published_MXDs\BirdingTrail\AddPoints_w_changeFieldTypes3.py", line 137, in deleteExistingFC
arcpy.JSONToFeatures_conversion(dataInput, output_fc)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\conversion.py", line 1171, in JSONToFeatures
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\conversion.py", line 1168, in JSONToFeatures
retval = convertArcObjectToPythonObject(gp.JSONToFeatures_conversion(*gp_fixargs((in_json_file, out_features, geometry_type), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000206: Cannot create FeatureClass 'E:/ArcGIS_Server/ArcGIS/Trail/ConnectionFile.sde/Data_Staging'. Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'R23_pk'. Cannot insert duplicate key in object 'dbo.Data_Staging'. The duplicate key value is (0).] [Trail.DBO.Data_Staging]
Failed to execute (JSONToFeatures).

0 Kudos
1 Solution

Accepted Solutions
jaykapalczynski
Frequent Contributor

OK that makes me happy that it was not me but annoyed this is a 7 year bug

OK so I think I should be able to:

  1. Delete Features from my Enterprise GDB FC
  2. Read my file geodatabase FC
  3. Append it to the Enterprise Geodatabase FC

Work around but think it will work

 

View solution in original post

11 Replies
JeffK
by MVP Regular Contributor
MVP Regular Contributor

You could use arcpy.env.overwriteOutput = True to avoid the 'if exists then delete' logic.

As for the error: Error 000206 

Description

The named feature class cannot be created as named.

Solution

The name that has been specified is likely invalid. Correct the name and try again. All data formats have characters that are considered invalid and should not be used as feature class names. Rename the feature class using characters that are valid for the underlying database. In addition to spaces, the following list contains characters that should not be used in a geodatabase feature class name: `~@#$%^&*()-+|\,<>?{}.!'[]:;. If working in Python, ArcPy has functions that allow you to validate table names and field names. You can use the ValidateTableName or ValidateFieldName functions to ensure that the name is valid.

0 Kudos
jaykapalczynski
Frequent Contributor

@JeffK- Ok so this is working off the solution you helped me with last week.  now just trying to tweak to write to a Enterprise SDE Database.

1. read from URL

2. write to local JSON file

3. JSONtoFeatures (Local JSON file , New Output Feature Class)

 

I am not doing anything crazy with the File name.

Is there a character limit in the Connection File String?

THIS WORKS if I write to a local File Geodatabase.

 

 

 

import arcpy
import json
import uuid
import os
import re
import requests

# SCRIPT GLOBAL VARIABLES
arcpy.env.workspace = 'E:/ArcGIS_Server/ArcGIS/Trail/'
fsURL = "https://dwr.virginia.gov/-/api/v1/vbwt-sites"

dataInput = "E:/ArcGIS_Server/ArcGIS/Trail/JSON_New4.json"
output_fc = "E:/ArcGIS_Server/AdminConnectionFile.sde/Trail.DBO.VABWTS"

inFeatures = "VABWTS"
tempLayer = "testLayer"       
fld_dict = {}
new_dict = {}
converted_list = []

def main():
    # Read URL and truncate long fields
    readURL()
    # Add New Feature Class
    addNewFC()
    
def readURL():
    r3 = requests.get(fsURL)
    data = json.loads(r3.text)

    # Added this So I could use it in the For loop
    data1 = {"BirdTrailLocations":  data }
    for i in data1['BirdTrailLocations']:
        feat_dict = {} 
        feat_dict["geometry"] = {"x": i["site_longitude"], "y": i["site_latitude"]}
        feat_dict["attributes"] = i
        converted_list.append(feat_dict)

    new_dict = {"geometryType" : "esriGeometryPoint",
                 "spatialReference" : {"wkid" : 4326},
                'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
                'features': converted_list}

    # write this truncated capture to a local file for further processing this is a requirement for this to work per ESRI
    writeLocalFileURLText(new_dict)    

def writeLocalFileURLText(urlResults):
    new_dict = urlResults
    print (new_dict)
    with open(dataInput, 'w') as f:
        json.dump(new_dict, f)

def addNewFC():
    arcpy.env.overwriteOutput = True
    arcpy.JSONToFeatures_conversion(dataInput, output_fc)
    arcpy.env.overwriteOutput = False
 

 

 

 

ERROR:

Traceback (most recent call last):
File "E:\ArcGIS_Server\ArcGIS\Trail\AddPoints_w_changeFieldTypes3.py", line 403, in <module>
main()
File "E:\ArcGIS_Server\ArcGIS\Trail\AddPoints_w_changeFieldTypes3.py", line 49, in main
deleteExistingFC()
File "E:\ArcGIS_Server\ArcGIS\Trail\AddPoints_w_changeFieldTypes3.py", line 144, in deleteExistingFC
arcpy.JSONToFeatures_conversion(dataInput, output_fc)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\conversion.py", line 1171, in JSONToFeatures
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\conversion.py", line 1168, in JSONToFeatures
retval = convertArcObjectToPythonObject(gp.JSONToFeatures_conversion(*gp_fixargs((in_json_file, out_features, geometry_type), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000206: Cannot create FeatureClass 'E:\ArcGIS_Server\AdminConnectionFile.sde\Trail.DBO.VABWTS'. Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'R34_pk'. Cannot insert duplicate key in object 'dbo.VABWTS'. The duplicate key value is (0).] [BirdingTrail.DBO.VABWTS]
Failed to execute (JSONToFeatures).

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

your test fc going to the file geodatabase is named 'test' (ok) and your sde fc is named 'Trail.DBO.VABWTS'.  It should just be VABWTS, the database adds the 'Trail.dbo.' part and is probably throwing the error based on the periods in the name, which are invalid.

0 Kudos
jaykapalczynski
Frequent Contributor

@JeffK ok so I name the file without DB and DBO

This is the full code without removing anything....

 

Still getting this stupid error...

File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000206: Cannot create FeatureClass 'E:\ArcGIS_Server_aws\ArcGIS_Published_MXDs\BirdingTrail\vdwrdwradminjkVBWTRack30.sde\VBWT.DBO.testNew55'. Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'R15_pk'. Cannot insert duplicate key in object 'dbo.TESTNEW55'. The duplicate key value is (0).] [VBWT.DBO.testNew55]
Failed to execute (JSONToFeatures).

 

I am baffled as to why it will not write to the SDE DB

 

import arcpy
import json
import uuid
import os
import re
import requests

# SCRIPT GLOBAL VARIABLES
arcpy.env.workspace = 'E:/ArcGIS_Server_aws/ArcGIS_Published_MXDs/BirdingTrail/'
fsURL = "https://dwr.virginia.gov/-/api/v1/vbwt-sites"

dataInput = r"E:/ArcGIS_Server_aws/ArcGIS_Published_MXDs/BirdingTrail/JSON_New4.json"
output_fc = r"E:/ArcGIS_Server_aws/ArcGIS_Published_MXDs/BirdingTrail/vdwrdwradminjkVBWTRack30.sde/testNew55"
    
fld_dict = {}
new_dict = {}
converted_list = []
p_tag = re.compile(r'.*?>(.*)<')


def main():
    # Read URL and truncate long fields
    readURL()
    # delete existing Feature Classes
    deleteExistingFC()

def readURL():
    r3 = requests.get(fsURL)
    data = json.loads(r3.text)
    print("")
    print("There are: " + str(len(data)) + " records")
    print("")

    # Added this So I could use it in the For loop
    data1 = {"BirdTrailLocations":  data }
    for i in data1['BirdTrailLocations']:
        feat_dict = {} 
        feat_dict["geometry"] = {"x": i["site_longitude"], "y": i["site_latitude"]}
        
        ## the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
        fixSiteFacilities = str(i["site_facilities"])
        newValSiteFacilities = fixSiteFacilities.replace("u'", "").replace('[', '').replace(']', '').replace(r"'", r'')
        stringTrunSiteFacitlities = newValSiteFacilities[0:255]
        i["site_facilities"] = str(stringTrunSiteFacitlities)
        # Truncate Site Access
        fixSiteAccess = str(i["site_access"])
        newValSiteaccess = fixSiteAccess.replace("u'", "'").replace('[', '').replace(']', '').replace(r"'", r'')
        stringTrunSiteAccess = newValSiteaccess[0:255]
        i["site_access"] = (stringTrunSiteAccess)   
        # Truncate Site Description
        varSiteDescription = str(i["site_description"])
        stringTrunSiteDescription = varSiteDescription[0:255]
        i["site_description"] = (stringTrunSiteDescription)
        # Truncate Site Directions
        varSiteDirections = str(i["site_directions"]) 
        stringTrunSiteDirections = varSiteDirections[0:255]
        i["site_directions"] = (stringTrunSiteDirections)
        # Truncate Website
        correctedWebsite = str(i["site_website"])
        newWebsiteVal = re.sub(r'/', r'\\', correctedWebsite)
        newWebsiteVal = newWebsiteVal.replace('\\\\', '\\')
        newWebsiteVal2 = newWebsiteVal[0:255]
        i["site_website"] = newWebsiteVal2
        
        feat_dict["attributes"] = i
        converted_list.append(feat_dict)

    new_dict = {"geometryType" : "esriGeometryPoint",
                 "spatialReference" : {"wkid" : 4326},
                'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
                'features': converted_list}

    # write this truncated capture to a local file for further processing this is a requirement for this to work per ESRI
    writeLocalFileURLText(new_dict)    


def writeLocalFileURLText(urlResults):
    new_dict = urlResults
    print (new_dict)
    with open(dataInput, 'w') as f:
        json.dump(new_dict, f)

def deleteExistingFC():
    arcpy.env.workspace = r"E:/ArcGIS_Server_aws/ArcGIS_Published_MXDs/BirdingTrail/vdwrdwradminjkVBWTRack30.sde"
    arcpy.env.overwriteOutput = True
    arcpy.JSONToFeatures_conversion(dataInput, output_fc)
    arcpy.env.overwriteOutput = False

if __name__ =="__main__":
    main()

 

0 Kudos
BrianWilson
Occasional Contributor II

Maybe you can load the JSON into an in-memory feature class and then use "Feature Class to Feature Class" to write it to the database.

Yes, clumsy, but does it work?

0 Kudos
jaykapalczynski
Frequent Contributor

@BrianWilsonthe crazy thing is this code works fine when I WRITE to a File Geodatabase.

0 Kudos
BrianWilson7
Regular Contributor

Yes, leverage that by writing output to a destination that works (FGDB) and then using a well-used tool (feature class to feature class) 

Either it will just work and you can eat lunch today unconcerned about the error you used to get or it will give you a different and more useful error message. 

In Esri land you always have to make 3 extra copies of your data so this approach should be considered normal.

 

JeffK
by MVP Regular Contributor
MVP Regular Contributor

I'm curious about the primary key statement included in the error. It could be related to the invalid name issue (SQL errors can piggyback sometimes), or could be that the JSONToFeatures does not create/designate an UniqueID in its output and requires you create/ designate one.  Do you get an prompt to create one when you add it to a map?  Maybe it gets created when written to the fgd, but since a SQL database can be more restrictive, it is not getting generated on insert.

--

unique-identifier 

If a qualifying field is present, ArcGIS sets the first not-null field it finds as the unique identifier field by default when you drag a database table from the Catalog tree and place it in ArcMap or validate a query layer definition. This is often an adequate value to use as the unique identifier field, but you can choose another qualifying field or fields from the Unique Identifier Field(s) list to be used instead.

 

I'd solve the name issue first though.

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

it looks like this problem has been an issue since 2015:

BUG-000088916

solution is to do:

1. Run the JSON to Features tool with the output to a file geodatabase.
2. Copy and paste the feature class to SDE.

0 Kudos