[CentralOH] Python and Oracle

[Catherine Devlin]

On Tue, Dec 8, 2009 at 10:40 PM, Michael S. Yanovich <
yanovich at cse.ohio-state.edu> wrote:

> I've started a challenge in Python (I may have gotten myself in over my
> head) where I want to create a Python script that would access a *remote*
> Oracle database and provide the contents of a certain entry in the database.
> I've done some research on this and it seems that I would need either
> cx_Oracle or PyODBC.
>
> For cx_Oracle it looks like I need to have Oracle installed locally to use
> it.


That's true, but vastly easier than it sounds.  You don't need Oracle server
software, just client software.  The Oracle 10g Express client is free and
easy to install, and doesn't even have a terribly big footprint (for
Oracle):

http://www.oracle.com/technology/software/products/database/xe/index.html

As for SQLAlchemy... it is a new set of things to learn, so it depends on
how much you expect to muck around with databases (on this project and
others).  If just a touch, then it might not be worth your while to learn.
If quite a bit, it's definitely worthwhile.

PyODBC is extremely similar, since it's also a DB-API2 client.  In that
case, you'd have to worry about ODBC configuration.  One feature that PyODBC
provides (that cx_Oracle doesn't, because it wasn't written into the DB-API2
specification, though it really should have been) is accessing row results
by their column names

cursor.execute("select user_id, user_name from users")
for row in cursor:
    print row.user_id, row.user_name


(other DB-API2 packages return raw tuples, without any direct association to
the column names, and you have to reference the cursor.description object)

However, I have found some useful tutorials on using cx_Oracle that make it
> look easy for what I want to accomplish.
>

I hope my PyOhio tutorial was among them!
http://catherinedevlin.pythoneers.com/pyora_tutorial.tar.gz


> The server I would like to remotely connect to doesn't have a documented
> API, but it does have a publicly search-able database that anyone can
> connect to and browse. I know it is built on Oracle. I have determined this
> by the error messages and such the system throws at someone. I am assuming
> though that the information I am looking for is in a database.
>
> The database is:
> https://courses.osu.edu/psp/hcosuct/EMPLOYEE/HRMS/c/COMMUNITY_ACCESS.OSR_CAT_SRCH.GBL
>
> Ah, now you're going to need some cooperation from the owners of the
database.  If it is in Oracle, they'll have to permit Oracle*Net access
(usually port 1521) from outside their organization; most organizations do
not.  You'll also need the address of the database hosting machine (not
necessarily the same as the web server), a username/password on the database
(many applications have *application* user accounts that are separate from
*database* user accounts), and... yeah, lots of cooperation.

If you're trying to access this data as a member of the anonymous public,
you'll probably end up needing to write scripts against the web interface
after all... in which case you won't be accessing the database directly,
you'll be writing Windmill scripts or something, and you won't know or care
whether the data is in Oracle, Tokyo Tyrant, or flat files.

Or do SQL injection.  :)  But I tried a little of that, and it revealed that
the application uses PeopleCode, so there's PeopleSoft running the app.
(Which, I dearly hope, is armored against SQL injection.)  I'd better stop
hacking them now, at least not from a work address.  PeopleSoft belongs to
Oracle now, so it's likely that the underlying database is indeed Oracle.

Good luck!
-- 
- Catherine
http://catherinedevlin.blogspot.com/
*** PyCon * Feb 17-25, 2010 * Atlanta, GA * us.pycon.org ***
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/mailman/private/centraloh/attachments/20091209/b60eb83d/attachment-0001.htm>