Functions sometimes return decimal or float

[Robert Buchholz]

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?


Cheers,
Robert

[Jonathan Vanasco]

what about using custom compiler functions ( http://docs.sqlalchemy.org/en/rel_0_9/core/compiler.html#utc-timestamp-function ) ?

you could make a custom function...

    sum_integer()

and just call that instead of the default sqlalchemy option 

[Robert Buchholz]

Hey Jonathan,

On Fr, 2013-12-06 at 10:12 -0800, Jonathan Vanasco wrote:
> what about using custom compiler functions

that is definitely a good idea to cast the computation result more
elegantly. Do you also know a way to solve the issue of doing it wrong
by accident and not noticing?
Besides SUM, there's still AVG and other mathematical functions, that
you can still use and not be aware of the impact in other DB engines.

My goal was to make it harder to make a mistake unknowingly.


Cheers,

Robert

[Jonathan Vanasco]

I just learned all about the Events model.  You could potentially write a listener that can filter the data for you.  I'm not very familiar with the events though, and it could be a bit difficult to do -- as some items would be functions, others column operations, and others from 'text' constructs -- you might end up needing to apply a filter to every 'number-like' value, and that could cause bad side effects.

are there any methods in the databases that would set up per-session options ?

mysql has session-level options, sqlite has pragma and some other stuff.  maybe you could tell the db to format stuff on it's end?

[Michael Bayer]

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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.