The way I'm handling this is by creating a TypeDecorator with the following
function:
def bind_expression(self, value):
"""
The objective of this is to force MySQL to deal with bind
parameters as
decimals rather than as strings. MySQL for some insane reason
falls back
to floating point arithmetic when you add a Decimal to a String.
"""
if value is not None:
value = type_coerce(value, String)
return cast(value, DECIMAL(self.precision, self.scale))
return super(ClassName, self).bind_expression(value)
Does that make sense? Any reason why this isn't the default behavior of the
Numeric column type?
On Monday, April 13, 2015 at 2:47:13 PM UTC-4, Gabriel Becedillas wrote:
>
> Thanks a lot Mike.
> Although I did see the "Specify 'fetch' or False for the
> synchronize_session parameter" in the error I associated that with a
> connection-time parameter and I thought 'no.. that has nothing to do with
> my problem'.
> My bad.
> Thanks a lot.
>
> On Mon, Apr 13, 2015 at 3:31 PM, Mike Bayer <mik...@zzzcomputing.com
> <javascript:>> wrote:
>
>>
>>
>> On 4/13/15 2:25 PM, Gabriel Becedillas wrote:
>>
>> Dear Michael,
>> Thanks a lot for your reply.
>> In trying to narrow the problem as much as possible, I missed something
>> important in my example. I'm actually doing an UPDATE, not a SELECT. When I
>> wrote 'I tried casting my decimals using sqlalcheme.cast(...,
>> sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work because the
>> filter expression failed' I meant that it didn't work in an update
>> scenario. In a select scenario it works ok. This is what I should have
>> wrote in my bug_test.py:
>>
>> amount = decimal.Decimal('0.00000001')
>> query = session.query(Balance)
>> query = query.filter(
>> Balance.available_balance + sqlalchemy.cast(amount,
>> sqlalchemy.Numeric(precision=16, scale=8)) <= Balance.full_balance
>> )
>>
>> values = {}
>> values[Balance.available_balance] = Balance.available_balance + amount
>> row_count = query.update(values)
>> print row_count, "rows updated"
>>
>>
>> and the error I get is 'sqlalchemy.exc.InvalidRequestError: Could not
>> evaluate current criteria in Python. Specify 'fetch' or False for the
>> synchronize_session parameter.'. This is not even getting to MySQL.
>>
>> for query.update() you usually want to send synchronize_session=False.
>>
>> also the cast() needs to be Decimal(), not Numeric(). Will not work with
>> Numeric().
>>
>>
>>
>>
>> Thanks a lot
>>
>> On Monday, April 13, 2015 at 2:46:14 PM UTC-3, Michael Bayer wrote:
>>>
>>>
>>>
>>> On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
>>>
>>> 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 ?
>>>
>>>
>>> this is driver stuff. If you change the query to see what you're
>>> getting:
>>>
>>> query = session.query(Balance.available_balance + amount,
>>> Balance.full_balance)
>>>
>>> you can see there's some floating point noise in there, not to mention
>>> we're even getting the value back as a floating point:
>>>
>>> Col (u'anon_1', u'balance_full_balance')
>>> 2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
>>> (3.0000000000000004e-08, Decimal('3E-8'))
>>>
>>> I'm getting the same result with MySQL-Python, PyMySQL, and
>>> Mysql-connector. The issue is definitely in the drivers however, the code
>>> below produces no result for all three drivers:
>>>
>>> conn = engine.raw_connection()
>>> cursor = conn.cursor()
>>> cursor.execute(
>>> "SELECT balance.available_balance + %s AS anon_1, "
>>> "balance.full_balance AS balance_full_balance FROM balance "
>>> "WHERE balance.available_balance + %s <= balance.full_balance",
>>> (amount, amount))
>>>
>>> print cursor.fetchall()
>>>
>>> If I embed the number 0000000.1 as is into the query without using a
>>> parameter, then it works, as it does on the command line.
>>>
>>> Looking into PyMySQL since that's the driver I'm most familiar with, if
>>> we send a Decimal it is doing this:
>>>
>>> SELECT balance.available_balance + 1E-8 AS anon_1, balance.full_balance
>>> AS balance_full_balance FROM balance WHERE balance.available_balance + 1E-8
>>> <= balance.full_balance
>>>
>>> What is interesting is that these values as passed in an INSERT *are*
>>> working correctly. Which means really, this is a MySQL bug; I can prove
>>> it at the command line.
>>>
>>> First, we illustrate that scientific notation *is* accepted directly by
>>> MySQL:
>>>
>>> mysql> insert into balance (full_balance, available_balance) values
>>> (3E-8, 2E-8);
>>> Query OK, 1 row affected (0.00 sec)
>>>
>>>
>>> values go in just fine (note I increased the scale in the table here,
>>> hence the two trailing 0's):
>>>
>>> mysql> select * from balance;
>>> +----+--------------+-------------------+
>>> | id | full_balance | available_balance |
>>> +----+--------------+-------------------+
>>> | 2 | 0.0000000300 | 0.0000000200 |
>>> +----+--------------+-------------------+
>>> 1 row in set (0.00 sec)
>>>
>>> but in the WHERE clause, *it fails*:
>>>
>>> mysql> select * from balance where available_balance + 1E-8 <=
>>> full_balance;
>>> Empty set (0.00 sec)
>>>
>>> writing out the whole value, *it succeeds*:
>>>
>>> mysql> select * from balance where available_balance + 0.00000001 <=
>>> full_balance;
>>> +----+--------------+-------------------+
>>> | id | full_balance | available_balance |
>>> +----+--------------+-------------------+
>>> | 2 | 0.0000000300 | 0.0000000200 |
>>> +----+--------------+-------------------+
>>> 1 row in set (0.00 sec)
>>>
>>> we can see that *MySQL itself is doing floating point*, so that's really
>>> the bug here:
>>>
>>> mysql> select available_balance + 1E-8 from balance;
>>> +----------------------------+
>>> | available_balance + 1E-8 |
>>> +----------------------------+
>>> | 0.000000030000000000000004 |
>>> +----------------------------+
>>> 1 row in set (0.00 sec)
>>>
>>> We can in fact make it work with a CAST. However! crazytown time.
>>> Even though NUMERIC and DECIMAL are equivalent in MySQL, cast will *not*
>>> accept NUMERIC (note SQLAlchemy only warns on these and only as of 1.0 I
>>> think):
>>>
>>> mysql> select available_balance + CAST(1E-8 AS NUMERIC) from balance;
>>> ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>> manual that corresponds to your MySQL server version for the right syntax
>>> to use near 'NUMERIC) from balance' at line 1
>>>
>>> But it *will* accept DECIMAL:
>>>
>>> mysql> select available_balance + CAST(1E-8 AS DECIMAL) from balance;
>>> +-------------------------------------------+
>>> | available_balance + CAST(1E-8 AS DECIMAL) |
>>> +-------------------------------------------+
>>> | 0.0000000200 |
>>> +-------------------------------------------+
>>> 1 row in set (0.00 sec)
>>>
>>> So there's our answer:
>>>
>>> from sqlalchemy import cast, DECIMAL
>>>
>>> amount = decimal.Decimal('0.00000001')
>>> query = session.query(Balance.available_balance + cast(amount,
>>> DECIMAL()), Balance.full_balance)
>>> query = query.filter(
>>> Balance.available_balance + cast(amount, DECIMAL()) <=
>>> Balance.full_balance
>>> )
>>>
>>>
>>> SELECT balance.available_balance + CAST(%s AS DECIMAL) AS anon_1,
>>> balance.full_balance AS balance_full_balance
>>> FROM balance
>>> WHERE balance.available_balance + CAST(%s AS DECIMAL) <=
>>> balance.full_balance
>>> 2015-04-13 13:43:16,630 INFO sqlalchemy.engine.base.Engine
>>> (Decimal('1E-8'), Decimal('1E-8'))
>>> 2015-04-13 13:43:16,631 DEBUG sqlalchemy.engine.base.Engine Col
>>> (u'anon_1', u'balance_full_balance')
>>> 2015-04-13 13:43:16,631 DEBUG sqlalchemy.engine.base.Engine Row
>>> (Decimal('2E-8'), Decimal('3E-8'))
>>> 1 should be > 0
>>>
>>> The drivers would really be nice if they expanded out these values from
>>> scientific notation. However, MySQL's bizarrely inconsistent behavior
>>> here is probably why the need for this hasn't been observed. So stick
>>> with cast(value, DECIMAL()) for now when using the value in the WHERE
>>> clause.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> 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 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/d/optout.
>>>
>>>
>>> --
>> 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 <javascript:>.
>> To post to this group, send email to sqlal...@googlegroups.com
>> <javascript:>.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
>> You received this message because you are subscribed to a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/lRWebyWl_A4/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+...@googlegroups.com <javascript:>.
>> To post to this group, send email to sqlal...@googlegroups.com
>> <javascript:>.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
