Python Database – Using One Database Connection Across Multiple Functions

python

whats the best way to go about this in python? I have a number of like so

def test1()
    .... code .....
def test2()
    .... code .....
def test3()
    .... code .....

how could I create one database connection and use it across all the functions? I'm connecting to my database in a test file like so:

import MySQLdb as mdb
import sys

try:
    con = mdb.connect('10.243.17.13', 'mark', '********', 'dex');

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    ver = cur.fetchone()
    
    print "Database version : %s " % ver

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

Best Answer

The problem is well known and the keyword you may be searching for is pooling.

The idea is that opening a connection is a complicated task: is usually requires reaching the server, doing authentication, checking the rights, auditing, etc.

On the other hand, preserving the same connection through the application by either storing it in a static variable or passing it to every method which may require access to the database is usually ugly and difficult to implement. Not counting the fact that in this case, the connection may remain opened for a long time while being unused, eventually reaching the maximum number of connections allowed by the server.

Connection pooling consists of preserving the pool of connections under the hood in order to make it possible for the programmer to open the connection and close it as soon as it is not needed any longer. The actual connections are managed transparently, meaning that you don't have the overheat of a connection opened again and again and you are less at risk of using all available connections.

Further reading:

Now, not reusing the connection doesn't mean you should duplicate database logic in every of your tests. You may put connection and disposal logic in a class with special __enter__ and __exit__ methods, and then use it as:

with DatabaseConnection() as db:
    cursor = db.connection.cursor()
    ...

Note that since you mention tests in your code sample:

  1. You may be interested in using a testing framework. After all, you said that you "just play with Python, learning by doing", so why not to learn a Python's testing framework as well?

  2. As others noted, unit tests shouldn't access a database. But access to databases is perfectly fine in integration or system tests.

Related Topic