DTuple for O/R mapping

Why use ActiveRecord or SQLObject when there is DTuple?

Both of these systems attempt to hide sql through an object oriented abstraction. (I thought that this was something that only the J2EE guys tried to do.) Long story short it won't perform nearly as well as hand coded SQL and it may be trying to solve one problem by introducing another. Why learn another DSL when SQL works so well?

It's not the mapping that I have issue with it's the fact that sql is code generated behind the scenes as if it were not meant for human consumption. Don't get me wrong I am a huge fan of O/R mapping and I am a huge fan of abstraction, however I think that attempting to talk to a database while speaking objects actually makes it harder to express what you actually want the database to do. The other disadvantage that this approach has is that it may make your code hard to debug.

What am I suggesting? Well for Ruby I am not quite sure but for python there is DTuple -- written by Greg Stein. I am not sure why DTuple gets no ink but it should. First off it has been around 4-ever since 1996 I think, and secondly it roks! It examines the result of your query and returns a collection of typed objects. This is done dynamically and preforms quite nicely. You basically get O/R Mapping for free and don't have to learn any object query syntax.

Example Data Access object:
(caution this code is almost 2 years old and may need a little tweaking with the latest version of MySQLdb)

Pay special attention to the get_records where DTuple is used...

import dtuple
import MySQLdb
from common.util import config

def _connect():
  """ Login to the database return the connection
  """
  conf = config.get_config()
  return MySQLdb.connect(
    host=conf.get('db', 'host'),
    user=conf.get('db', 'user'),
    passwd=conf.get('db', 'passwd'),
    db=conf.get('db','database'))

def _get_conn(relogin = 0):
  """ Returns a persistent connection to the database
  attempts to reconnect if the connection is lost.
  """
  global CONN

  if relogin:
    CONN = _connect()
    return CONN
  else:
    try:
      conn = CONN
      return conn
    except NameError:
      CONN = _connect()
  return CONN

def _exec_stmt(stmt):
  """ Runs SQL statement and returns (count, curs)
  """
  curs = None
  try:
    curs = _get_conn().cursor()
    count = curs.execute(stmt)
  except MySQLdb.OperationalError, e:
    curs = _get_conn(relogin=1).cursor()
  count = curs.execute(stmt)
  return count, curs

def do_test(stmt):
  count, curs = _exec_stmt(stmt)
  try: return curs.fetchall()
  finally: curs.close()

def get_first_record(stmt=None):
  """ Runs select statement and returns 1st record
  """
  rec = get_records(stmt, 0, 1)
  if rec[0] == 1: return rec[1][0]
  return None

def get_records(stmt=None, start=0, max=500):
  """ Runs select statement and returns a list of the form:
  (record count, result set)
  """
  if stmt == None:
    return None
  limit_stmt = "%s limit %d, %d" % (stmt, start, max)
  count, curs = _exec_stmt(limit_stmt)
  try:
    return count, map(
      lambda x:
        dtuple.DatabaseTuple(
          dtuple.TupleDescriptor(curs.description),
        x),
      curs.fetchall())
  finally: curs.close()

def do_insert(stmt=None):
  """ Runs insert statement and returns the id of the newly
  inserted record or -1 on error.
  """
  if stmt == None:
    return -1
  count, curs = _exec_stmt(stmt)
  if curs == None:
    return -1
  try: return curs.insert_id()
  finally: curs.close()

def do_update(stmt=None):
  """ Runs update statement and returns the number of records
  updated or -1 on failure.
  """
  if stmt == None:
    return -1
  count, curs = _exec_stmt(stmt)
  curs.close()
  return count

def do_delete(stmt=None):
  """ Runs delete statement and returns the number of records
  deleted or -1 on failure.
  """
  return do_update(stmt) # same impl as update

def escape_string(str=""):
  return MySQLdb.escape_string(str)