#54 No truncation warnings using Decimal literals

[Richard Davies]

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.

[Richard Davies]

Patch for MySQL 1.2.2c1