connect to enterprise geodatabase in script

JaredPilbeam2 · ‎08-06-2019

This question is based off of another one I just had.

I can't seem to update a feature dataset in a sql server enterprise geodatabase. I have sysadmin user role access to the database. One thing I've tried is copying the path of the connection file from an ArcPro project and using that in the script. This particular script here works perfect if I use it on a file geodatabase inside a folder. Basically, I'm not sure how to connect to the enterprise geodatabase.

"""
update the feature classes in the feature dataset gisedit.DBO.dFIRM_2019_Effective
from the shapefiles in this directory:
\\gisfile\GISsources\Federal\FEMA\DFIRM\EffectiveProducts\SHP\17197C_WillCo_FIRMdb
"""

import arcpy
from arcpy import env
import os

#shapefile variable
shp = r'\\gisfile\GISsources\Federal\FEMA\DFIRM\
EffectiveProducts\SHP\17197C_WillCo_FIRMdb'
#sde connection file variable
sde = r'C:\Users\jpilbeam\AppData\Local\Temp\ArcGISProTemp11396\
d532de7f-5c8c-43c9-94bc-3cf9407b9505\gissql.sde\dFIRM_2019_Effective'

env.workspace = shp
#populate the fcList
fcList = arcpy.ListFeatureClasses()

env.workspace = sde
env.overwriteOutput = True
#iterate list of shapefiles and copy to sde feature dataset
for shapefile in fcList:
    in_shapefile = os.path.join(shp, shapefile)
    out_featureclass = os.path.splitext(shapefile)[0]
    print('Copying %s to %s' %(in_shapefile, out_featureclass))
    arcpy.CopyFeatures_management(in_shapefile, out_featureclass)
##
print("done")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Error:

Traceback (most recent call last):
  File "\\gisfile\GISstaff\Jared\Python Scripts\ArcGISPro\dFIRM.py", line 30, in <module>
    arcpy.CopyFeatures_management(in_shapefile, out_featureclass)
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 2948, in CopyFeatures
    raise e
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 2945, in CopyFeatures
    retval = convertArcObjectToPythonObject(gp.CopyFeatures_management(*gp_fixargs((in_features, out_feature_class, config_keyword, spatial_grid_1, spatial_grid_2, spatial_grid_3), True)))
  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
    return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000733: Output Feature Class: Same as input Input Features
Failed to execute (CopyFeatures).
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

EDIT:
I forgot to mention that the shapefiles and the features in the dataset have the same names. I need to update the dataset every month with these shapefiles.

LanceCole · ‎08-06-2019

Jared,

Your SDE connection string should be stored in your ArcGIS Pro Project folder with an sde extension not in the path you are referencing. You can also use the Create Database Connection tool in Pro to Create and save a connection to your SDE database. I save them to a common location on my system or network share. Just be careful if you include username and passwords with database authentication as the SDE file can be used by anyone.

Please also insure your logon credentials have sufficient permissions to add and delete feature classes to the SDE database. You can test this once you create the SDE connection, just try to add then delete a feature class in Catalog. You may need to use the SDE or DBO user to add/delete a feature class.

JaredPilbeam2 · ‎08-07-2019

Lance,

Thanks. I changed the sde variable to a network directory sde connection file:

sde = r'\\gisfile\GISstaff\Jared\connectionadmin.sde\gisedit.DBO.dFIRM_2019_Effective'‍‍‍

It threw an error:

ERROR 000733: Output Feature Class: Same as input Input Features
Failed to execute (CopyFeatures).‍‍‍‍‍‍

I wonder if this has to do with env.overwriteOutput not being too reliable.

EDIT:

That was the wrong dataset name. It should be:

sandbox.DBO.dFIRM_2019_Effective‍

I ran it again with this and it gave me the schema lock error:

ERROR 000464: Cannot get exclusive schema lock.  Either being edited or in use by another application or service.
Failed to execute (CopyFeatures).‍‍

I have nothing open except for Pyscripter? And nobody in my office is logged in as gisadmin?

LanceCole · ‎08-07-2019

Jared,

Go into catalog and open the connection you have listed above. Navigate to dFRIM_2019_Effective dataset and see if you can add and delete a feature class. If you can it may be the overwriteOutput. If allowable and you have a backup, see if you can delete one of the feature classes you are trying to over write. If those work, try adding a delete_Management statement to your code to remove the feature class before writing the new.

EDIT, just saw your revision. No one else can be on the SDE database to modify the schema and add/delete feature classes. Make sure others are not logged on or that you do not have multiple sessions open. We have this issue when someone locks their workstation rather than logging off at the end of the day. Have to terminate their connection forcibly but they have been warned.

You also need appropriate access level, test the connection as I noted above. You may need to log on to the database using the SDE or DBO credentials to modify the schema.

SaM1 · ‎10-19-2021

Hi, i have seen in your post that you have shapefiles and the feature in the dataset have the same names, i have a similar situation where I have sde database and file geodatabase. both have same name of feature classes, and i have to update sde using file gdb every month. I am writing script to deleteRows from sde and append data from file geodatabase, this deleteRows delete everything form both dataset. was wondering if you can guide me with your expenrience