cx_oracle: should connection object be a singleton?

Question

A similar question was asked for Java, however as far as I know Python does not really supports multiple threads being executed concurrently because of the GIL.

In cx_Oracle, it seems to be considered bad practice to implement the db connection as a Singleton, with code like this:

# "con" would be a global variable, or a class member
def get_connection():
    if not con:
        con = cx_Oracle.Connection('...connecton details')
    return con

I saw that cx_Oracle also offers the possibility to do connection pooling, which seems a better option to me.

When using a db connection object in Python with cx_Oracle, what are the pros and cons among the possible approaches?

E.g. :

Single global variable (no singleton)
Single DB connection with singleton pattern
Connection pooling
Opening/Closing a new connection on any new DB request

How can one decide on which approach to chose?

Thanks

Pooling is a must if you have multiple users accessing the app concurrently. Turn on thread mode when you create the pool. — Christopher Jones, Nov 05 '20 at 11:37

score 1 · Accepted Answer · answered Nov 05 '20 at 15:39

If you have a single thread in your application (or multiple threads but only a single thread ever accesses the database), a singleton connection or single global variable is sufficient. As soon as you have multiple threads in your application that need to access the database concurrently, multiple connections are necessary. The Oracle client software does not permit concurrent access of the database through a single connection. It will instead serialize those accesses. The cx_Oracle driver does support concurrent access through multiple connections, however. For those situations a SessionPool is the best option. As Chris mentioned, ensure that threaded=True is used when creating the pool.

cx_oracle: should connection object be a singleton?

1 Answers1