user7172343 user7172343 - 13 days ago 5
Python Question

How to remove "Decimal" from the query result

I have result from PostgreSQL query using the following code:

cur = con.cursor()
cur.execute("""SELECT to_char(tt.service_date,'yyyy-mm-01') AS txn_month,
SUM (tt.customer) AS customer,SUM (tt.tour) AS tour,
SUM (tt.distancetraveled) AS distancetraveled
FROM
tbl_travel as tt
GROUP BY
txn_month""")
rows = cur.fetchall()


My query result is something like this:

[('2016-01-01', Decimal('11.0909090909090909'), Decimal('3.7272727272727273'), 58.5354545454545),
('2016-02-01', Decimal('11.6666666666666667'), Decimal('4.0000000000000000'), 74.8766666666667)]


I need to remove the "Decimal" string in front of the values and get result like:

[('2016-01-01', '11.0909090909090909', '3.7272727272727273','58.5354545454545'),
('2016-02-01', '11.6666666666666667', '4.0000000000000000','74.8766666666667')]

Answer

The numbers are being stored as the Python "Decimal" format (see: https://docs.python.org/3.4/library/decimal.html)

You can convert them into normal strings by mapping the data types, some sample code that would do this:

from decimal import Decimal

var = [('2016-01-01', Decimal('11.0909090909090909'), Decimal('3.7272727272727273'),
  58.5354545454545),
 ('2016-02-01', Decimal('11.6666666666666667'), Decimal('4.0000000000000000'),
  74.8766666666667)]

var_fixed = []
for row in var:
    var_fixed.append(list(map(str, list(row))))

This gives var_fixed as:

[['2016-01-01', '11.0909090909090909', '3.7272727272727273', '58.5354545455'],
 ['2016-02-01', '11.6666666666666667', '4.0000000000000000', '74.8766666667']]