Result SQL to CSV file

[Ludovic-Menard]

Hello all, I would like to execute a SQL request and I want to have the result to a CSV format file. I am looking for informations on Ansible documentation but I don't find. Can you help me ? Thanks Ludo

[changlexc]

Then Ansible for IBM i SQL query related module put the result into a dict of python object. I don't think now it has the ability to directly generate an CSV.

[volmcat]

Bumping, I have been trying to register SQL output from ibmi_sql_query module and dump to csv with no success. Any ideas to how this can be done?

Working with the following 2 tasks, query then build out csv with lineinfile but its not recognozing the registered ptf_output as a variable to pass

• name: Query the PTF info table ibm.power_ibmi.ibmi_sql_query: sql: "SELECT PTF_IDENTIFIER, PTF_IPL_REQUIRED, A.PTF_PRODUCT_DESCRIPTION, A.PTF_LOADED_STATUS, A.PTF_STATUS_TIMESTAMP, A.PTF_IPL_ACTION FROM QSYS2.PTF_INFO A Where ptf_creation_timestamp IS NOT NULL and date(ptf_status_timestamp) >= '2023-12-01' ORDER BY A.PTF_STATUS_TIMESTAMP desc" register: ptf_output

  • name: Build out CSV file ansible.builtin.lineinfile: dest: "{{ csv_path }}/{{ csv_filename }}" line: "{{ ptf_output.sql.row }}" create: true state: present

"msg": "The task includes an option with an undefined variable. The error was: 'ansible.utils.unsafe_proxy.AnsibleUnsafeText object' has no attribute 'row'. 'ansible.utils.unsafe_proxy.AnsibleUnsafeText object' has no attribute 'row'\n\nThe error appears to be in '/runner/project/Playbooks/patching/queryptfinfo.yml': line 38, column 5, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n - name: Build out CSV file\n ^ here\n",

[duieolson]

This worked for me. My example takes the results of every host i've run it for and puts it in to a localhost directory to consolidate all the host systems into one csv file. Hope it helps.

samplesqltocsv.txt

[krka01]

Ansible generates output in json format and like @duieolson writes you need to build the csv file.

The example samplesqltocsv.txt that @duieolson uploaded requires you to know and use the column names when the cvs file is built.

I wanted a more dynamic approach. The results from the SQL statement is returned as a list of dicts so you can use the dict2items to split them in key/value pairs and then use the key to build the column header and the values to populate the data section of the csv file.

Edit: Seems like hyphen - signs are treated like bullets even if inserting code using the code button. Also the indent does not seems to work. So I will also upload the code as a attachement. ibmi-sql-to-csv-file.yml.txt

`# ansible-playbook ibmi-sql-to-csv-file.yml -e "variable_host=xxx"

---

• hosts: "{{ variable_host }}" gather_facts: false collections:

  • ibm.power_ibmi

  vars: sql_stmt: "select from table(QSYS2.OBJECT_STATISTICS('QGPL', 'FILE')) where OBJATTRIBUTE = 'SAVF' and OBJSIZE > 500000000;" csv_directory_path: "~/ansible_output" csv_file_name: "test_sql_results.csv" csv_combine_results: True csv_column_header: True

  tasks:

  • name: Execute SQL statement ibm.power_ibmi.ibmi_sql_query: sql: "{{ sql_stmt }}" register: sql_results

  • name: Send message if no recordes is returned ansible.builtin.debug: msg: "No records returned, " when: sql_results.row_count == 0

  • name: End play for host that returned no records
    ansible.builtin.meta: end_host when: sql_results.row_count == 0

  • ansible.builtin.set_fact: column_header: "{{ column_header | default('') + item.key + ',' }}" loop: "{{ sql_results.row|combine|dict2items }}" when: csv_column_header

  • name: Write column header to the combined cvs file ansible.builtin.lineinfile: dest: "{{ csv_directory_path }}/{{ csv_file_name }}" line: "HOST_NAME, {{ column_header[:-1] }}" create: yes when: csv_column_header and csv_combine_results delegate_to: localhost run_once: true

  • name: Write data to the combined cvs file ansible.builtin.lineinfile: dest: "{{ csv_directory_path }}/{{ csv_file_name }}" line: "{{ inventory_hostname }},{{ (item|dict2items|map(attribute='value')|join(',')) }}" create: yes loop: "{{ sql_results.row }}" when: csv_combine_results
    delegate_to: localhost throttle: 1

  • name: Write column header to separate cvs file ansible.builtin.lineinfile: dest: "{{ csv_directory_path }}/{{ inventory_hostname }}.{{ csv_file_name }}" line: "{{ column_header[:-1] }}" create: yes when: csv_column_header and not csv_combine_results delegate_to: localhost

  • name: Write data to separate cvs file ansible.builtin.lineinfile: dest: "{{ csv_directory_path }}/{{ inventory_hostname }}.{{ csv_file_name }}" line: "{{ (item|dict2items|map(attribute='value')|join(',')) }}" create: yes loop: "{{ sql_results.row }}" when: not csv_combine_results
    delegate_to: localhost`