> On Nov 11, 2014, at 12:25 PM, Thomas Wanschik <[email protected]>
> wrote:
>
> Hi,
> the query
>
> session.query(func.sum(MyModel.amount)).scalar()
>
> with
>
> MyModel.amount = Column(SmallInteger, nullable=False)
>
> returns a Decimal instead of an integer when using mysql with the default
> driver (as well as with the cymysql driver).
>
> How can I get this query to return an integer? And why can't it return an
> integer automatically?
the datatypes returned here are determined by the MySQL server protocol and the
driver, SQLAlchemy isn’t very much involved. the SUM expression is likely
reporting the result as a NUMERIC which translates to a Decimal.
We just had a similar issue regarding floats, and while MySQL doesn’t support
CAST to float, in this case it does support a CAST to exactly “signed integer",
so do that, here’s a demo:
from sqlalchemy import *
m = MetaData()
t = Table('t', m, Column('amount', SmallInteger))
e = create_engine("mysql://scott:tiger@localhost/test", echo='debug')
m.drop_all(e)
m.create_all(e)
e.execute(t.insert(), amount=250)
result = e.execute(select([cast(func.sum(t.c.amount), Integer)]))
print result.scalar()
SELECT CAST(sum(t.amount) AS SIGNED INTEGER) AS anon_1
FROM t
2014-11-11 12:39:01,484 INFO sqlalchemy.engine.base.Engine ()
2014-11-11 12:39:01,484 DEBUG sqlalchemy.engine.base.Engine Col ('anon_1',)
2014-11-11 12:39:01,485 DEBUG sqlalchemy.engine.base.Engine Row (250L,)
250
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
