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
  Mysql and mysql.connector error lostintime 2 607 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Mysql Workbench table not updated CatBall 2 1,040 Feb-13-2023, 05:37 PM
Last Post: CatBall
Photo How to select NULL and blank values from MySQL table into csv python300 9 2,328 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  Issue in writing sql data into csv for decimal value to scientific notation mg24 8 2,869 Dec-06-2022, 11:09 AM
Last Post: mg24
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,671 Feb-09-2022, 09:55 AM
Last Post: ibreeden
  List to table issue robdineen 2 1,435 Nov-07-2021, 09:31 PM
Last Post: robdineen
  Calculate the Euler Number with more decimal places Pedroski55 10 4,392 Oct-31-2021, 04:45 AM
Last Post: Pedroski55
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 2,112 Jul-02-2021, 02:19 PM
Last Post: xtc14
  Creating a table in SQLite3 djwilson0495 2 2,016 Aug-10-2020, 03:01 PM
Last Post: djwilson0495
  Load table from Oracle to MYSQL using python himupant94 0 1,608 May-12-2020, 04:50 PM
Last Post: himupant94

Forum Jump:

User Panel Messages

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