Unable to pass decimal.Decimal as parameter (InterfaceError)

[GoogleCodeExporter]

What steps will reproduce the problem?

Attempting to run any query with a parameter of type `decimal.Decimal` will 
throw an InterfaceError.  I'm using the SQLAlchemy ORM with a column declared 
as "unit_price = Column(Numeric(precision=10, scale=4, asdecimal=True), 
nullable=False)",  although I've deduced that it is not just an issue with how 
SQLAlchemy handles Cloud SQL as I've thrown the same exception with a raw DBAPI 
connection like so:

   conn.execute(some_sql, decimal.Decimal(1.23))

I also found a user on StackOverflow using Django that appears to be having 
issue (http://goo.gl/tcZ0h)

Here is the stacktrace when using SQLAlchemy:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/smlynch/Development/sbcs/expense-assist/packages/sqlalchemy/engine/base.py", line 664, in execute
    params)
  File "/Users/smlynch/Development/sbcs/expense-assist/packages/sqlalchemy/engine/base.py", line 808, in _execute_text
    statement, parameters
  File "/Users/smlynch/Development/sbcs/expense-assist/packages/sqlalchemy/engine/base.py", line 878, in _execute_context
    context)
  File "/Users/smlynch/Development/sbcs/expense-assist/packages/sqlalchemy/engine/base.py", line 871, in _execute_context
    context)
  File "/Users/smlynch/Development/sbcs/expense-assist/packages/sqlalchemy/engine/default.py", line 320, in do_execute
    cursor.execute(statement, parameters)
  File "/Applications/GoogleAppEngineLauncher.app/Contents/Resources/GoogleAppEngine-default.bundle/Contents/Resources/google_appengine/google/storage/speckle/python/api/rdbms.py", line 562, in execute
    statement, args, request.bind_variable.add)
  File "/Applications/GoogleAppEngineLauncher.app/Contents/Resources/GoogleAppEngine-default.bundle/Contents/Resources/google_appengine/google/storage/speckle/python/api/rdbms.py", line 427, in _AddBindVariablesTo
Request
    raise InterfaceError('unknown type %s for arg %d' % (type(arg), i))
InterfaceError: (InterfaceError) unknown type <class 'decimal.Decimal'> for arg 
0 'SELECT product_price.archived AS product_price_archived, 
product_price.created_at AS product_price_created_at, product_price.updat
ed_at AS product_price_updated_at, product_price.id AS product_price_id, 
product_price.volume AS product_price_volume, product_price.start_date AS 
product_price_start_date, product_price.end_date AS product_price_
end_date, product_price.unit_price AS product_price_unit_price, 
product_price.unit_description AS product_price_unit_description, 
product_price.contract_id AS product_price_contract_id, 
product_price.product_id AS
 product_price_product_id, product_price.updated_by_id AS product_price_updated_by_id, product_price.created_by_id AS product_price_created_by_id \nFROM product_price \nWHERE product_price.volume <= %s' (Decimal('
1.229999999999999982236431605997495353221893310546875'),)

I found if I convert the decimal.Decimal to a string the query will execute:
    conn.execute(some_sql, str(decimal.Decimal(1.23)))

Original issue reported on code.google.com by techniq35 on 10 Mar 2013 at 4:56

[GoogleCodeExporter]

Could this be as simple as adding `decimal.Decimal: jdbc_type.DECIMAL` to 
_PYTHON_TYPE_TO_JDBC_TYPE?

https://code.google.com/p/googleappengine/source/browse/trunk/python/google/stor
age/speckle/python/api/rdbms.py#141

Original comment by techniq35 on 12 Mar 2013 at 6:11

[GoogleCodeExporter]

I hope to hear a response soon as this is a critical issue for us and are 
investigating other hosting options at this point if a fix is not found soon.

Original comment by techniq35 on 12 Mar 2013 at 6:28

[GoogleCodeExporter]

We're working on a fix for this, but for now, you can try including the 
following monkeypatch somewhere in your application's bootstrapping code for 
immediate support: 

import decimal
from google.storage.speckle.proto import jdbc_type
from google.storage.speckle.python.api import rdbms as api_rdbms
from google.storage.speckle.python.api import converters

api_rdbms._PYTHON_TYPE_TO_JDBC_TYPE[decimal.Decimal] = jdbc_type.DECIMAL
converters.conversions[decimal.Decimal] = converters.Any2Str
converters.conversions[jdbc_type.DECIMAL] = decimal.Decimal

Then, your code should function with decimal support:

from google.appengine.api import rdbms
conn = rdbms.connect('<instance name>')
cursor = conn.cursor()
cursor.execute('SELECT %s', decimal.Decimal('1.23'))
print cursor.fetchone()
(u'1.23',)

Original comment by mmcdon...@google.com on 13 Mar 2013 at 12:12

[GoogleCodeExporter]

Thank you. My initial tests look positive.  I will test more tomorrow and let 
you know my findings.

Original comment by techniq35 on 13 Mar 2013 at 12:44

[GoogleCodeExporter]

The monkeypatch is working well.  It appears the rdbms is wrapping more thrown 
errors than yesterday as I've had to update the SQLAlchemy driver to handle 
these (could just be coincidence).

Original comment by techniq35 on 13 Mar 2013 at 4:58

[GoogleCodeExporter]

These issues with the deprecated drivers will not be fixed now that Cloud SQL 
works against the regular MySQL drivers and using the regular drivers is the 
only supported solution.

See https://developers.google.com/cloud-sql/docs/native-mysql-gae

Thank you.

Original comment by rclev...@google.com on 12 Feb 2014 at 8:15

• Changed state: WontFix