- name: Fetch data from database
  ttafsir.sqlite_utils.run_sql:
    db_path: database.sqlite
    query: "SELECT * FROM emails ORDER BY email_id"
  register: query_1

- debug: var=query_1.rows

- name: Fetch data based on ID
  ttafsir.sqlite_utils.run_sql:
    db_path: database.sqlite
    query: "SELECT * FROM emails WHERE email_id = ?;"
    params: [3]
  register: query_2

- debug: var=query_2.rows

- name: Fetch data based on name and age
  ttafsir.sqlite_utils.run_sql:
    db_path: database.sqlite
    query: |-
      SELECT * FROM emails
      WHERE subject = :subject
      AND email_id = :email_id
    params:
      subject: "Hello World"
      email_id: 1
  register: query_3

- debug: var=query_3.rows

- name: Update data based on ID
  ttafsir.sqlite_utils.run_sql:
    db_path: database.sqlite
    query: "UPDATE emails SET subject = ? WHERE email_id = ?;"
    params: ["Hello World Updated", 1]
  register: update_result

- debug: var=query_3.rows_affected

- name: Create database
  ttafsir.sqlite_utils.create:
    db_path: database.db
    table: emails
    columns: {"email_id": "int", "subject": "str", "body": "str"}
    pk: email_id

- name: Insert single records into database
  ttafsir.sqlite_utils.insert:
    db_path: database.db
    table: emails
    records: {"email_id": 2, "subject": "Hello World 2", "body": "body of the email"}

- name: Insert from json file
  ttafsir.sqlite_utils.insert_json:
    db_path: network.db
    table: interfaces
    file_path: interfaces.json
    flatten: true
    alter: true

---
- hosts: localhost
  gather_facts: no
  vars:
    where_arg: {"subject": "Peek #4"}

  tasks:

    - name: Fetch two columns from SQLite
      debug:
        msg: "{{
          lookup(
            'ttafsir.sqlite_utils.sqlite',
            table='emails',
            db_path=database,
            select='email_id, subject')
        }}"

    - name: Fetch row from SQLite where email subject matches
      debug:
        msg: "{{
          lookup(
            'ttafsir.sqlite_utils.sqlite',
            table='emails',
            db_path='database.sqlite',
            where='subject = :subject',
            where_args=where_arg
          )
        }}"