ansible insert rows into mysql db

Question

I am novice to ansible and trying out few use cases to grab more knowledge. I am trying out one use case where I plan to connect to MYSQL db and run one select query. What I am trying is shown in the playbook below. This code works fine. Now I want to run one insert query to insert some rows from .csv file. How can I achieve this?

ansible playbook:

---
- hosts: localhost
  gather_facts: false
  #become: true

  tasks:
    - name: Excute query to db  with positional arguments
      community.mysql.mysql_query:
        login_host: sqltest-lb-fm-in.dbaas.domain.com
        login_user: devops_baseline_db
        login_password: *********
        login_port: 3307
        login_db: mydb
        ca_cert : mydomain-SHA256-Root-CA.crt
        query: SELECT * FROM Inventory
      register: output
    - debug:
        msg: "{{ output }}"

example of /tmp/report.csv content

Date,Hostname/IP,OS-Version,Package-Name,Pre-installed-Package-Status,Current-Installed-Version,Post-installed-Package-Status,log-loc
2022-12-15,10.109.20.12,12.5,curl,up-to-date,7.60.0-11.49.1,up-to-date,http://hostname/log_dir/
2022-12-15,10.109.20.12,12.5,libcurl4-32bit,up-to-date,7.60.0-11.49.1,up-to-date,http://hostname/log_dir/
2022-12-15,10.109.20.12,12.5,libcurl4,up-to-date,7.60.0-11.49.1,up-to-date,http://hostname/log_dir/
2022-12-15,10.109.20.12,12.5,libtiff5,up-to-date,4.0.9-44.56.1,up-to-date,http://hostname/log_dir/

Answer 1

You have Three ways

1. using ansible mysqldb module (Not working but expected to work) :

- name: Copy database dump file
  ansible.builtin.copy:
    src: /tmp/report.csv
    dest: /tmp

- name: Restore database
  community.mysql.mysql_db:
    name: my_db
    state: import
    target: /tmp/report.csv

2. Using Commands:

- name: Copy database dump file
  ansible.builtin.copy:
    src: /tmp/report.csv
    dest: /tmp

- name: Excute query to db  with positional arguments
  community.mysql.mysql_query:
    login_host: sqltest-lb-fm-in.dbaas.domain.com
    login_user: devops_baseline_db
    login_password: *********
    login_port: 3307
    login_db: mydb
    ca_cert : mydomain-SHA256-Root-CA.crt
    query: |
      LOAD DATA LOCAL INFILE  '/tmp/report.csv'
      INTO TABLE Inventory
      FIELDS TERMINATED BY ',' 
      ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
      IGNORE 1 ROWS;

3. Looping over lines:

- name: Read CSV File
  community.general.read_csv:
    path: /tmp/report.csv
  register: report
  delegate_to: localhost

- name: Excute query to db  with positional arguments
  community.mysql.mysql_query:
    login_host: sqltest-lb-fm-in.dbaas.domain.com
    login_user: devops_baseline_db
    login_password: *********
    login_port: 3307
    login_db: mydb
    ca_cert : mydomain-SHA256-Root-CA.crt
    query: |
      INSERT INTO Inventory (Date,Hostname,OS-Version,Package-Name,Pre-installed-Package-Status,Current-Installed-Version,Post-installed-Package-Status,log-loc)
      VALUES ({{ item.Date }},{{ item.Hostname }},{{ item.OS_Version }}, {{ item.Package_Name }},{{ item.Pre_installed_Package_Status }},{{ item.Current_Installed_Version }},{{ item.Post_installed_Package_Status }},{{ item.log_loc }});
  loop: "{{ report }}"

I know it's a bit dirty but you can try it. ;)

Note that I have removed/changed some special characters or you can find some workaround for that