arcpy and SDE outside of ArcGIS

RobMcCulley · ‎10-16-2013

I'm having trouble accessing ArcSDE Feature Classes from Python running outside of ArcGIS 10.2.

I put together a quick test script:

import arcpy

# Test for existence of various datasets, both in SDE and shapefile
for pth in (r"Database Connections\GIS.sde",
               r"C:\Users\RMcCulley\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\GIS.sde",
               r"Database Connections\GIS.sde\gis.cvr.Basemap\gis.cvr.Townships",
               r"G:\Parcel GIS Files\Townships.shp"):
    if arcpy.Exists(pth):
        value = pth + " Exists"
    else:
        value = pth + " Doesn't Exist"
    arcpy.AddMessage(value)

# Try FeatureClassToFeatureClass to export from Shapefile to Shapefile
if arcpy.Exists(r"C:\Temp\Townships_From_SHP.shp"): 
    arcpy.Delete_management(r"C:\Temp\Townships_From_SHP.shp")
try:
    value = arcpy.FeatureClassToFeatureClass_conversion(in_features=r"G:\Parcel GIS Files\Townships.shp",out_path=r"C:\Temp",out_name="Townships_From_SHP.shp")
except:
    value = "FeatureClassToFeatureClass Failed"
arcpy.AddMessage(value)

# Try FeatureClassToFeatureClass to export from SDE to Shapefile
if arcpy.Exists(r"C:\Temp\Townships_From_SDE.shp"): 
    arcpy.Delete_management(r"C:\Temp\Townships_From_SDE.shp")
try:
    value = arcpy.FeatureClassToFeatureClass_conversion(in_features=r"Database Connections\GIS.sde\gis.cvr.Basemap\gis.cvr.Townships",out_path=r"C:\Temp",out_name="Townships_From_SDE.shp")
except:
    value = "FeatureClassToFeatureClass Failed"
arcpy.AddMessage(value)

If I run this from Toolbox inside of ArcGIS the output is:

Executing: arcpytesting
Start Time: Wed Oct 16 13:12:03 2013
Running script arcpytesting...
Database Connections\GIS.sde Exists
C:\Users\RMcCulley\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\GIS.sde Exists
Database Connections\GIS.sde\gis.cvr.Basemap\gis.cvr.Townships Exists
G:\Parcel GIS Files\Townships.shp Exists
C:\Temp\Townships_From_SHP.shp
C:\Temp\Townships_From_SDE.shp
Completed script arcpytesting...
Succeeded at Wed Oct 16 13:12:04 2013 (Elapsed Time: 1.00 seconds)

Everything works fine!

If I run this at the command line using C:\Python27\python.exe arcpytesting.py the output is:

Database Connections\GIS.sde Exists
C:\Users\RMcCulley\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\GIS.sde Exists
Database Connections\GIS.sde\gis.cvr.Basemap\gis.cvr.Townships Doesn't Exist
G:\Parcel GIS Files\Townships.shp Exists
C:\Temp\Townships_From_SHP.shp
FeatureClassToFeatureClass Failed

The feature class inside the SDE database doesn't exist, and the FeatureClassToFeatureClass from the SDE database fails.

The exception is:

arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Features: Dataset Database Connections\GIS.sde\gis.cvr.Basemap\gis.cvr.Townships does not exist or is not supported
Failed to execute (FeatureClassToFeatureClass).

Is there something I'm missing? Should this script work outside of ArcGIS? It seems strange that arcpy outside of ArcGIS works fine on shapefiles or file geodatabases (I tested this as well), but not on SDE datasets.

ZachLiu1 · ‎10-16-2013

Have you tried to use real sde connection file location instead of "Database Connections\\"?

The connection files are usually at "C:\Users\username\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog"

RobMcCulley · ‎10-16-2013

Have you tried to use real sde connection file location instead of "Database Connections\\"?

The connection files are usually at "C:\Users\username\AppData\Roaming\ESRI\Desktop10.0\ArcCatalog"

I've tried it with the same result.

RobMcCulley · ‎10-16-2013

I think I've figured out my problem ... sort of.

At version 10.1 I was using a full SDE installation, and scripts running at the command line connected to the database no problem using the .sde file.

When we moved to 10.2, we dropped the SDE install (because the SDE application server is being phased out by Esri). Instead we followed the Setting" rel="nofollow" target="_blank">http://resources.arcgis.com/en/help/main/10.2/index.html#/Se... up a Geodatabase in PostgreSQL instructions.

This setup works fine inside of ArcGIS, but I can't connect to it using any other python interpreter than the one in ArcGIS. Is there a way to work with PostgreSQL direct connections from python?

MathewCoyle · ‎10-16-2013

If it creates an sde connection file and you can use it in ArcGIS, that should be all arcpy needs to connect to the database. And I may be mistaken, but even using direct connects I believe you need to have SDE installed for your database.

RobMcCulley · ‎10-17-2013

If it creates an sde connection file and you can use it in ArcGIS, that should be all arcpy needs to connect to the database. And I may be mistaken, but even using direct connects I believe you need to have SDE installed for your database.

I have the ST_Geometry.dll installed in the lib directory of postgresql. That seems to be all I need to do. I can view, edit, delete, and create features in the geodatabase, and the SDE application is not installed on the server. When I connect to the database it creates an sde connection file, which is the file I'm trying to use in arcpy.

What's interesting, when I run the following at the command line:

arcpy.CreateDatabaseConnection_management("C:\\Temp","GISTest","POSTGRESQL","<server>","DATABASE_AUTH","<user>","<password>","SAVE_USERNAME","gis")

It's successful. An sde connection file is created, and I can browse the geodatabase in ArcCatalog. I still can't access any of the data in the geodatabase outside of ArcGIS though.

EthanGranger · ‎11-11-2013

I'm having the same problem. I'm on 10.1 and PostgreSQL and my code was working last week and now I can't access any of my feature classes.

Here is test code that WORKS in the ArcCatalog immediate windows and not from the command prompt:

username = >USERNAME<
conn1 = "c:/Users/"+username+"/AppData/Roaming/ESRI/Desktop10.1/ArcCatalog/spatialdb_vectors_arcadmin.sde/"
lyr = conn1 + "vectors.arcadmin.AIRPORTS"

if arcpy.Exists(lyr): 
 print "Feature Class Found!"
else: 
 print "FAILURE :("

Strangely, if I run this code with the above variables, it works:

if arcpy.Exists(conn1): 
 print "DATABASE FOUND!"
else: 
 print "Failure"

Can anyone help? Thanks in advance.

RhondaGregory · ‎06-03-2016

Any resolution on this?

I am trying to automate the migration of data using geoprocessing tools. So I am starting with an XML Workspace Document that I exported using ArcCatalog. When I try to import this into a new geodatabase, I tried dragging the db connection into the geoprocessing tool (ImportXMLWorkspaceDocument) and it fails saying 'Cannot access local or remote database'.

This happens with multple tools, i.e., EnableAttachments_management, Append_management and ImportXMLWorkspaceDocuemnt)

Any thoughts?

RobMcCulley · ‎12-19-2018

I figured this one out a long time ago - and forgot to mention the resolution to this problem.

arcpy requires numpy, but doesn't seem to import it automatically. If I import numpy before importing arcpy, arcpy works perfectly.

So short answer - every script you're planning to run at the command line, or in an interpreter, should start with:

import numpy

import arcpy