1. Michael Manfre
  2. django-mssql
  3. Issues

Issues

Issue #55 resolved

Decimals being saved with only four decimal places

João Pedro Francese
created an issue

When I use django-mssql, all DecimalFields are being saved with 4 decimal places in the database, even through the fields have far greater precision: all of them are decimal(28, 13). Everything works fine with other databases (MySQL and Postgres).

This was driving me mad, and I began debugging the save code, digging as deep as I could. I've managed to trace it down to the conversion from Decimal to adDecimal, which happens in Cursor.execute() in dbapi.py.

If I pause the Aptana debugger in line 520

                _configure_parameter(p, value)  # line 519
                self.cmd.Parameters.Append(p)   # line 520

when it's handling a Decimal parameter, and run the following command in the Console window

print value, value.__class__, p, p.__class__

I get

1643991.703308 <class 'decimal.Decimal'> 1643991,7033 win32com.client.CDispatch

which is obviously wrong. I don't know how to go further because it's crossing the C layer.

When I query the field above through SQL Server Management Studio, the result is 1643991.7033000000000. For the record, if I run a UPDATE query manually in Management Studio (UPDATE portfolio_snapshot SET shares = 1643991.703308 WHERE id = 20), the field gets saved with the appropriate number of places.

Possibly useful information:

  • Running django-mssql 1.4 with Django 1.5, Python 2.7
  • Happens in both Windows 8 and Windows Server 2008 R2
  • Using SQL Server 2008 R2
  • Using 'sqlncli10' as the provider

Comments (11)

  1. Michael Manfre repo owner

    This appears to be either a COM or ADO limitation. A similar issue was previously encountered when adding support for datetime2 and datetimeoffset data types. The most likely fix is to pass the Decimal value to SQL as a string. I predict a string format headache dealing with Decimals like '1.2E+5'.

  2. João Pedro Francese reporter

    Thanks for the directions. I was able to make it work by changing the _configure_parameter() method, although I'm not sure I did it the best way possible.

    I changed the elif starting in dbapi.py:199 from

        elif isinstance(value, decimal.Decimal):
            p.Value = value
            exponent = value.as_tuple()[2]
            digit_count = len(value.as_tuple()[1])
    
            if exponent == 0:
                p.NumericScale = 0
                p.Precision =  digit_count
            elif exponent < 0:
                p.NumericScale = -exponent
                p.Precision = digit_count
                if p.Precision < p.NumericScale:
                    p.Precision = p.NumericScale            
            elif exponent > 0:
                p.NumericScale = 0
                p.Precision = digit_count + exponent
    

    to

            p.Type = adBSTR
            exponent = value.as_tuple()[2]
            if exponent < 0:
                s = "%%.%df" % -exponent % value
            else:
                s = "%d" % value
            p.Value = s
            p.Size = len(s)
    

    How should I proceed from here? Is the snippet above enough for you to test and include in the lib, or do you want me to create a fork, commit and make a pull request?

  3. João Pedro Francese reporter

    "I predict a string format headache dealing with Decimals like '1.2E+5'."

    Sadly, you were right. The solution implemented in 8c1e5bf2e315 fails with that kind of number.

    >>> from sqlserver_ado.dbapi import format_decimal_as_string, decimal
    >>> number = decimal.Decimal('28.2') / decimal.Decimal('0.01')
    >>> print number
    2.82E+3
    >>> print format_decimal_as_string(number)
    282.
    

    I suggest adding an if-else checking for the exponent (somewhat like in the solution I proposed initially):

    def new_format_decimal_as_string(value):
        """
        Convert a decimal.Decimal to a fixed point string. Code borrowed from
        Python's moneyfmt recipe.
        https://docs.python.org/2/library/decimal.html#recipes
        """
        sign, digits, exp = value.as_tuple()
        if exp > 0:
            return "%d" % value
        result = []
        digits = list(map(str, digits))
        build, next = result.append, digits.pop
        for i in range(-exp):
            build(next() if digits else '0')
        build('.')
        if not digits:
            build('0')
        while digits:
            build(next())
        if sign:
            build('-')
        return ''.join(reversed(result))
    
    >>> print new_format_decimal_as_string(number)
    2820
    
  4. Michael Manfre repo owner

    The formatting you proposed initially doesn't work.

    "%%.%df" % -exponent % value
    

    People use decimal instead of float because they need the extra precision. Converting the decimal to a float on save stores the wrong value in many situations.

  5. João Pedro Francese reporter

    I realized that; that's why I didn't include that part in new_format_decimal_as_string(). The only part of my initial proposal that I used is this:

     if exp > 0:
            return "%d" % value
    

    When exp is <= 0, I kept your original fix's code.

  6. Log in to comment