Replacing a value in all lines of the cursor

Question

Using SQLite and Python 3.1, I want to display currency data in an HTML table through. A template that takes a cursor as a parameter. Therefore, all currency values ​​must have 2 decimal places, but SQLite saves them as a floating point type (even if the structure contains decimal numbers :-(), so some of them must be converted before displaying (for example, I want 12.1 to be displayed like 12.10).

The code looks something like this (simplified to illustrate) ...

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 element assignment"

How can I solve the problem where the values ​​of the string object cannot be changed? Can I convert the cursor to a list of dictionaries (one for each line, for example [{'order_no': 1, 'amount': 12.1}, {'order_no': 2, 'amount': 6.32}, ...]) , then format the "sum" value for each item? If so, how can I do this?

Are there any better solutions to achieve my goal? Any help would be appreciated.

TIA, Alan

Answer 1

Yes:

 cur.execute("select order_no, amount from orders where cust_id=123") dictrows = [dict(row) for row 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.

Answer 2

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

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