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
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.
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.