Data corruption with sqlalchemy and sql server

[shawjef3]

I have a numeric(20,2) column in Microsoft SQL Server 2008 R2 into which I've inserted 10 random values.

4834782447686723.20 7024994842825643.50 6406817835830643.20 9613049409652183.00 9399498980445681.90 5183119749740031.40 5342892910645982.10 455367940560899.04 8598415910039290.90 6879037788280369.90

The same column with the same ordering, when selected on PostgreSQL 9.4 on CentOS 7 x64 via a multicorn foreign table, gives

4834782447686723.00 7024994842825644.00 6406817835830643.00 9613049409652184.00 9399498980445682.00 5183119749740031.00 5342892910645982.00 455367940560899.06 8598415910039291.00 6879037788280370.00

[rdunklau]

What driver are you using ?

[shawjef3]

here are the srvoptions: "{wrapper=multicorn.sqlalchemyfdw.SqlAlchemyFdw,db_url=mssql+pymssql://sa:redacted@jshawd_log/test}"

[shawjef3]

I believe what's happening is that the numeric column is being converted to a floating point number (double precision) before being sent to PostgreSQL. The values in PostgreSQL are consistent with first converting the original numeric value to a float and then back into a numeric(20,2).

[shawjef3]

sqlalchemy 0.9.7, python 2.7.5, multicorn 1.1.0, pymssql 2.1.1

[rdunklau]

Upon testing, it appears that I get the following message:

SAWarning: Dialect mssql+pymssql does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage

The solution provided by SQLAlchemy creator is to use another driver:

http://stackoverflow.com/questions/13532177/accessing-decimals-using-pymssql

[shawjef3]

pyodbc doesn't work for me either, because it doesn't work with MS SQL's uniqueidentifier.

[rdunklau]

So this is a problem with the general python sqlserver ecosystem, then.