CODEWITHSUNDEEP
pythonmysqlmysql-python
jeffery_the_wind
jeffery_the_wind

Reputation: 18178

get raw decimal value from mysqldb query

I am executing a mysql query in python using the MySQLdb package. The code looks something like this:

c=db.cursor()
c.execute("""select * from table""")
output = []
for row in c:
    output.append(row[4])

where row[4] contains a decimal value that I want to store in the output list.

The problem is every value that I am getting looks like this: Decimal('XX.XX') where all I want in the output list is XX.XX. At the end of the script, my output list looks like this:

[Decimal('10.02'), Decimal('20.24'), ...]

But I need it to just contain the numbers, like this:

[10.02, 20.24, ...]

How do I do that?

Thanks!

Upvotes: 22

Views: 27097

Answers (4)

saprophagist
saprophagist

Reputation: 31

To avoid losing precision, you can use Python's decimal module.

from decimal import Decimal
c=db.cursor()
c.execute("""select * from table""")
output = []
for row in c:
    row_data = []
    for data in row:
        if type(data) is Decimal:
            row_data.append(float(data))
        else:
            row_data.append(str(dat))
        output.append(row_data)

Upvotes: 3

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

You can either convert a Decimal object to a string:

cursor = db.cursor()
cursor.execute("""select * from table""")
output = []
for row in cursor:
    output.append(str(row[4]))

Or to a float:

cursor = db.cursor()
cursor.execute("""select * from table""")
output = []
for row in cursor:
    output.append(float(row[4]))

Converting it to a float will cause it to lose its full precision, so a value like 20.24 will become 20.239999999999998.

Also, casting it to a float will raise an Exception if the value is None. To avoid that, you can use a helper function like this:

def convert_mysql_decimal_to_float(decimal_object):
    if (decimal_object == None):
        return None
    else:
        return float(decimal_object)

cell_value = convert_mysql_decimal_to_float(row[4])

Upvotes: 21

Kannan Ramamoorthy
Kannan Ramamoorthy

Reputation: 4180

If you are trying to write a generic code that operates on the column output, the above solution won't workout. In that case we can write our SELECT query in a way that the column in returned as String and we just get the value of what we want. The query can be framed in below way,

SELECT 
 CAST(COL1 AS CHAR) AS COL1,
 CAST(COL2 AS CHAR) AS COL2,
 .
 .
 .
FROM TABLE;

Upvotes: 8

Ashwini Chaudhary
Ashwini Chaudhary

Reputation: 250931

Use float():

output.append(float(row[4]))

But float() can result in something like:

In [184]: float(Decimal('10.02'))
Out[184]: 10.02

In [185]: float(Decimal('20.24'))
Out[185]: 20.239999999999998

Upvotes: 8

Related Questions