AnsweredAssumed Answered

Python GP tool to edit Postgres table

Question asked by ejparry on Mar 11, 2017
Latest reply on Mar 13, 2017 by kcblessm

Problem: can't add new.rexord to related table in AGOL, so appreciate any feedback on if a Python script could work / if the workflow makes sense in general.


I am trying to create a spatially enabled document database, with a few more capabilities than the basic attachment manager. Collector is a possibility, but this is a school project, and I'm trying to get this to work as an enterprise gdb.


The first goal is to have a web app in which a user can add a document and enter attributes (author, type of document and the site it is related to).  A document can relate to one or many sites. I know it is not best DB design, but if a document relates to several sites, it is entered as a separate record each time for each site.


The second goal is to be able to SQL and spatial query- e.g. "which sites have documents of category x", "which sites have documents expiring in 30 days", "which documents are related to this group of sites"


The basic workflow in desktop is to start edit session, add a new record to tbl_doxuments, use attachment manager to upload a document to that record. When publishing as feature service, no built in widgets have the ability to add a new record to the related table (documents)



1) FC_sites: point FC with names of sites. 1:m rc with tbl_doxuments 

2) Tbl_documents: postgres table with each documents attributes, and attachments have been enabled. The site name is also entered and that is the key to FC_sites

3) Tbl_documents_ATTACH: actual storage of each doc.


Proposed Python script

1) create a new record in tbl_documents - user enters document attributes. Retrieve the new object id

2) create a temporary match table for the one document to the one new record. (New OBJ ID+ filename)

3) call add attachments to add the file to the docunents table.


I would then publish this as a GP tool and set the parameters for the user to enter the various attributes/file path and upload the doc. I will probably then work with enhanced query widget to set up the different queries.



-Will using the Python gp tool actually be able create the new record in the Postgres DB?


- I do have a "BYTEA" field already in the tbl_documents, so possible to add a document directly to that table, but to use arcpy tools, think I have to have the separate documents_ATTACH table. 


I truly appreciate any feedback, and I will try to post code as I get it to work. Thanks