Dear all,
I have a table that has 2 numeric columns, and I'm writing a query that
performs some arithmetic on the filter clause between those columns and a
Decimal. The problem that I'm facing is that I don't get any results at
all. After a while I realized that the SQL statement getting generated is
dumping Decimals as strings, and when strings are involved in a numeric
expression they get converted to floats. So, my query is not returning
anything at all due to float representation limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
filter expression failed. Can anyone help me in getting a cast over a query
parameter to work in a filter expression ?
I'm attaching a sample to reproduce the issue.
Thanks a lot.
--
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.
import sqlalchemy
from sqlalchemy import orm
from sqlalchemy.ext import declarative
import decimal
Base = declarative.declarative_base()
engine = None
session_factory = None
class Balance(Base):
__tablename__ = "balance"
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
full_balance = sqlalchemy.Column(sqlalchemy.Numeric(precision=16, scale=8), nullable=False)
available_balance = sqlalchemy.Column(sqlalchemy.Numeric(precision=16, scale=8), nullable=False)
def initialize():
global engine
global session_factory
engine = sqlalchemy.create_engine("mysql+mysqldb://develuser:develpassword@localhost/bugtest", pool_recycle=3600)
session_factory = orm.sessionmaker()
session_factory.configure(bind=engine)
def create_schema():
Base.metadata.create_all(engine)
def main():
# Manually execute these 3 statements in MySQL:
# CREATE DATABASE bugtest;
# CREATE USER 'develuser'@'localhost' IDENTIFIED BY 'develpassword';
# GRANT ALL ON bugtest.* TO 'develuser'@'localhost';
initialize()
create_schema()
# Insert a new balance.
session = session_factory()
db_balance = Balance()
db_balance.full_balance = decimal.Decimal('0.00000003')
db_balance.available_balance = decimal.Decimal('0.00000002')
session.add(db_balance)
session.commit()
# Query.
amount = decimal.Decimal('0.00000001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + amount <= Balance.full_balance
)
print len(query.all()), "should be > 0"
if __name__ == "__main__":
main()
