search

michiya / django-pyodbc-azure

Django backend for Microsoft SQL Server and Azure SQL Database using pyodbc
https://pypi.python.org/pypi/django-pyodbc-azure
BSD 3-Clause "New" or "Revised" License
321 stars 140 forks source link

Inserting Decimals at max_digits Results in "django.db.utils.DataError: ('String data, right truncation: length 22 buffer 20', '22001')" #154

Closed isosphere closed 6 years ago

isosphere commented 6 years ago

This is a confusing error I'm trying to figure out. Here's the basic setup:

System

Model definition

class examplemodel(models.Model):
    date = models.DateField(blank=False, null=False, db_index=True)
    reference_date = models.DateField(blank=False, null=False, db_index=True)
    value = models.DecimalField(max_digits=10, decimal_places=4, null=True)
    updated = models.DateTimeField()

    class Meta:
        db_table = 'examplemodel'
        verbose_name = 'examplemodel'
        unique_together = (('date', 'reference_date'),)

Data Insertion

data_entry = examplemodel(**{'date': datetime.date(2048,12,1), 'reference_date': datetime.date(2019,2,1), 'value': 131712.5000, 'updated': '2017-03-01 15:30'})
data_entry.save()

Error

Traceback (most recent call last): File "c:\python36\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params) File "c:\python36\lib\site-packages\sql_server\pyodbc\base.py", line 547, in execute return self.cursor.execute(sql, params) pyodbc.DataError: ('String data, right truncation: length 22 buffer 20', '22001')

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "", line 1, in File "c:\python36\lib\site-packages\django\db\models\base.py", line 808, in save force_update=force_update, update_fields=update_fields) File "c:\python36\lib\site-packages\django\db\models\base.py", line 838, in save_base updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields) File "c:\python36\lib\site-packages\django\db\models\base.py", line 924, in _save_table result = self._do_insert(cls._base_manager, using, fields, update_pk, raw) File "c:\python36\lib\site-packages\django\db\models\base.py", line 963, in _do_insert using=using, raw=raw) File "c:\python36\lib\site-packages\django\db\models\manager.py", line 85, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File "c:\python36\lib\site-packages\django\db\models\query.py", line 1076, in _insert return query.get_compiler(using=using).execute_sql(return_id) File "c:\python36\lib\site-packages\django\db\models\sql\compiler.py", line 1112, in execute_sql cursor.execute(sql, params) File "c:\python36\lib\site-packages\django\db\backends\utils.py", line 79, in execute return super(CursorDebugWrapper, self).execute(sql, params) File "c:\python36\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params) File "c:\python36\lib\site-packages\django\db\utils.py", line 94, in exit six.reraise(dj_exc_type, dj_exc_value, traceback) File "c:\python36\lib\site-packages\django\utils\six.py", line 685, in reraise raise value.with_traceback(tb) File "c:\python36\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params) File "c:\python36\lib\site-packages\sql_server\pyodbc\base.py", line 547, in execute return self.cursor.execute(sql, params) django.db.utils.DataError: ('String data, right truncation: length 22 buffer 20', '22001')

Alternative Data Insertion

data_entry = examplemodel(**{'date': datetime.date(2022,1,1), 'reference_date': datetime.date(2019,2,1), 'value': 2.5000, 'updated': '2017-03-01 15:30'})
data_entry.save()

(no error)

data_entry = examplemodel(**{'date': datetime.date(2022,2,1), 'reference_date': datetime.date(2019,2,1), 'value': 100000.5000, 'updated': '2017-03-01 15:30'})
data_entry.save()

(same error)

data_entry = examplemodel(**{'date': datetime.date(2022,2,1), 'reference_date': datetime.date(2019,2,1), 'value': 10000.5000, 'updated': '2017-03-01 15:30'})
data_entry.save()

(no error)

I made a little tweak to base.py in django-pyodbc-azure to help with debugging, printing repr(sql) and repr(params) in execute.

sql & params for the error condition:

'SET NOCOUNT ON INSERT INTO [examplemodel] ([date], [reference_date], [value], [updated]) VALUES (?, ?, ?, ?); SELECT CAST(SCOPE_IDENTITY() AS bigint)' ('2022-02-01', '2019-02-01', '100000.5000', datetime.datetime(2017, 3, 1, 20, 30))

sql & params for a close, non-error condition:

'SET NOCOUNT ON INSERT INTO [examplemodel] ([date], [reference_date], [value], [updated]) VALUES (?, ?, ?, ?); SELECT CAST(SCOPE_IDENTITY() AS bigint)' ('2022-03-01', '2019-02-01', '10000.5000', datetime.datetime(2017, 3, 1, 20, 30))

Since I've been burned with decimal.Decimal and MSSQL before:

Decimal(float(131712.5000)) (ans) Decimal('131712.5')

The only inference I seem to be able to make from this is that I need to increase max_digits. However, I do not understand why, the above is 10 digits including the decimal places. The string error is pretty confusing, as there are no strings involved. Maybe pyodbc or django-pyodbc-azure is converting this decimal to a very long string, but I haven't been able to catch it doing that with SQL profiler since the error occurs at binding rather than execution.

isosphere commented 6 years ago

I replicated the problem using only pyodbc, looks like the problem lies there.