topic Re: Reference non-SDE sql table in python script - arcpy.Append_management in Python Questions

Reference non-SDE sql table in python script - arcpy.Append_management

AllenScully — Wed, 21 Feb 2018 20:52:49 GMT

I'm wondering if anyone can help with the syntax (or confirm that it can be done) to reference an SQL table that's in a non-spatial database in a python script

In my case I'm trying to append from this table into an SDE table. I need the field mapping, because there is an ID field in the source table (non-spatial) that needs to be preserved and by default it treats it as an object id field, so it changes every time the job is run, which is a problem. So it is mapped to the correct column in the target (SDE) table.

The connection to the SQL non-spatial database is fine in ArcMap, and I can run the append tool without issue. Whne I grab the python snippet, no matter how I modify the table name/path, the Append_management section fails, saying 'Cannot open table...' (the input table).

Here's what I have: (only import currently is arcpy)

arcpy.Append_management

(inputs="SQLDB.TABLENAME.dbo.users", *this is what the table is listed as under 'properties' in the catalog window in ArcMap

target= SDE_DEV + "\TABLENAME_DEV",

schema_type="NO_TEST",

field_mapping=....)

SDE_DEV is a variable that is the path to the SDE connection file + sde file name to our dev sde database. I've tried the same methodology for the input dataset, with no luck so far.

Gracias -

Allen

Re: Reference non-SDE sql table in python script - arcpy.Append_management

JoshuaBixby — Thu, 22 Feb 2018 13:56:30 GMT

If you can run the Append tool in the GUI without any issue; then go to the Results window, find the successful run, and then use the "Copy As Python Snippet" to see how the tool is setting up the arguments.

Using the Results window—Help | ArcGIS Desktop

Re: Reference non-SDE sql table in python script - arcpy.Append_management

AllenScully — Thu, 22 Feb 2018 16:38:53 GMT

Thanks Josh -

The snippet from the tool result is in fact the original source for my code here. It's been my experience that the snippet rarely works as-is with no modifications in an IDE (especially relating to datasources), and that was the case here, so I've tried many permutations of the paths.

I currently have it so that the Append_management does read the input data:

(inputs=r"\\ServerName\DATABASECONNECTIONS\SQLCONNECTION.sde\UserName.dbo.tablename", target=...)

where the DATABASECONNECTIONS folder contains the appropriate .sde connection file for the database in question.

And it actually adds the correct # of records to the target. however every attribute minus objectid is null, which is fun. I can figure that out - was mainly trying to see how to reference a non-spatial SQL table within this tool, and confirm that it's possible in an arcpy tool within a python script.