CODEWITHSUNDEEP
pythonmysqldecimalroundingmysql-python
Oluderi
Oluderi

Reputation: 323

MySQL database updates to the round decimal

I am using Python 2.7 and MySQLdb. I am trying to update and set as the number the decimal I have set as data but what I get is an the closest integer. This is the code:

Value = 5
data = 5
data = data + 0.5
print(data)                       
x.execute(""" UPDATE Testing SET number = %s WHERE id = %s """, (data, Value))
conn.commit()

For example, if data = 5.5 and I try to update the database, I see in the table that the number is 6 when I want it to be 5.5. I have seen some others ask the same question but not in Python. Number is an INT. Could you please help me? Thanks in advance.

Upvotes: 1

Views: 2672

Answers (1)

unutbu
unutbu

Reputation: 879421

The number column in the Testing database table apparently has an integer data type. You can check the data type by querying EXPLAIN Testing. If it has an integer data type, the number value is coerced to an integer before it stored in the table.

If you wish to store a decimal then you'll need to alter the table first:

ALTER TABLE `Testing` CHANGE `number` `number` DECIMAL(M,D)

where (per the docs):

  • M is the maximum number of digits (the precision). It has a range of 1 to 65.

  • D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

For example, if we create a Testing table with number having an INT(11) data type:

import MySQLdb
import config

def show_table(cursor):
    select = 'SELECT * FROM Testing'
    cursor.execute(select)
    for row in cursor:
        print(row)

def create_table(cursor):
    sql = 'DROP TABLE Testing'
    cursor.execute(sql)
    sql = '''CREATE TABLE `Testing` (
             `id` INT(11) NOT NULL AUTO_INCREMENT,
             `number` INT(11),
             PRIMARY KEY (id))'''
    cursor.execute(sql)

with MySQLdb.connect(host=config.HOST, user=config.USER, 
                     passwd=config.PASS, db='test') as cursor:

    create_table(cursor)

Suppose the table has a record with number = 5:

    insert = 'INSERT INTO Testing (number) VALUE (%s)'
    cursor.execute(insert, (5,))
    show_table(cursor)
    # (1L, 5L)

If we try to set the number to 5.5:

    update = 'UPDATE Testing SET number = %s where id = %s'
    cursor.execute(update, [5.5, 1])

instead the number is stored as 6:

    show_table(cursor)
    # (1L, 6L)

If we alter the data type of the number field to DECIMAL(8,2):

    alter = 'ALTER TABLE `Testing` CHANGE `number` `number` DECIMAL(8,2)'
    cursor.execute(alter)

then setting number to 5.5 stores number as a decimal:

    cursor.execute(update, [5.5, 1])
    show_table(cursor)
    # (1L, Decimal('5.50'))

Of course, alternatively, you could create a Testing table with a number field with DECIMAL data type from the beginning, and then floats would be stored as decimals from the start.

PS. It's not quite clear (to me) if you really want a DECIMAL(M,D) data type. If you use DECIMAL(M,D), then querying the table will return numbers which are decimal.Decimals on the Python side. If you just want regular Python floats, then define Testing with a number field with data type FLOAT instead of DECIMAL(M,D).

Upvotes: 2

Related Questions