In Python, how to make sure database connection will always close before leaving a code block?
- by Cawas
I want to prevent database connection being open as much as possible, because this code will run on an intensive used server and people here already told me database connections should always be closed as soon as possible.
def do_something_that_needs_database ():
dbConnection = MySQLdb.connect(host=args['database_host'], user=args['database_user'], passwd=args['database_pass'], db=args['database_tabl'], cursorclass=MySQLdb.cursors.DictCursor)
dbCursor = dbConnection.cursor()
dbCursor.execute('SELECT COUNT(*) total FROM table')
row = dbCursor.fetchone()
if row['total'] == 0:
print 'error: table have no records'
dbCursor.execute('UPDATE table SET field="%s"', whatever_value)
return None
print 'table is ok'
dbCursor.execute('UPDATE table SET field="%s"', another_value)
# a lot more of workflow done here
dbConnection.close()
# even more stuff would come below
I believe that leaves a database connection open when there is no row on the table, tho I'm still really not sure how it works.
Anyway, maybe that is bad design in the sense that I could open and close a DB connection after each small block of execute. And sure, I could just add a close right before the return in that case...
But how could I always properly close the DB without having to worry if I have that return, or a raise, or continue, or whatever in the middle? I'm thinking in something like a code block, similar to using try, like in the following suggestion, which obviously doesn't work:
def do_something_that_needs_database ():
dbConnection = MySQLdb.connect(host=args['database_host'], user=args['database_user'], passwd=args['database_pass'], db=args['database_tabl'], cursorclass=MySQLdb.cursors.DictCursor)
try:
dbCursor = dbConnection.cursor()
dbCursor.execute('SELECT COUNT(*) total FROM table')
row = dbCursor.fetchone()
if row['total'] == 0:
print 'error: table have no records'
dbCursor.execute('UPDATE table SET field="%s"', whatever_value)
return None
print 'table is ok'
dbCursor.execute('UPDATE table SET field="%s"', another_value)
# again, that same lot of line codes done here
except ExitingCodeBlock:
closeDb(dbConnection)
# still, that "even more stuff" from before would come below
I don't think there is anything similar to ExitingCodeBlock for an exception, tho I know there is the try else, but I hope Python already have a similar feature...
Or maybe someone can suggest me a paradigm move and tell me this is awful and highly advise me to never do that. Maybe this is just something to not worry about and let MySQLdb handle it, or is it?
