possible numeric regression on 0.9.1

[Mike Bayer]

test env: sql server 2005 and pyodbc 3.0.7 and freetds 0.91, on debian 8.0
product env: sql server 2005 and pyodbc 3.0.7 and native client 10.0, on windows 2003
above two env  is the same behavior with 0.9.1, always return decimal object for float type

follow is part data model:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column, 
from sqlalchemy.types import SmallInteger, Integer, Float
from sqlalchemy.types import String

Base = declarative_base()

class Unit(Base):
    __tablename__ = 't_MeasureUnit'

    id = Column('FMeasureUnitID', Integer, autoincrement=False,  primary_key=True)
    name = Column('FName', String(80), nullable=False)
    number = Column('FNumber', String(30), nullable=False)
    short_number = Column('FShortNumber', String(30))
    conversation = Column('FConversation', Integer, server_default='(1)')
    coefficient = Column('FCoefficient', Float, nullable=False, server_default='(1)')
    deleted = Column('FDeleted', SmallInteger, nullable=False, server_default='(0)')

with 0.8.4, conversation column return python float object, upgrade to 0.9 or 0.9.1 all Float column always return decimal object.
my project and the data model has been running for over two years, pass two year with 0.7 and 0.8 no problem. my project not need to use decimal, float precision  is sufficient.

[Mike Bayer]

will try this out but waiting from the user if they meant the "coefficient" column, not "conversation"

[Mike Bayer]

this is due to the column being actually DECIMAL on the DB side. the MSSQL dialect in 0.9 specifically has a pyodbc float type and it does not check for a type handler. We can check for pyodbc sending us the "Decimal" class here in the result processor; some dialects, like that of psycopg2, are smart enough to look at the incoming "coltype" to determine the correct handling, while others (most?) are not (e.g. MySQLdb sends us integer codes but we don't have a lookup for them).

So in this case the behavior of using a Float to receive Numeric/DECIMAL values is undefined; it will vary by backend. In MSSQL's case it's just changed. I'd rather not get into trying to support mismatches like this consistently in all cases, it's better to just use the correct type.

here's a test just for record:

import sys
import cdecimal
sys.modules["decimal"]("decimal") = cdecimal

from sqlalchemy import *

e = create_engine("mssql://scott:tiger@ms_2005", echo='debug')

conn = e.connect()
trans = conn.begin()

t = Table('t', MetaData(), Column('data', Numeric(10, 5)))
t.create(conn)
conn.execute(t.insert().values(data="45.17"))

t2 = Table('t', MetaData(), Column('data', Float()))

print(conn.execute(t2.select()).fetchall())

t3 = Table('t', MetaData(), Column('data', Numeric(asdecimal=False)))

print(conn.execute(t3.select()).fetchall())