As a first step, get familiar with the basic concepts of Oracle-Python connectivity. 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. I was trying to connect to Oracle DB hosted in AWS using the code as below: The log message returns: 32-bit Oracle Client library cannot be loaded: 'The specified module could not be found'. Is my argument for cxOracle.connect or my path in the environment variables wrong? Python, WHL and Instant Client are all 32-bits. Python is a popular general purpose dynamic scripting language. With the rise of Frameworks, Python is also becoming common for Web application development. If you want to use Python and an Oracle database, this tutorial helps you get started by giving examples. It is recommended that you complete this OBE first.

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.

Introducing DB API 2.0 and cx_Oracle

The Python Database API Specification v2.0 is a community effort to unify the model of accessing different database systems. Having a relatively small set of methods and properties, it is easy to learn and remains consistent when switching database vendors. It doesn't map database objects to Python structures in any way. Users are still required to write SQL by hand. After changing to another database, this SQL would probably need to be rewritten. Nevertheless it solves Python-database connectivity issues in an elegant and clean manner.

The specification defines parts of the API such as the module interface, connection objects, cursor objects, type objects and constructors, optional extensions to the DB API and optional error handling mechanisms.

The gateway between the database and Python language is the Connection object. It contains all the ingredients for cooking database-driven applications, not only adhering to the DB API 2.0 but being a superset of the specification methods and attributes. In multi-threaded programs, modules as well as connections can be shared across threads; sharing cursors is not supported. This limitation is usually acceptable because shareable cursors can carry the risk of deadlocks.

Python makes extensive use of the exception model and the DB API defines several standard exceptions that could be very helpful in debugging problems in the application. Below are the standard exceptions with a short description of the types of causes:

• Warning—Data was truncated during inserts, etc.
• Error—Base class for all of the exceptions mentioned here except for Warning
• InterfaceError—The database interface failed rather than the database itself (a cx_Oracle problem in this case)
• DatabaseError—Strictly a database problem
• DataError—Problems with the result data: division by zero, value out of range, etc.
• OperationalError—Database error independent of the programmer: connection loss, memory allocation error, transaction processing error, etc.
• IntegrityError—Database relational integrity has been affected, e.g. foreign key constraint fails
• InternalError—Database has run into an internal error, e.g. invalid cursor, transaction out of synchronization
• ProgrammingError—Table not found, syntax error in SQL statement, wrong number of parameters specified etc.
• NotSupportedError—A non-existent part of API has been called

The connect process begins with the Connection object, which is the base for creating Cursor objects. Beside cursor operations, the Connection object also manages transactions with the commit() and rollback() methods. The process of executing SQL queries, issuing DML/DCL statements and fetching results are all controlled by cursors.

cx_Oracle extends the standard DB API 2.0 specification in its implementation of the Cursor and Connection classes at most. All such extensions will be clearly marked in the text if needed.

Python Oracle Db Driver

Getting Started

Before working with queries and cursors, a connection to the database needs to be established. The credentials and data source names can be supplied in one of several ways, with similar results. In the extract from the Python interactive session below, connection objects db, db1 and db2 are all equivalent. The makedsn() function creates a TNS entry based on the given parameter values. Here it is being assigned to the variable dsn_tns. When environment settings are properly set then you can use the shorter form cx_Oracle.connect('hr/hrpwd'), skipping even the Easy Connect string used for db and db1.

Within the scope of a Connection object (such as assigned to the db variable above) you can get the database version by querying the version attribute (an extension to DB API 2.0). This can be used to make Python programs Oracle-version dependent. Likewise, you can get the connect string for the connection by querying the dsn attribute.

Cursor Objects

You can define an arbitrary number of cursors using the cursor() method of the Connection object. Simple programs will do fine with just a single cursor, which can be used over and over again. Larger projects might however require several distinct cursors.

Application logic often requires clearly distinguishing the stages of processing a statement issued against the database. This will help understand performance bottlenecks better and allow writing faster, optimized code. The three stages of processing a statement are:

1. Parse (optional)
   • cx_Oracle.Cursor.parse([statement]) Not really required to be called because SQL statements are automatically parsed at the Execute stage. It can be used to validate statements before executing them. When an error is detected in such a statement, a DatabaseError exception is raised with a corresponding error message, most likely 'ORA-00900: invalid SQL statement, ORA-01031: insufficient privileges or ORA-00921: unexpected end of SQL command.'
2. Execute
   • cx_Oracle.Cursor.execute(statement, [parameters], **keyword_parameters) This method can accept a single argument - a SQL statement - to be run directly against the database. Bind variables assigned through the parameters or keyword_parameters arguments can be specified as a dictionary, sequence, or a set of keyword arguments. If dictionary or keyword arguments are supplied then the values will be name-bound. If a sequence is given, the values will be resolved by their position. This method returns a list of variable objects if it is a query, and None when it's not.
   • cx_Oracle.Cursor.executemany(statement, parameters) Especially useful for bulk inserts because it can limit the number of required Oracle execute operations to just a single one. For more information about how to use it please see the 'Many at once' section below.
3. Fetch (optional)—Only used for queries (because DDL and DCL statements don't return results). On a cursor that didn't execute a query, these methods will raise an InterfaceError exception.
   • cx_Oracle.Cursor.fetchall() Fetches all remaining rows of the result set as a list of tuples. If no more rows are available, it returns an empty list. Fetch actions can be fine-tuned by setting the arraysize attribute of the cursor which sets the number of rows to return from the database in each underlying request. The higher setting of arraysize, the fewer number of network round trips required. The default value for arraysize is 1.
   • cx_Oracle.Cursor.fetchmany([rows_no]) Fetches the next rows_no rows from the database. If the parameter isn't specified it fetches the arraysize number of rows. In situations where rows_no is greater than the number of fetched rows, it simply gets the remaining number of rows.
   • cx_Oracle.Cursor.fetchone() Fetches a single tuple from the database or none if no more rows are available.

Before going forward with cursor examples please welcome the pprint function from the pprint module. It outputs Python data structures in a clean, readable form.

cx_Oracle cursors are iterators. These powerful Python structures let you iterate over sequences in a natural way that fetches subsequent items on demand only. Costly database select operations naturally fit into this idea because the data only gets fetched when needed. Instead of creating or fetching the whole result set, you iterate until the desired value is found or another condition fulfilled.

Oracle Driver Download

Just after an execute list(cursor) does the same job as cursor.fetchall(). This is because the built-in list() function iterates until the end of the given iterator.

Datatypes

During the fetch stage, basic Oracle data types get mapped into their Python equivalents. cx_Oracle maintains a separate set of data types that helps in this transition. The Oracle - cx_Oracle - Python mappings are: