What is the best practice for performing a SQL query inside of an Ansible playbook task?
I know that Ansible provides some builtin MySQL modules but they only allow you to create databases, tables inside of databases, and users. They don’t allow you to actually do a query.
There’s a mysql_query.py library module (https://github.com/teemops/ansible-modules/blob/master/database/mysql_query.py) written back in 2016 that worked up to the point until MySQL required you to authenticate to a UNIX socket rather than a TCP/IP socket.
There’s a newer mysql_query.py library module (https://github.com/zauberpony/ansible-mysql-query/blob/master/library/mysql_query.py) that does seem to handle UNIX socket authentication but the manner of expressing the queries is completely different (and non-intuitive to my eyes).
Someone pointed to an obscure use of the mysql_db module (https://docs.ansible.com/ansible/latest/modules/mysql_db_module.html - see the last import example) do “run a query” but you need to have an external Jinja2 file for each query and I’d prefer something embedded, if possible.
Then there’s this mechanism (https://stackoverflow.com/questions/30605950/running-a-select-query-with-an-ansible-task) which I haven’t been able to test yet.
So what do people do? How do you handle this?
What’s best practice?
I would appreciate it if you would provide examples.
Thank you!