Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I need to access data that is inside an oracle database using a python script. Its a Unix server running oracle 10g. I dont have much access to the database server.

It works fine on model builder when the connection is open and I add my data, but exporting it to python means all the connection properties are gone (they are just "TABLE NAME" etc.)

Bascially the script grabs this information based on a query, lets say "SELECT id WHERE Status = 0", and it writes it to a new table in a filegeodatabase. I need to do it through python as I am processing it further, but thats not my issue.

I just cant get it to connect. I've tried to use pyodbc plugin using the syntax below:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=rtsprod;DATABASE=DBNAME;UID=USERID;PWD=PASSWORD')

Now i know its an oracle database but cant find any doco on how exactly to connect using the pyodbc plugin. Is there a better way?

Thanks for any help!

share|improve this question
4  
This is more of a StackOverflow question, where it would be a duplicate of stackoverflow.com/questions/10586361/… see also related tags: stackoverflow.com/questions/tagged/pyodbc+oracle –  Mike T Jun 6 '12 at 6:01
add comment

closed as off topic by blah238, whuber Jun 6 '12 at 14:31

Questions on Geographic Information Systems Stack Exchange are expected to relate to geographic information systems within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers

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.

share|improve this answer
    
Thats great, thank you very much. –  sprocket Jun 6 '12 at 6:43
add comment

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)
share|improve this answer
add comment

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.
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.