Extremely Simple Python ORM

data_access.py

'''
Extremely Simple Python ORM

Simple stuff for mapping database results onto objects. Extremely simplistic, does not handle table 
relationships or anything at all complicated. Not real fast either so not useful for anything that 
requires speed or beyond semi-casual usage. Still, it is nice for simple scripts to automate 
object creation from data stored in a database.

Tested with MySQL and Oracle (selects only).
'''

class Entity(type):
  '''
  Entity metaclass - maps keys from a dict in args into data members on a class, 
  then assigns the dict values to the appropriate data member.
  '''
  def __call__(cls, *args, **kwargs):
    ob = object.__new__(cls, args)
    if isinstance(args[0], dict):
      ob.__dict__ = dict(args[0])
    ob.__init__(args, kwargs)
    return ob

def _get(conn, sql, cls, params=( )):
  '''
  Generic SQL fetch - maps result set to an entity object 
  with Entiity metaclass
  '''
  cursor = conn.cursor()
  cursor.execute(sql, params)
  results = cursor.fetchall()
  column_names = [i[0].lower() for i in cursor.description]
  
  data = []
  for result in results:
    data.append(cls(dict(zip(column_names, result))))
      
  cursor.close()
  return data

def _get_scalar(conn, sql, params=( )):
  '''
  Generic fetch unique simple value (int for example)
  '''
  cursor = conn.cursor()
  cursor.execute(sql, params)
  result = cursor.fetchone()
  cursor.close()
  return result[0] if result and len(result) else None
  
def _get_unique(conn, sql, cls, params=()):
  '''
  Generic fetch unique class
  '''
  vals = _get(conn, sql, cls, params)
  return vals[0] if len(vals) else None
    
def _insert_mysql(conn, sql, params):
  '''
  MySQL-specific insertion; returns the id of the thing inserted
  '''
  cursor = conn.cursor()
  cursor.execute(sql, params)
  id = cursor.lastrowid
  conn.commit()
  cursor.close()
  
  return id

'''
Example usages
Uses MySQL Connector/Python syntax. Other DBAPI-compliant drivers use their own syntax 
for prepared statements, so would need to adjust accordingly.
Assumes a package-level DBAPI connection called 'conn'
'''
class Book(metaclass=Entity)
  '''
  Example using Entity metaclass
  '''
  def __init__(self, *args, **kwargs):
    '''
    do any special initialization stuff here
    '''
    pass

def get_books():  
  sql = 'select * from books'
  return _get(conn, sql, Book)
  
def get_books_by_author(author):
  sql = 'select * from books where author=%s'
  return _get(conn, sql, Book, (author,))
  
def get_book_count():
  sql = 'select count(1) from books'
  return _get_scalar(conn, sql)
  
def get_book_count_by_author(author):
  sql = 'select count(1) from books where author=%s'
  return _get_scalar(conn, sql, Book, (author,))
  
def add_book(title, author):
  sql = 'insert into books values (%s, %s)'
  return _insert_mysql(conn, sql, (title, author))