Passing decimal value to MySQL in Python

Question

So currently my table is as follow:

    name      ->      type 
--------------------------------
   gameid     ->   mediumint(7)
  timestamp   ->    timestamp
  duration    ->   decimal (5,5)
   winner     ->    varchar(20)
winner_score  ->     int(1)
    loser     ->    varvhar(20)
 loser_score  ->     int(1)

Now, I'm able to connect to the database, but I'm getting a warning when inserting a new row to the database:

Warning: Out of range value for column 'duration' at row 1

For the duration I am calculating the start and end time and subtracting it. I played around with it by rounding it (thought it was the decimal issues) and passing it as a string, but it looks like this:

gametime = round((time.time() - start_time),5)

Tried playing around with what I have written for it, which looks like:

cur.execute("INSERT INTO scores (duration, winner, winner_score, loser, loser_score) \
         VALUES(%s, %s, %s, %s, %s)", (gametime, red_p, red_s, blue_p, blue_s))

For some reason, I keep getting the error. Not sure if the %s is what is affecting it. On the MySQL backend, everything is good, except that the duration (regardless of the length) will always be 0.99999. Any help?

Dylan Su · Accepted Answer · 2016-04-13 05:04:20Z

1

decimal(5,5) means that it can store only with decimals. Any number with integer will always be truncated to 0.99999.

Demo:

mysql> select cast(2 as decimal(5,5));
+-------------------------+
| cast(2 as decimal(5,5)) |
+-------------------------+
|                 0.99999 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast(2 as decimal(5,5))' at row 1 |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

To make it work, you may change the column type to other precision, like decimal(10,5).

answered Apr 13, 2016 at 5:04

Dylan Su

6,0751 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jetmas Over a year ago

I agree with what you said, but even if the gametime value is a decimal like 8.618591, it will give me problems. Even if I use the round() function to bring it to 8.61859, or 8.6186, it will still give that warning.

Dylan Su Over a year ago

decimal(5,5) can't hold 7.18374. It is out of range.

jetmas Over a year ago

So my question would be then, the 5 after the comma, does not denote how many spaces after the decimal period?

Dylan Su Over a year ago

I didn't get your meaning. decimal(5,5) means 5 digits, with 5 digits after decimal point.

jetmas Over a year ago

I gotcha! I thought the first 5 was the number of digits before decimal point and the second 5 was the precision, so after the decimal point.

Collectives™ on Stack Overflow

Passing decimal value to MySQL in Python

1 Answer 1

Demo:

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Demo:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related