Try SpotterRun MySQL queries
| "added in version" 0.1.0 of community.mysql"
Authors: Andrew Klychkov (@Andersson007)
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections:
- name: community.mysql
version: 3.9.0Runs arbitrary MySQL queries.
Pay attention, the module does not support check mode! All queries will be executed in autocommit mode.
To run SQL queries from a file, use M(community.mysql.mysql_db) module.
# If you encounter the "Please explicitly state intended protocol" error,
# use the login_unix_socket argument
- name: Simple select query to acme db
community.mysql.mysql_query:
login_db: acme
query: SELECT * FROM orders
login_unix_socket: /run/mysqld/mysqld.sock
- name: Select query to db acme with positional arguments
community.mysql.mysql_query:
login_db: acme
query: SELECT * FROM acme WHERE id = %s AND story = %s
positional_args:
- 1
- test
- name: Select query to test_db with named_args
community.mysql.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
- name: Run several insert queries against db test_db in single transaction
community.mysql.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: true
query:
description:
- SQL query to run. Multiple queries can be passed using YAML list syntax.
- Must be a string or YAML list containing strings.
- If you use I(named_args) or I(positional_args) any C(%) will be interpreted as a
formatting character. All literal C(%) characters in the query should be escaped
as C(%%).
- Note that if you use the C(IF EXISTS/IF NOT EXISTS) clauses in your query and C(mysqlclient)
or C(PyMySQL 0.10.0+) connectors, the module will report that the state has been
changed even if it has not. If it is important in your workflow, use the C(PyMySQL
0.9.3) connector instead.
required: true
type: raw
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.
- The default config file, C(~/.my.cnf), if it exists, will be read, even if I(config_file)
is not specified.
- The default config file, C(~/.my.cnf), if it exists, must contain a C([client])
section as a MySQL connector requirement.
- To prevent the default config file from being read, set I(config_file) to be an
empty string.
type: path
check_hostname:
description:
- Whether to validate the server host name when an SSL connection is required. Corresponds
to MySQL CLIs C(--ssl) switch.
- Setting this to C(false) disables hostname verification. Use with caution.
- Requires pymysql >= 0.7.11.
- This option has no effect on MySQLdb.
type: bool
version_added: 1.1.0
version_added_collection: community.mysql
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).
elements: raw
type: list
login_unix_socket:
description:
- The path to a Unix domain socket for local connections.
- Use this parameter to avoid the C(Please explicitly state intended protocol) error.
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
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