代码之家 › 专栏 › 技术社区 › HyperioN

查询具有数字字段的表时发生模块错误

ansible python

HyperioN · 技术社区 · 4 年前

我正在使用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中的bug吗?

0 回复 | 直到 4 年前

mdaniel 4 年前

根据 a related question , psycopg2 将物品打包 Decimal 这不能保证在 float 既然可行,无论是好是坏,只要 coerces the row into a dict ,则无法将结果序列化为JSON,因为 十进制的 JSON编码器无法理解。

如果你不需要,你必须通过省略该列来满足ansible的要求(也就是说, SELECT * 只是习惯),或者在查询返回之前将其显式地投射到服务器端

- 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 对该列的字符串结果的表示提供了更多的控制