StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest practices for manipulating database result sets in Python?
text
Body
copied!<p>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.</p> <p>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.</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload