Python Forum

Full Version: Creating table in MySQL db with decimal number issue
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
New coder here. I am working on code to create a simple MySQL table and I want a number value to have decimals. I used the DECIMAL data type and specified the digits and decimal places:

CREATE TABLE products3 (product VARCHAR(255), price DECIMAL(12,2), PRIMARY KEY(product))

I am able to commit data to it but when I select the data the number value looks like this:

('Lumber-2x4x96 inch', Decimal('5.50'))

I expected to see just the actual number value and not the "Decimal" with parenthesis. Am I doing something wrong or is there a way to strip off the non-number portion of the decimal value?
Decimal is a python data type that doesn't lose precision for decimal fractions. You can cast it to a float or an integer as you prefer.

>>> import decimal
>>> float(decimal.Decimal("5.87"))
5.87
>>> int(decimal.Decimal("5.87"))
5
>>> round(decimal.Decimal("5.87"))
6
But you can also just leave it as a Decimal, and most anything you want to do with it will work as you might expect a "regular" number to work.

>>> num = decimal.Decimal("5.87")
>>> num > 4
True
>>> print(num)
5.87
Thanks. I am unsure of what to do with how it is stored in the MySQL database. When I print the value of the number it shows as: Decimal('5.50')

If I want to do a calculation with that, or display in a GUI then it has the "Decimal" and the parenthesis and I don't want that. I just want the number. Should I do something different when writing the number to the db?
It doesn't have quotes around it like the "lumber" entry does, so it's not a string. Can you show the type of the object? If you assign it to "value" for instance, what do you get for:

print(value)
print(type(value))
Maybe I just don't understand how the db stuff works with python and mysql. If I do a fetchall then it shows the number with the Decimal text before it. If I do a fetchone then it shows up as just the number.
(Nov-20-2020, 10:20 PM)bowlofred Wrote: [ -> ]It doesn't have quotes around it like the "lumber" entry does, so it's not a string. Can you show the type of the object? If you assign it to "value" for instance, what do you get for:

print(value)
print(type(value))

Maybe I just don't understand how the db stuff works with python and mysql. If I do a fetchall then it shows the number with the Decimal text before it. If I do a fetchone then it shows up as just the number.

When I do fetchone the class/type is decimal.Decimal
When I do fetchall the class/type is tuple
If you have a variable in a container (like a list or a tuple) and print the container, then the elements inside are shown with their repr() display. This often includes class information.

But if you print the object explicitly, then you get the str() representation. For Decimals, that's just the value.

import decimal
d = decimal.Decimal("5.87")
print([d])
print(repr(d))

print(d)
print(str(d))
Output:
[Decimal('5.87')] Decimal('5.87') 5.87 5.87
Perfect. Thank you. That makes sense.