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)
4
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:
with DatabaseConnection() as db:
cursor = db.connection.cursor()
...
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.
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):
try:
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])
sys.exit(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
pass
def test2(self):
# Do something with self.con or self.cur
pass
if __name__ == '__main__': # If it's executed like a script (not imported)
db = MyDBTest('10.243.17.13', 'mark', 'LiverpoolFc', 'dex')
db.show_version()
db.test1()
db.test2()
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
.