New module postgresql_query

[Andersson007]

SUMMARY

• Runs arbitraty PostgreSQL queries.
• Can run queries from SQL script files.

ISSUE TYPE

• New Module Pull Request

EXAMPLES

- name: Simple select query to acme db
  postgresql_query:
    db: acme
    query: SELECT version()

- name: Select query to db acme with positional arguments and non-default credentials
  postgresql_query:
    db: acme
    login_user: django
    login_password: mysecretpass
    query: SELECT * FROM acme WHERE id = %s AND story = %s
    positional_args:
    - 1
    - test

- name: Select query to test_db with named_args
  postgresql_query:
    db: test_db
    query: SELECT * FROM test WHERE id = %(id_val)s AND story = %(story_val)s
    named_args:
      id_val: 1
      story_val: test

- name: Insert query to db test_db
  postgresql_query:
    db: test_db
    query: INSERT INTO test_db (id, story) VALUES (2, 'my_long_story')

- name: Run queries from SQL script
  postgresql_query:
    db: test_db
    path_to_script: /var/lib/pgsql/test.sql
    positional_args:
    - 1

RETURN

query:
    description: Query that was tried to be executed.
    returned: always
    type: str
    sample: 'SELECT * FROM bar'
statusmessage:
    description: Attribute containing the message returned by the command.
    returned: always
    type: str
    sample: 'INSERT 0 1'
query_result:
    description:
    - List of dictionaries in column:value form representing returned rows.
    returned: changed
    type: list
    sample: [{"Column": "Value1"},{"Column": "Value2"}]
rowcount:
    description: Number of affected rows.
    returned: changed
    type: int
    sample: 5

[Andersson007]

+label postgresql
+label database

[ansibot]

The test ansible-test sanity --test pylint [explain] failed with 1 error:

lib/ansible/modules/database/postgresql/postgresql_query.py:208:4: unreachable Unreachable code

click here for bot help

[ansibot]

cc @Dorn- @amenonsen @andytom @jbscalia @kostiantyn-nemchenko @kustodian @matburt @nerzhul @sebasmannem
click here for bot help

[Andersson007]

ready_for_review

[ansibot]

@Dorn- @andytom @b6d @dschep @jbscalia @jensdepuydt @kostiantyn-nemchenko @kustodian @matburt @nerzhul @sebasmannem

As a maintainer of a module in the same namespace this new module has been submitted to, your vote counts for shipits. Please review this module and add shipit if you would like to see it merged.

click here for bot help

[resmo]

looks solid, two minors (no blockers)

[gundalow]

I don't know much about Postgres, though just one minor comment

[gundalow]

Where are you checking this?

In Ansible 2.8 we added ansible/community#346 (comment) which hopefully will give give clearer error messages to users.

[Andersson007]

Good notice! Thank you, fixed

[Andersson007]

@gundalow , is it necessary to use traceback here ?

import traceback

PSYCOPG2_IMP_ERR = None
...
    PSYCOPG2_IMP_ERR = traceback.format_exc()

I'm asking because I've recently heard that it's not necessary.
If we use module.fail_json(msg=missing_required_lib('psycopg2'), exception=PSYCOPG2_IMP_ERR)
the output is a little different:

The full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_postgresql_ping_payload_HnnFhj/__main__.py", line 113, in <module>
    import psycopg2
ImportError: No module named psycopg2

spblnx176 | FAILED! => {
    "changed": false, 
    "invocation": {
        "module_args": {
            "db": null, 
            "login_host": "", 
            "login_password": "", 
            "login_unix_socket": "", 
            "login_user": "postgres", 
            "port": 5432, 
            "ssl_mode": "prefer", 
            "ssl_rootcert": null
        }
    }, 
    "msg": "Failed to import the required Python library (psycopg2) on spblnx176.lan's Python /usr/bin/python. Please read module documentation and install in the appropriate location"
}

module.fail_json(msg=missing_required_lib('psycopg2') without traceback

The full traceback is:
WARNING: The below traceback may *not* be related to the actual failure.
  File "/tmp/ansible_postgresql_ping_payload_y3izFs/__main__.py", line 111, in <module>
    import psycopg2

spblnx176 | FAILED! => {
    "changed": false, 
    "invocation": {
        "module_args": {
            "db": null, 
            "login_host": "", 
            "login_password": "", 
            "login_unix_socket": "", 
            "login_user": "postgres", 
            "port": 5432, 
            "ssl_mode": "prefer", 
            "ssl_rootcert": null
        }
    }, 
    "msg": "Failed to import the required Python library (psycopg2) on spblnx176.lan's Python /usr/bin/python. Please read module documentation and install in the appropriate location"
}

Should I add traceback to all my new modules including this or not ? (now I use just module.fail_json(msg=missing_required_lib('psycopg2') here)

[gundalow]

I think what you've got it good, thanks

[Dorn-]

Hello, the code is looking good to me.
I'm just wondering if this module make sense, does Ansible is really needed to request a database. Is it really why Ansible have been created ?
The cons for me:

• The module won't be idempotent (at least for select or others cmd drop , truncate ...)
• AFAIK there are no query modules for other databases (mysql mongo ...).
• This is not really the Ansible "mindset"

Pros:

• This is a clean way to request the database, better than using the shell or cmd module

I don't have the answer tbh, this is more a philosophy question. Maybe I'm totally wrong, anyway I would be really glad to read your thought.

Kind regards.

[ansibot]

The test ansible-test sanity --test pylint [explain] failed with 2 errors:

lib/ansible/modules/database/postgresql/postgresql_query.py:237:29: undefined-variable Undefined variable 'missing_required_lib'
lib/ansible/modules/database/postgresql/postgresql_query.py:237:73: undefined-variable Undefined variable 'PSYCOPG2_IMP_ERR'

click here for bot help

[Andersson007]

Hi, @Dorn- . Thank you! There is a lot of things that we can do using shell module but implemented by specific modules. At least, it’s more comfortable. Moreover, imho, it should be implemented for the other database modules too.

[Andersson007]

plus we can use returned values

[ansibot]

The test ansible-test sanity --test pylint [explain] failed with 1 error:

lib/ansible/modules/database/postgresql/postgresql_query.py:237:29: undefined-variable Undefined variable 'missing_required_lib'

click here for bot help

[resmo]

shipit

[Andersson007]

@resmo , thank you for the review!