cx_Oracle and the data source paradigm

Question

There is a Java paradigm for database access implemented in the Java DataSource. This object create a useful abstraction around the creation of database connections. The DataSource object keeps database configuration, but will only create database connections on request. This is allows you to keep all database configuration and initialization code in one place, and makes it easy to change database implementation, or use a mock database for testing.

I currently working on a Python project which uses cx_Oracle. In cx_Oracle, one gets a connection directly from the module:

import cx_Oracle as dbapi
connection = dbapi.connect(connection_string)
# At this point I am assuming that a real connection has been made to the database.
# Is this true?

I am trying to find a parallel to the DataSource in cx_Oracle. I can easily create this by creating a new class and wrapping cx_Oracle, but I was wondering if this is the right way to do it in Python.

Answer 1

I don't think there is a "right" way to do this in Python, except maybe to go one step further and use another layer between yourself and the database.

Depending on the reason for wanting to use the DataSource concept (which I've only ever come across in Java), SQLAlchemy (or something similar) might solve the problems for you, without you having to write something from scratch.

If that doesn't fit the bill, writing your own wrapper sounds like a reasonable solution.

Answer 2

You'll find relevant information of how to access databases in Python by looking at PEP-249: Python Database API Specification v2.0. cx_Oracle conforms to this specification, as do many database drivers for Python.

In this specification a Connection object represents a database connection, but there is no built-in pooling. Tools such as SQLAlchemy do provide pooling facilities, and although SQLAlchemy is often billed as an ORM, it does not have to be used as such and offers nice abstractions for use on top of SQL engines.

If you do want to do object-relational-mapping, then SQLAlchemy does the business, and you can consider either its own declarative syntax or another layer such as Elixir which sits on top of SQLAlchemy and provides increased ease of use for more common use cases.

Answer 3

Yes, Python has a similar abstraction.

This is from our local build regression test, where we assure that we can talk to all of our databases whenever we build a new python.

if database == SYBASE:
    import Sybase
    conn = Sybase.connect('sybasetestdb','mh','secret')
elif database == POSTRESQL:
    import pgdb
    conn = pgdb.connect('pgtestdb:mh:secret')
elif database == ORACLE:
    import cx_Oracle
    conn = cx_Oracle.connect("mh/secret@oracletestdb")

curs=conn.cursor()
curs.execute('select a,b from testtable')
for row in curs.fetchall():
    print row

(note, this is the simple version, in our multidb-aware code we have a dbconnection class that has this logic inside.)

Answer 4

I just sucked it up and wrote my own. It allowed me to add things like abstracting the database (Oracle/MySQL/Access/etc), adding logging, error handling with transaction rollbacks, etc.