Replacing value in all cursor rows

Question

Hi there,

Using SQLite and Python 3.1, I want to display currency data in a HTML table via. a template which accepts a cursor as a parameter. Hence all currency values must have 2 decimal places, but SQLite stores them as float type (even though the structure states decimal :-( ) so some must be converted before display (eg. I want 12.1 displayed as 12.10).

The code goes something like this (simplified for illustration)...

import sqlite3
con = sqlite3.connect("mydb")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select order_no, amount from orders where cust_id=123")

for row in cur:
   row['amount'] = format(row['amount'],'%.2f')

The last command throws the error "# builtins.TypeError: 'sqlite3.Row' object does not support item assignment"

How can I solve the problem whereby the row object values cannot be changed? Could I convert the cursor to a list of dictionaries (one for each row, eg. [{'order_no':1, 'amount':12.1}, {'order_no':2, 'amount':6.32}, ...]), then format the 'amount' value for each item? If so, how can I do this?

Are there any better solutions for achieving my goal? Any help would be appreciated.

TIA, Alan

Answer 1

Yep...:

dictrows = [dict(row) for rows in cur]
for r in dictrows:
  r['amount'] = format(r['amount'],'%.2f')

there are other ways, but this one seems the simplest and most direct one.

Answer 2

An alternative is to store your value as an integer number of cents (which is always an exact amount, no rounding), and then convert to dollars when displaying for reports using divmod:

>>> value_in_cents = 133
>>> print "$%d.%d" % divmod(value_in_cents,100)
$1.33