Guidelines for managing database result sets in Python?

Question

I am writing a simple Python web application consisting of several pages of business data formatted for iPhone. I'm comfortable programming Python, but I'm not very good at Python's “idiom,” especially with regard to classes and objects. Python's object-oriented design is slightly different from the other languages ​​I worked with. So, while my application is working, I am wondering if there is a better way to achieve my goals.

Features: How is the request-transform-render database workflow typically implemented in Python? I am currently using pyodbc to retrieve data, copy the results into the attributes of an object, perform some calculations and merge using a list of these objects, and then render output from the list of objects. (Sample code below, SQL queries edited.) Is this normal? Is there a better way? Are there any specific “touches” that I stumbled upon my relative ignorance of Python? I'm particularly worried about how I implemented a list of strings using an 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

An empty record class and a free-floating function, which (as a rule) are applied to a separate record, are a hint that you did not correctly develop your class.

 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 ) simpler and correctly uses the object.

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

It's not about trivially reorganizing your code in small ways.

The fact is that: A class encapsulates responsibility .

Yes, an empty class is usually a problem. This means that responsibilities are scattered elsewhere.

A similar analysis is performed to collect records. This is a simpler list, because the collection - in general - has operations that it performs.

"Request-Transform-Render" is not quite right. You have a model (recording class). Model instances are created (possibly due to a request.) Model objects are responsible for their own transformations and state updates. Perhaps they are displayed (or visualized) by some object that examines their condition.

This is the Transform step, which often breaks a good design, scattering responsibility everywhere. “Transformation" is a deduction from non-object design, where responsibility was a vague concept.

Answer 2

Have you considered using ORM? SQLAlchemy is pretty good, and Elixir makes it beautiful. It can really reduce the amount of template code needed to work with databases. In addition, many of the problems mentioned above have already appeared, and SQLAlchemy developers have dealt with them.

Answer 3

Depending on how much you want to do with the data, you may not need to fill out an intermediate object. The cursor header data structure will allow you to get the column names - a little introspection will allow you to create a dictionary with col-name: value pairs for a row. You can pass the dictionary to the% operator. The docs for the odbc module will explain how to get the column metadata.

This piece of code shows the% operator application in this way.

 >>> 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

Using ORM for an iPhone application might be a bad idea due to performance issues, you want your code to be as fast as possible. Therefore, you cannot escape the template code. If you are considering ORM, besides SQLAlchemy, I would recommend Storm.