community.general.mysql_query (0.2.1) — module

Install collection

Install with ansible-galaxy collection install community.general:==0.2.1

Add to requirements.yml

  collections:
    - name: community.general
      version: 0.2.1

Description

Runs arbitrary MySQL queries.

Pay attention, the module does not support check mode! All queries will be executed in autocommit mode.

Requirements

• PyMySQL (Python 2.7 and Python 3.X), or
• MySQLdb (Python 2.x)

Usage examples

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

- name: Simple select query to acme db
  mysql_query:
    login_db: acme
    query: SELECT * FROM orders

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

- name: Select query to db acme with positional arguments
  mysql_query:
    login_db: acme
    query: SELECT * FROM acme WHERE id = %s AND story = %s
    positional_args:
    - 1
    - test

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

- name: Select query to test_db with named_args
  mysql_query:
    login_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

Scanned by Steampunk Spotter

• Success
  Steampunk Spotter scan finished with no errors, warnings or hints.

- name: Run several insert queries against db test_db in single transaction
  mysql_query:
    login_db: test_db
    query:
    - INSERT INTO articles (id, story) VALUES (2, 'my_long_story')
    - INSERT INTO prices (id, price) VALUES (123, '100.00')
    single_transaction: yes

Inputs

    
query:
    description:
    - SQL query to run. Multiple queries can be passed using YAML list syntax.
    elements: str
    required: true
    type: list

ca_cert:
    aliases:
    - ssl_ca
    description:
    - The path to a Certificate Authority (CA) certificate. This option, if used, must
      specify the same certificate as used by the server.
    type: path

login_db:
    description:
    - Name of database to connect to and run queries against.
    type: str

client_key:
    aliases:
    - ssl_key
    description:
    - The path to the client private key.
    type: path

login_host:
    default: localhost
    description:
    - Host running the database.
    - In some cases for local connections the I(login_unix_socket=/path/to/mysqld/socket),
      that is usually C(/var/run/mysqld/mysqld.sock), needs to be used instead of I(login_host=localhost).
    type: str

login_port:
    default: 3306
    description:
    - Port of the MySQL server. Requires I(login_host) be defined as other than localhost
      if login_port is used.
    type: int

login_user:
    description:
    - The username used to authenticate with.
    type: str

named_args:
    description:
    - Dictionary of key-value arguments to pass to the query.
    - Mutually exclusive with I(positional_args).
    type: dict

client_cert:
    aliases:
    - ssl_cert
    description:
    - The path to a client public key certificate.
    type: path

config_file:
    default: ~/.my.cnf
    description:
    - Specify a config file from which user and password are to be read.
    type: path

login_password:
    description:
    - The password used to authenticate with.
    type: str

connect_timeout:
    default: 30
    description:
    - The connection timeout when connecting to the MySQL server.
    type: int

positional_args:
    description:
    - List of values to be passed as positional arguments to the query.
    - Mutually exclusive with I(named_args).
    type: list

login_unix_socket:
    description:
    - The path to a Unix domain socket for local connections.
    type: str

single_transaction:
    default: false
    description:
    - Where passed queries run in a single transaction (C(yes)) or commit them one-by-one
      (C(no)).
    type: bool

Outputs

executed_queries:
  description: List of executed queries.
  returned: always
  sample:
  - SELECT * FROM bar
  - UPDATE bar SET id = 1 WHERE id = 2
  type: list
query_result:
  description:
  - List of lists (sublist for each query) containing dictionaries in column:value
    form representing returned rows.
  returned: changed
  sample:
  - - Column: Value1
    - Column: Value2
  - - ID: 1
    - ID: 2
  type: list
rowcount:
  description: Number of affected rows for each subquery.
  returned: changed
  sample:
  - 5
  - 1
  type: list