python - 查询具有数字字段的表时出现模块错误

Question

我正在使用 postgresql_query 模块从 ansible playbook 查询 postgres 数据库中的表：

- name: Query table
  postgresql_query:
    db: "db"
    login_host: "host"
    login_user: "user"
    login_password: "pass"
    query: "SELECT * FROM test WHERE col1 = 'test_col'"

测试表：

CREATE TABLE test (
  col1 VARCHAR(75),
  col2 VARCHAR(75),
  col3 NUMERIC,
  PRIMARY KEY (col1, col2));
)

当 col3 中没有数据时，Playbook 运行成功，但是当我查询 col3 中具有某些值的行时，出现以下错误：

The full traceback is:
Traceback (most recent call last):
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 102, in <module>
    _ansiballz_main()
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 176, in run_module
    fname, loader, pkg_name)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 82, in _run_module_code
    mod_name, mod_fname, mod_loader, pkg_name)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2072, in exit_json
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2065, in _return_formatted
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 418, in remove_values
  File "/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 401, in _remove_values_conditions
TypeError: Value of unknown type: <class 'decimal.Decimal'>, 1001

fatal: [localhost]: FAILED! => {
    "changed": false, 
    "module_stderr": "Traceback (most recent call last):\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 102, in <module>\n    _ansiballz_main()\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 94, in _ansiballz_main\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n  File \"/Users/ss/.ansible/tmp/ansible-tmp-1593526567.54-188577479536469/AnsiballZ_postgresql_query.py\", line 40, in invoke_module\n    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 176, in run_module\n    fname, loader, pkg_name)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 82, in _run_module_code\n    mod_name, mod_fname, mod_loader, pkg_name)\n  File \"/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py\", line 72, in _run_code\n    exec code in run_globals\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2072, in exit_json\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2065, in _return_formatted\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 418, in remove_values\n  File \"/var/folders/w7/fxgqd7615jj_j8_043rw00ww0000gn/T/ansible_postgresql_query_payload_khzSMG/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 401, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'decimal.Decimal'>, 1001\n", 
    "module_stdout": "", 
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", 
    "rc": 1
}

我的本地环境详细信息：

  ansible 2.9.1
  config file = /Users/ss/Work/doaa/git/Server-DECOM/ansible.cfg
  configured module search path = [u'/Users/ss/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /Library/Python/2.7/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 2.7.10 (default, Feb 22 2019, 21:55:15) [GCC 4.2.1 Compatible Apple LLVM 10.0.1 (clang-1001.0.37.14)]

如何解决此错误？这是ansible中的错误吗？

标签： pythonansible

Answer 1

根据一个相关的问题，psycopg2包装中的东西Decimal不能保证在float. 由于 ansible（无论好坏）只是将行强制转换为 adict，因此它无法将结果序列化为 JSON，因为DecimalJSON 编码器无法理解。

如果您不需要它（也就是说，SELECT *这只是习惯），您必须通过忽略该列来满足 ansible 的一半，或者在查询返回之前将其显式转换到服务器端

- name: Query table
  postgresql_query:
    db: "db"
    login_host: "host"
    login_user: "user"
    login_password: "pass"
    query: "SELECT col1, col2, CAST(col3 AS TEXT) FROM test WHERE col1 = 'test_col'"

CAST只是这个答案的一个简短示例，to_char提供了对该列字符串结果表示的更多控制