Python Forum
Creating table in MySQL db with decimal number issue
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Creating table in MySQL db with decimal number issue
#1
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?
Reply
#2
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
Reply
#3
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?
Reply
#4
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))
Reply
#5
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.
Reply
#6
(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
Reply
#7
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
Reply
#8
Perfect. Thank you. That makes sense.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Fetch Oracle DB rows & print it in HTML file with table's col headers in table format tssr_2001 1 494 Sep-04-2020, 01:39 PM
Last Post: ibreeden
  Creating a table in SQLite3 djwilson0495 2 349 Aug-10-2020, 03:01 PM
Last Post: djwilson0495
  Load table from Oracle to MYSQL using python himupant94 0 429 May-12-2020, 04:50 PM
Last Post: himupant94
  Numpy savetxt, how save number with decimal separator SpongeB0B 1 441 May-10-2020, 01:05 PM
Last Post: ThomasL
  Code import .CSV file to MySQL table rtakle 4 633 Apr-30-2020, 03:16 PM
Last Post: anbu23
  Mysql CREATE TABLE IF NOT EXISTS dynamic table name nisusavi 0 401 Apr-29-2020, 06:45 PM
Last Post: nisusavi
  Issue with creating an array of pixel data for PNG files in Google Colab The_Sarco 1 447 Apr-29-2020, 12:03 AM
Last Post: bowlofred
  Python animate live plotting fetching data from Mysql Table dhirajm 6 644 Apr-24-2020, 05:07 PM
Last Post: dhirajm
  How show decimal only for none whole number ? SpongeB0B 6 531 Mar-27-2020, 09:15 AM
Last Post: DeaD_EyE
  Approach to creating Audit table pynewbie 4 930 Feb-24-2020, 06:12 PM
Last Post: pynewbie

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020