On Dec 6, 2013, at 12:28 PM, Robert Buchholz <robert.buchh...@goodpoint.de> wrote:
> Hello,
>
> I'm working with a database that stores mostly integer values or floats
> with little (relevant) precision. Thus, I am using all numeric types
> with asdecimal=True.
> Unfortunately, our database-calculations return Decimal objects when run
> in MySQL, though they return int/float in Sqlite. While I can deal with
> either type, the inconsistency is causing some pain -- as we only notice
> the lack of return value conversion / db CAST rather late in the
> development process.
> Generally, I would like the ORM ensure consistent result types for a
> query like:
> DBSession.query(func.sum(Table.int_column))
>
> We often handle this as:
> DBSession.query(cast(func.sum(Table.int_column), Integer))
>
> Is there something we can do to generally avoid this type of bug?
> E.g., always return all DB-results to float, issue a warning when an
> explicit cast is missing and some DBs may return decimal, ... ?
>
> How do you deal with this situation?
the func.XYZ() construct returns an object that is untyped in most cases,
meaning SQLAlchemy has no opinion about the data that’s returned by the
database. This type can be specified using “type_=<sometype>”.
However, that should not be necessary for sum() - there’s a small subset of
known SQL functions for which we assign a fixed type. sum() is actually one of
them, and should be using the same type as that of the given expression. So if
you hand it a Column with type Numeric(asdecimal=True), that will be the return
type of the func, and the dialect-specific logic will take effect.
A test case (below) confirms this is the case for sum() - the type of column is
maintained, whether asdecimal is True or False, succeeds on both MySQL and
SQLite. If you’re using some function other than sum() which isn’t one of the
known types, then you need to add type_=<yourtype> to it, like
func.myfunc(table.c.column, type_=Float(asdecimal=True)).
from sqlalchemy import func, Table, Column, MetaData, Float, select,
create_engine
from decimal import Decimal
# create a table with a Float column not using decimal.
m = MetaData()
t = Table('t', m, Column('data', Float))
dburl = "sqlite://"
#dburl = "mysql://scott:tiger@localhost/test"
e = create_engine(dburl, echo=True)
m.drop_all(e)
m.create_all(e)
e.execute(t.insert(), data=45.67)
# now let's query func.sum() using a Float with asdecimal:
m2 = MetaData()
t_with_decimal = Table('t', m2, Column('data', Float(asdecimal=True)))
# with the Float column, we get float
assert isinstance(
e.scalar(select([func.sum(t.c.data)])),
float
)
# with the Float(asdecimal=True) column, we get Decimal
assert isinstance(
e.scalar(select([func.sum(t_with_decimal.c.data)])),
Decimal
)
>
>
> Cheers,
> Robert
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
