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

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

For a related example, I create

DOCS_ROOT="c:docs" ## as an example

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?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:

_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

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.

Method 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 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.

Method 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.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x