Best practices for manipulating database result sets in Python?

Question

I am writing a simple Python web application that consists of several pages of business data formatted for the iPhone. I'm comfortable programming Python, but I'm not very familiar with Python "idiom," especially regarding classes and objects. Python's object oriented design differs somewhat from other languages I've worked with. So, even though my application is working, I'm curious whether there is a better way to accomplish my goals.

Specifics: How does one typically implement the request-transform-render database workflow in Python? Currently, I am using pyodbc to fetch data, copying the results into attributes on an object, performing some calculations and merges using a list of these objects, then rendering the output from the list of objects. (Sample code below, SQL queries redacted.) Is this sane? Is there a better way? Are there any specific "gotchas" I've stumbled into in my relative ignorance of Python? I'm particularly concerned about how I've implemented the list of rows using the empty "Record" class.

class Record(object):
    pass

def calculate_pnl(records, node_prices):
    for record in records:
        try:
            # fill RT and DA prices from the hash retrieved above
            if hasattr(record, 'sink') and record.sink:
                record.da = node_prices[record.sink][0] - node_prices[record.id][0]
                record.rt = node_prices[record.sink][1] - node_prices[record.id][1]
            else:
                record.da = node_prices[record.id][0]
                record.rt = node_prices[record.id][1]

            # calculate dependent values: RT-DA and PNL
            record.rtda = record.rt - record.da
            record.pnl = record.rtda * record.mw
        except:
            print sys.exc_info()

def map_rows(cursor, mappings, callback=None):
    records = []
    for row in cursor:
        record = Record()
        for field, attr in mappings.iteritems():
            setattr(record, attr, getattr(row, field, None))
        if not callback or callback(record):
            records.append(record)

    return records

def get_positions(cursor):
    # get the latest position time
    cursor.execute("SELECT latest data time")
    time = cursor.fetchone().time
    hour = eelib.util.get_hour_ending(time)

    # fetch the current positions
    cursor.execute("SELECT stuff FROM atable", (hour))

    # read the rows
    nodes = {}
    def record_callback(record):
        if abs(record.mw) > 0:
            if record.id: nodes[record.id] = None
            return True
        else:
            return False
    records = util.map_rows(cursor, {
        'id': 'id',
        'name': 'name',
        'mw': 'mw'
    }, record_callback)

    # query prices
    for node_id in nodes:
        # RT price
        row = cursor.execute("SELECT price WHERE ? ? ?", (node_id, time, time)).fetchone()
        rt5 = row.lmp if row else None

        # DA price
        row = cursor.execute("SELECT price WHERE ? ? ?", (node_id, hour, hour)).fetchone()
        da = row.da_lmp if row else None

        # update the hash value
        nodes[node_id] = (da, rt5)

    # calculate the position pricing
    calculate_pnl(records, nodes)

    # sort
    records.sort(key=lambda r: r.name)

    # return the records
    return records

Answer 1

Have you considered using an ORM? SQLAlchemy is pretty good, and Elixir makes it beautiful. It can really reduce the ammount of boilerplate code needed to deal with databases. Also, a lot of the gotchas mentioned have already shown up and the SQLAlchemy developers dealt with them.

Answer 2

Using a ORM for an iPhone app might be a bad idea because of performance issues, you want your code to be as fast as possible. So you can't avoid boilerplate code. If you are considering a ORM, besides SQLAlchemy I'd recommend Storm.

Answer 3

Depending on how much you want to do with the data you may not need to populate an intermediate object. The cursor's header data structure will let you get the column names - a bit of introspection will let you make a dictionary with col-name:value pairs for the row. You can pass the dictionary to the % operator. The docs for the odbc module will explain how to get at the column metadata.

This snippet of code to shows the application of the % operator in this manner.

>>> a={'col1': 'foo', 'col2': 'bar', 'col3': 'wibble'}
>>> 'Col1=%(col1)s, Col2=%(col2)s, Col3=%(col3)s' % a
'Col1=foo, Col2=bar, Col3=wibble'
>>>

Answer 4

The empty Record class and the free-floating function that (generally) applies to an individual Record is a hint that you haven't designed your class properly.

class Record( object ):
    """Assuming rtda and pnl must exist."""
    def __init__( self ):
        self.da= 0
        self.rt= 0
        self.rtda= 0 # or whatever
        self.pnl= None # 
        self.sink = None # Not clear what this is
    def setPnl( self, node_prices ):
        # fill RT and DA prices from the hash retrieved above
        # calculate dependent values: RT-DA and PNL

Now, your calculate_pnl( records, node_prices ) is simpler and uses the object properly.

def calculate_pnl( records, node_prices ):
    for record in records:
        record.setPnl( node_prices )

The point isn't to trivially refactor the code in small ways.

The point is this: A Class Encapsulates Responsibility.

Yes, an empty-looking class is usually a problem. It means the responsibilities are scattered somewhere else.

A similar analysis holds for the collection of records. This is more than a simple list, since the collection -- as a whole -- has operations it performs.

The "Request-Transform-Render" isn't quite right. You have a Model (the Record class). Instances of the Model get built (possibly because of a Request.) The Model objects are responsible for their own state transformations and updates. Perhaps they get displayed (or rendered) by some object that examines their state.

It's that "Transform" step that often violates good design by scattering responsibility all over the place. "Transform" is a hold-over from non-object design, where responsibility was a nebulous concept.