community / community.mysql / 3.9.0 / module / mysql_query Run MySQL queries | "added in version" 0.1.0 of community.mysql" Authors: Andrew Klychkov (@Andersson007)community.mysql.mysql_query (3.9.0) — module
Install with ansible-galaxy collection install community.mysql:==3.9.0
collections: - name: community.mysql version: 3.9.0
Runs 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