PUBLIC MARKS from pvergain with tags python & oracle

There are countless reasons for parsing data, as well as tools and techniques to do it. But even the "right" tool may be insufficient when you need to do something new with the data. The same concerns exist for the integration of heterogeneous data sources. Sooner or later, the right tool for the right job happens to be a programming language. Oracle offers some very powerful utilities for loading, processing, and unloading data. SQL*Loader, Data Pump, external tables, Oracle Text, regular expressions—it's all there. Yet there is often a need to do things outside the database (or, trivially, perhaps you just weren't granted the necessary database privileges). Python delivers possibilities for efficient data parsing at a high level. The extensive standard library and many modules available for free on the Internet make it possible to work with data logic rather than dissecting bytes by hand.

Starting with the Python 2.4 release, cx_Oracle handles DATE and TIMESTAMP datatypes natively, mapping values of such columns to Python datetime objects from the datetime module. This offers certain advantages as datetime objects support arithmetic operations in-place. Built-in time zone support and several dedicated modules make Python a real time machine. The transition between Python and Oracle date/time datatypes is completely transparent to developers thanks to cx_Oracle's mapping mechanisms. Python developers might find Oracle's date arithmetic a bit odd at first, but only with a few tips it becomes completely clear and very reasonable. This part of the series will give you an in-depth understanding of date arithmetic from both Oracle and Python's point of view. Each of them offers rich support for handling date/time datatypes, so it is the programmer's choice which one to rely on. If you tend to put application logic inside the database or whether you prefer to encapsulate date/time operations in the application itself, the seamless integration of Oracle with Python offers you maximum flexibility with limited programming effort.

Among the core principles of Python's way of doing things there is a rule about having high-level interfaces to APIs. The Database API (in this case the Oracle API) is one example. Using the cx_Oracle Python module from Computronix, you can take command over the Oracle query model while maintaining compatibility with Python Database API Specification v2.0. The model of querying databases using DB API 2.0 remains consistent for all client libraries conforming to the specification. On top of this, Anthony Tuininga, the principal developer of cx_Oracle, has added a wide set of properties and methods that expose Oracle-specific features to developers. It is absolutely possible to use only the standard methods and forget about the "extra" ones, but in this installment you won't be doing that. The concept of universal database wrappers might work in some cases but at the same time, you lose all the optimizations that the RDBMS offers.