How to remove "Decimal" from the query result

Question

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')]

Can you please show the Python code that executes that query? — , Nov 25 '16 at 10:11
where `Decimal` come from in this case? *the "Decimal" string* - it doesn't look like a string — RomanPerekhrest, Nov 25 '16 at 10:13
Example casting double to bigInt (Postgresql): http://dba.stackexchange.com/questions/3429/how-can-i-convert-from-double-precision-to-bigint-with-postgresql — Sohaib Farooqi, Nov 25 '16 at 10:15
Complete list of rounding functions for postgresql: https://www.postgresql.org/docs/current/static/functions-math.html — Sohaib Farooqi, Nov 25 '16 at 10:16

score 4 · Answer 1 · answered Nov 25 '16 at 10:19

Use list comprehensions,

from decimal import Decimal

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

results = [tuple(str(item) for item in t) for t in lists]

print(results)
[('2016-01-01', '11.0909090909090909', '3.7272727272727273', '58.5354545455'), 
 ('2016-02-01', '11.6666666666666667', '4.0000000000000000', '74.8766666667')]

Qichao Zhao · Accepted Answer · 2016-11-25T10:25:47.960

3

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

edited Nov 25 '16 at 10:25

answered Nov 25 '16 at 10:17

Qichao Zhao

191
7

Qichao Zhao It is giving me result like this: [, ] – nas Nov 25 '16 at 10:22
Ah, I believe that's because in python3+ the map function returns an iterator instead of a list, try editing this line as follows: var_fixed.append(list(map(str, list(row)))) – Qichao Zhao Nov 25 '16 at 10:25
1

Glad to help. Personally though I think sparkandshine's list comprehensions answer below is a little more elegant, I would recommend that method in future! :) – Qichao Zhao Nov 25 '16 at 10:29

ettanany · Answer 3 · 2016-11-25T10:30:57.200

2

Try the following:

res = [(item[0], float(item[1]), float(item[2]), item[3]) for item in my_list]

Output:

>>> res
[('2016-01-01', 11.090909090909092, 3.7272727272727275, 58.5354545454545), ('2016-02-01', 11.666666666666666, 4.0, 74.8766666666667)]

Use the following to get string values and avoid the changes due to float precision:

>>> res = [(item[0], str(item[1]), str(item[2]), str(item[3])) for item in my_list]
>>> res
[('2016-01-01', '11.0909090909090909', '3.7272727272727273', '58.5354545455'), ('2016-02-01', '11.6666666666666667', '4.0000000000000000', '74.8766666667')]

edited Nov 25 '16 at 10:30

answered Nov 25 '16 at 10:18

ettanany

19,038
9
47
63

Thank you very much – nas Nov 25 '16 at 10:26
1

You are using the case with `str`? using `float` would change a little bit the precision of your numbers. – ettanany Nov 25 '16 at 10:27

How to remove "Decimal" from the query result

3 Answers3