topic Accessing Oracle from Python for automated publishing to ArcGIS for Server in Python Questions

Accessing Oracle from Python for automated publishing to ArcGIS for Server

Jay_Gregory — Fri, 14 Mar 2014 16:35:12 GMT

Hi, I've been tasked with automating the daily publishing of an Oracle table to an ArcGIS for Server instance. There is no SDE installed on Oracle, and I have little control over the database instance itself. My solution to this is the following:
1. Install ArcMap on a server machine.
2. Have a recurring Python script that updates a local geodatabase with a fresh copy of the table from the Oracle instance.
3. Automate republishing of the data to an ArcGIS for server instance using ArcGIS for Server admin utilities and arcpy.mapping.

Is there a simpler way to do this? If not, does anyone have any tips on how to get started on accessing Oracle database through Python using OBDC connections?

Thanks!

Re: Accessing Oracle from Python for automated publishing to ArcGIS for Server

MikePowell — Fri, 14 Mar 2014 19:12:25 GMT

I am definitely not the greatest programmer and I probably can't help too much but I import "cx_Oracle" module in python and that allows me to query oracle tables through sql. I don't know if I am saying that correctly. You can check out my recent post where I had another issue, and see in the script that is attached to it what I am referring to. It is a starting point anyway. I think there are other modules to import that can connect you to oracle as well, just not sure exactly what they are. Hope that helps a little.

Re: Accessing Oracle from Python for automated publishing to ArcGIS for Server

FilipKrál — Sat, 15 Mar 2014 10:45:09 GMT

The cx_Oracle module digmapper mentioned is developed by Oracle but some people prefer pyodbc module. In any case you need the Instant Oracle Client installed on the computer that will connect to your Oracle database. If the client has not been installed, it may take a while to figure out what version you need to install and how.

The solution you propose should work but I am thinking there must be an easier way. I'd try to add the table to ArcMap using one of the many options
(
oledb connection
Enterprise" rel="nofollow" target="_blank">http://resources.arcgis.com/en/help/main/10.1/index.html#//00290000003q000000]Enterprise database connection
other...?
you may need to use Make Query Table tool
)
and publish the document as a service (if validation allows you to do that).

If you can manage to register the database in your Server's data stores, will the service reflect changes in the database instantly? If you don't want instant changes, you might need to go with the approach you outlined.

Filip.