#54 No truncation warnings using Decimal literals

MySQLdb
open
Andy Dustman
MySQLdb (53)
5
2012-09-19
2008-08-14
Richard Davies
No

MySQLdb/converters.py already contains lines which map FIELD_TYPE.DECIMAL into Decimal for reading data. I have experienced bugs indicating that similar explicit conversion is needed to correctly handle all cases of Decimals when writing data.

In MySQLdb-1.2.2, we should add "conversions[Decimal] = Thing2Str". In MySQLdb-SVN-trunk the equivalent is "conversions[Decimal] = object_to_sql".

The test case follows. I have a table:

CREATE TABLE accounts (
user integer NOT NULL UNIQUE,
balance numeric(20, 2) NOT NULL,
)

and I want to execute the following SQL:

update accounts set balance=balance+1.27 where user=1

So I call:

cursor.execute('update accounts set balance=balance+%s where user=1', [Decimal('1.27')])

At present this actually generates:

update accounts set balance=balance+'1.27' where user=1

which is critically different - sometimes this line works, but sometimes it gives me the warning:

_mysql_exceptions.Warning: Data truncated for column 'balance' at row 1

I believe this indicates that addition is being done in float arithmetic, with tiny precision errors which must then be truncated away. The version in which the value is unquoted never generates this error, so I believe that the addition is done correctly in decimal arithmetic.

Discussion

  • Richard Davies
    Richard Davies
    2008-08-14

    Patch for MySQL 1.2.2c1

     
    Attachments