pyodbc returns decimals rounded to 2 decimal places from SQL Server

[cmcca...@gmail.com]

Hi,

I've not been able to find this issue raised so here goes. Apologies if it is a known issue.

When querying SQL Server 2012 from Python 3 I am finding all the DECIMAL(p,s) types are rounded to two decimal places. 

Here's a script to demonstrate usage:

import pyodbc
import sys
from decimal import getcontext

print('python version is: {0}'.format(sys.version))
print('python version info is: {0}'.format(sys.version_info))
print('pyodbc version is: {0}'.format(pyodbc.version))
print('decimal context is: {0}'.format(getcontext()))

cnxn = pyodbc.connect("DSN=SOMESERVER")
cursor = cnxn.cursor()
cursor.execute("select 3.141592")
rows = cursor.fetchall()
for row in rows:
    print(row[0])
cursor.close()

cursor = cnxn.cursor()
cursor.execute('select cast(2.71828 as decimal(6,5)) as [PI]')
row = cursor.fetchone()
print(row)
# Problem: the precision is lost in both cases

# what database are we on?
cursor.execute('select @@version as databaseVersion')
print(cursor.fetchone())

cursor.close()

This will give you the following output:

python version is: 3.4.4 |Continuum Analytics, Inc.| (default, Feb 16 2016, 09:54:04) [MSC v.1600 64 bit (AMD64)]

python version info is: sys.version_info(major=3, minor=4, micro=4, releaselevel='final', serial=0)

pyodbc version is: 3.0.10

decimal context is: Context(prec=28, rounding=ROUND_HALF_EVEN, Emin=-999999, Emax=999999, capitals=1, clamp=0, flags=[], traps=[InvalidOperation, DivisionByZero, Overflow])

3.14

(Decimal('2.72'), )

('Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) \n\tOct 20 2015 15:36:27 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )\n', )

As for the ODBC setup:

System DSN

Microsoft SQL Server Native Client 10.0 

Version=2007.100.2531.00

File=SQLSRV32.DLL 

also tried:

SQL Server

Version=6.01.7601.17514

File=SQLNCLI10.DLL

I will try the MS native client 11 next and see if that makes a difference.

Any suggestions or workarounds very welcome!

Thanks.

[cmcca...@gmail.com]

And after all that effort to craft a question: it was the driver.

If I set up a DSN with MSODBC 11 driver then I get the following output:

3.141592

(Decimal('2.71828'), )

My data is back.

Still, next time someone gets this issue, they might stumble upon this post...