the proper method for making a DB connection available across many python modules

Question

I want to make a single database object available across many python modules.

For a related example, I create globl.py:

DOCS_ROOT="c:\docs" ## as an example
SOLR_BASE="http://localhost:8636/solr/"

Any other module which needs it can do a

from globl import DOCS_ROOT

Now this example aside, I want to do the same thing with database connection objects, share them across many modules.

import MySQLdb
conn = MySQLdb.connect (host="localhost"...)
cursor = conn.cursor()

I tried this on the interpreter:

from globl import cursor

and it seems to work. But I suspect that this will cause the same module to be executed each time one imports from it. So is this the proper way?

Answer 1

Even if the import doesn't run the code multiple times, this is definitely not the correct way.

You should instead hide the process of obtaining a connection or cursor behind a function. You can then implement this function using either a Singleton or Object Pool design pattern.

So it would be something like this:

db.py:

_connection = None

def get_connection():
    global _connection
    if not _connection:
        _connection = MySQLdb.connect(host="localhost"...)
    return _connection

# List of stuff accessible to importers of this module. Just in case
__all__ = [ 'getConnection' ]

## Edit: actually you can still refer to db._connection
##         if you know that's the name of the variable.
## It's just left out from enumeration if you inspect the module

someothermodule.py:

import db
conn = db.get_connection() # This will always return the same object

By the way, depending on what you are doing, it may not be so much of a good idea to share your connection object rather than create a new one every time you need one.

But, that's why you'd want to write a get_connection() method, to abstract from these issues in the rest of your code.

Answer 2

You suspect wrongly. The code will only be executed once - subsequent imports just refer to the module via sys.modules, and don't re-run it.

(Note that this is the case as long as you always use the same path to import the module - if you do from globl import cursor in one place, and from my.fullyqualified.project.global import cursor in another, you probably will find the code is re-executed.)

Edit to add as S.Lott says in the comment, this is a perfectly good way to handle a global object.

Answer 3

I think Daniel already answered the question, while I'd like to add few comments about the cursor object you want to share.

It is generally not a good idea to share the cursor object that way. Certainly it depends on what your program is, but as a general solution I'd recommend you to hide this cursor object behind a "factory" producing cursors. Basically you can create a method cursor() or get_cursor() instead of making the cursor a global variable. The major benefit (but not the only one) - you can hide a more complex logic behind this "factory" - pooling, automatic re-connection in case the connection is dropped, etc. Even if you don't need it right away - it will be very easy to add it later if you start using this approach now, and while for now you can keep this function implementation as simple as return _cursor.

And yes, still, the module itself will be imported once only.