Python script: Access data from databases [closed]

Checkout cx_Oracle - Documentation here on making the connection.

cx_Oracle.connect([user, password, dsn, mode, handle, pool, threaded, twophase, events, cclass, purity, newpassword])

Constructor for creating a connection to the database. Return a Connection object (Connection Object). All arguments are optional and can be specified as keyword parameters.

For future reference, my final connection string was (after a bit more digging):

Theres a print message there to test it works.

import cx_Oracle, os, sys

def db_connect():
    # Build a DSN (can be subsitited for a TNS name)
    dsn = cx_Oracle.makedsn("ServerName", 1521, "DatabaseName")
    db = cx_Oracle.connect("username", "password", dsn)
    cursor = db.cursor()
    return cursor

def db_lookup(cursor):
    cursor.execute("SELECT SERVICEID FROM SERVICE WHERE STATUS = 0")
    row = cursor.fetchone()
    print row
    return row

cursor = db_connect()
#db_connect()
db_lookup(cursor)

There are a number of built-in options for connecting to Oracle and other DBMS's without using 3rd-party Python modules (not that you couldn't or shouldn't just that there are reasonable alternatives):

• SDE Connection Files: If your table is located on an SDE geodatabase, you can use these just as you would any other workspace from arcpy. E.g. arcpy.env.workspace = r"Database Connections\mySDEConnection.sde"

  • The "Database Connections" shortcut normally points to %APPDATA%\ESRI\Desktop10.0\ArcCatalog; you could also copy these files to another location if required.

  • You can create SDE connection files in ArcCatalog or with the Create ArcSDE Connection File (Data Management) tool.

  • If your table is not located on an SDE geodatabase, but your database administrator is willing to create a database link, you could access the non-SDE Oracle database from an SDE connection file through the DB link using a query with the ArcSDESQLExecute command in arcpy.

• OLE DB Connection Files: Create an OLE DB Connection in ArcCatalog and use it as you would any other workspace from arcpy. E.g. arcpy.env.workspace = r"Database Connections\myOracleDB.odc"

  • There is currently no geoprocessing tool to let you create OLE DB Connection files, so this must be done beforehand in ArcCatalog.

• The Make Query Table (Data Management) tool:

  This tool applies an SQL query to a database and the results are represented in a layer or table view. The query can be used to join several tables or return a subset of columns or rows from the original data in the database.

  This tool accepts data from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase, or an OLE DB connection.

• The Data Interoperability Extension: This is an integrated version of FME (Feature Manipulation Engine) by Safe Software. See licensing and other info here. There are at least a couple of ways you could use this to access your Oracle table.

  1. You could create an interoperability connection to your Oracle database through the ODBC reader (which has direct-read support; see this list for supported formats and whether they are direct-read, import or export -- importantly, Oracle Non-Spatial does not appear to be direct-read) and use it like any other workspace.
  2. You could use the Quick Import (Data Interoperability) tool to copy the Oracle table to a temporary "staging" file or personal geodatabase.
  3. Depending on how fancy you want to get, you could create a simple Spatial ETL tool that just reads and outputs the table, or you could do your entire workflow in it. You could then call this tool from ModelBuilder or Python.
  4. You could create a custom format that does everything the spatial ETL tool does, but lets you use the result as an interoperability connection or Quick Import format.