Value of unknown type: <class 'decimal.Decimal'

[nicolaibaralmueller]

Using below query fails.

      - name: query
        mssql_query:
          login_user: sa
          login_password: "{{ sql_admin_password }}"
          login_host: localhost
          db: msdb
          #as_dict: true
          query: >
                SELECT bcks.database_name, bckS.backup_start_date, bckS.backup_finish_date, CAST(bcks.backup_size / 1073741824.0E AS DECIMAL(10, 2)) as [Backup Size(GB)],
                is_compressed, is_encrypted, physical_device_name
                FROM  msdb.dbo.backupset bckS INNER JOIN msdb.dbo.backupmediaset bckMS
                ON bckS.media_set_id = bckMS.media_set_id
                INNER JOIN msdb.dbo.backupmediafamily bckMF
                ON bckMS.media_set_id = bckMF.media_set_id
                WHERE bckS.backup_finish_date > (SELECT CONVERT(varchar, getdate(), 23))
                ORDER BY bckS.backup_start_date DESC
        register: backupset

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: TypeError: Value of unknown type: <class 'decimal.Decimal'>, 730.48
fatal: [xx.xx.xx.xx]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n  File \"<stdin>\", line 102, in <module>\n  File \"<stdin>\", line 94, in _ansiballz_main\n  File \"<stdin>\", line 40, in invoke_module\n  File \"/usr/lib/python2.7/runpy.py\", line 188, in run_module\n    fname, loader, pkg_name)\n  File \"/usr/lib/python2.7/runpy.py\", line 82, in _run_module_code\n    mod_name, mod_fname, mod_loader, pkg_name)\n  File \"/usr/lib/python2.7/runpy.py\", line 72, in _run_code\n    exec code in run_globals\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/modules/mssql_query.py\", line 168, in <module>\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/modules/mssql_query.py\", line 164, in main\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/module_utils/basic.py\", line 2072, in exit_json\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/module_utils/basic.py\", line 2065, in _return_formatted\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/module_utils/basic.py\", line 422, in remove_values\n  File \"/tmp/ansible_mssql_query_payload_CE9v8f/ansible_mssql_query_payload.zip/ansible/module_utils/basic.py\", line 401, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'decimal.Decimal'>, 730.48\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

Executing the query manually produces no error. Seems that the module does not support decimal datatypes in the output.

[RamyChaabane]

Hello,

I have the same problem with MySQL database
https://github.com/RamyChaabane/mysql_query

• name: selecting result
  mysql_query:
  db: classicmodels
  login_user: root
  login_password: "{{ mysql_password }}"
  query: select * from payments where customerNumber='496' and paymentDate='2004-12-31'

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: TypeError: Value of unknown type: <type 'exceptions.TypeError'>, Value of unknown type: <class 'decimal.Decimal'>, 52166.10

Manually:

db_connect = pymysql.Connect(**connection_params)
with db_connect.cursor() as cursor:
... cursor.execute("select * from payments where customerNumber='496' and paymentDate='2004-12-31'")
... re = cursor.fetchall()
...
1
print re
[{u'checkNumber': 'MN89921', u'amount': Decimal('52166.10'), u'customerNumber': 496, u'paymentDate': datetime.date(2004, 12, 31)}]

[lowriepa]

I found casting INT column results to CHAR type works:
SELECT CAST( column1 AS CHAR) AS header, CAST(SUM(column2) AS CHAR) AS total FROM wherever...

[murrahjm]

I see this is pretty old but I ran into this issue and found a fix for it so I thought I'd post it up. Basically the error is from the ansible module trying to convert the sql output to json. I think this happens in the module.exit_json method.

As a fix, instead of letting ansible try to convert the python object to json, I converted it to json myself. so something like:

query_result = cursor.fetchall()
result = json.dumps(query_result, default=str)

that should cover any non json-able data types like decimal, datetime, etc.