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:
Opening and closing database connection for each query at Stack Overflow.
Pooling and performance: what benchmarks and profiling may reveal, an article from my blog.
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:Note that since you mention tests in your code sample:
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?
As others noted, unit tests shouldn't access a database. But access to databases is perfectly fine in integration or system tests.