Creating table in MySQL db with decimal number issue

Creating table in MySQL db with decimal number issue - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Creating table in MySQL db with decimal number issue (/thread-31068.html)

Creating table in MySQL db with decimal number issue - dangermaus33 - Nov-20-2020

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?

RE: Creating table in MySQL db with decimal number issue - bowlofred - Nov-20-2020

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

RE: Creating table in MySQL db with decimal number issue - dangermaus33 - Nov-20-2020

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?

RE: Creating table in MySQL db with decimal number issue - bowlofred - Nov-20-2020

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

RE: Creating table in MySQL db with decimal number issue - dangermaus33 - Nov-20-2020

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.

RE: Creating table in MySQL db with decimal number issue - dangermaus33 - Nov-20-2020

(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

RE: Creating table in MySQL db with decimal number issue - bowlofred - Nov-20-2020

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

RE: Creating table in MySQL db with decimal number issue - dangermaus33 - Nov-20-2020

Perfect. Thank you. That makes sense.