Python Database Cursor Exception Timing

Question

I'm creating a class that will manage a connection to a Firebird database. The Firebird Service will be installed to facilitate multiple connections to the database. Unfortunately, the environments where my software will deploy can be volatile, and I can't always guarantee that the Firebird Service will be running when I attempt to connect, or that it will continue to run after I've established a connection.

In the interest of centralizing error handling, I've made the decision that disparate parts of my code won't directly work with database cursors in any way. Instead, I'll expose query() and dml() methods from my Connection Manager. This works, to an extend, given the code below (some code not included for brevity).

class DBConnection(object):
  # self._conn is an instance of kinterbasdb.connect()
  def query(self, query, params = None):
    cursor = self._conn.cursor()

    if params:
      cursor.execute(query, params)
    else:
      cursor.execute(query)

    return [[x[0].title() for x in cursor.description]] + [r for r in cursor.fetchall()]

  def dml(self, query, params = None):
    cursor = self._conn.cursor()

    if params:
      cursor.execute(query, params)
    else:
      cursor.execute(query)

    self._conn.commit()

The trouble manifests when the Firebird Service stops or is unreachable for some reason. I would expect that self._conn.cursor() would throw an exception, which would make it simple to do something like this:

class DBConnection(object):
  # self._conn is an instance of kinterbasdb.connect()
  def cursor(self):
    try:
      return self._conn.cursor()
    except:
      # Error handling code here, possibly reconnect, display alert.

  def query(self, query, params = None):
    cursor = self.cursor()

  def dml(self, query, params = None):
    cursor = self.cursor()

Unfortunately, there is no exception thrown when I request a cursor. I don't become aware of the trouble until the call to cursor.execute(). That means that, if I want to properly centralize my error handling, I have to do something like this:

class DBConnection(object):
  # self._conn is an instance of kinterbasdb.connect()
  def cursor(self):
    try:
      cursor = self._conn.cursor()

      cursor.execute("Select NULL From <sometable>")

      return cursor
    except:
      # Error handling code here, possibly reconnect, display alert.

This requires an extra round-trip to my database, wastes a transaction (Firebird databases have a hard upper limit on total transactions for the life of the database), and generally just feels wrong. I'm wondering, has anyone encountered anything similar with other implementations of the Python Database API, and if so, how were they overcome?

score 1 · Accepted Answer · answered Jun 15 '11 at 05:55

I am testing the following modifications to my class which I believe will achieve the centralized handling that I want with minimal code duplication. They also simplify the query and dml methods slightly, and it eliminates the extra queries (heartbeat) that I wanted to avoid.

class DBConnection(object):
  # self._conn is an instance of kinterbasdb.connect()
  def query(self, query, params = None):
    cursor = self._conn.cursor()

    self.execute(cursor, query, params)

    return [[x[0].title() for x in cursor.description]] + 
            [r for r in cursor.fetchall()]

  def dml(self, query, params = None):
    cursor = self._conn.cursor()

    self.execute(cursor, query, params)

    self._conn.commit()

  def execute(self, cursor, query, params = None):
    try:
      if params:
        cursor.execute(query, params)
      else:
        cursor.execute(query)
    except Exception, e:
      # Handling

score 0 · Answer 2 · answered Jun 14 '11 at 08:38

The DB connection to Firebird suffers the connectivity detection issues as standard TCP/IP connections. That is, the connection isn't easily detected as "dead" until it's used. The ways to work around this in the TCP/IP world would be to use KeepAlives (still a ~15 minute detection cycle) and explicit heartbeats. Your sending of the query for detection is similar to issuing a heartbeat to see if it's still alive.

When you execute a SQL statement on a cursor, you'll get the exception at that time when disconnected. The appropriate place to detect connection failure is at the point of usage. Centralized error checking is a great goal, but implemented as above you still leave open the possibility that the connectivity to Firebird is lost between returning a valid cursor object and later execution (i.e. you could still fail on the execute() call in the dml() function).

Python Database Cursor Exception Timing

2 Answers2