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

    con = mdb.connect('', 'mark', 'LiverpoolFc', '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])
  • 1
    Are these meant to be unit-tests? When that's the case, they shouldn't use the database because that would introduce dependencies on the state of an external system. You should use mock-objects to simulate the database instead. – Philipp Nov 6 '14 at 13:33
  • hi @Philipp no its just a theoretical programmer, just having a play with Python, learning by doing – twigg Nov 6 '14 at 14:04
  • 1
    Why not just make the connection a parameter of each function? – Steven Burnap May 5 '15 at 18:29
  • 1
    If is pretty efficient to open and close a connection. The typical best practice it open and close rather than hold a connection. – paparazzo Jul 4 '15 at 18:20

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.


The best thing it occurs to me it's creating a class, so you can save the connection in an attribute, and then use it by all the test methods. In example:

import sys
import MySQLdb as mdb

class MyDBTest():

    def __init__(self, host, user, passw, db):
            self.con = mdb.connect(host, user, passw, db)
            self.cur = self.con.cursor()
        except mdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])

    def show_version(self):
            self.cur.execute("SELECT VERSION()")
            print "Database version : %s " % self.cur.fetchone()

    def test1(self):
        # Do something with self.con or self.cur

    def test2(self):
        # Do something with self.con or self.cur

if __name__ == '__main__':  # If it's executed like a script (not imported)
    db = MyDBTest('', 'mark', 'LiverpoolFc', 'dex')

In the example I've put the error checking (try-except) inside the class. But you could also don't put it in there and leave the main (__name__ == __main__ part) to handle the errors if they occurr inside MyDBTest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.