Python生成动态Ansible Playbook时JSON数据无法正确填充问题

我通过Python脚本从MySQL获取数据，结合Jinja2模板生成动态Ansible Playbook。已确认能从数据库正确拉取JSON格式的install/uninstall数据，但生成的Playbook无法正确填充install字段中的name与package_name等值。

代码片段 #

query = "SELECT vm_id, install, uninstall FROM vm_released_applications"
cursor.execute(query)
data = cursor.fetchall()

cursor.close()
connection.close()

return data

def create_dynamic_playbook(template_file, output_file, context):
    env = Environment(loader=FileSystemLoader(searchpath="./"))
    template = env.get_template(template_file)
    playbook_content = template.render(context)
    
    with open(output_file, 'w') as file:
        file.write(playbook_content)

def main():
    data = fetch_data_from_mysql()
    
    for record in data:
        vm_id = record['vm_id']
        install = json.loads(record['install']) if record['install'] else []
        uninstall = json.loads(record['uninstall']) if record['uninstall'] else []

        # Print statements to verify the JSON parsing
        print(f"VM ID: {vm_id}")
        print(f"Install Data: {json.dumps(install, indent=4)}")
        print(f"Uninstall Data: {json.dumps(uninstall, indent=4)}")   
        
        context = {
            'vm_id': vm_id,
            'install': install,
            'uninstall': uninstall
        }

        playbook_filename = f'playbook_vm_{vm_id}.yml'
        create_dynamic_playbook('playbook_template.yml.j2', playbook_filename, context)
        print(f"Playbook {playbook_filename} created successfully.")

if __name__ == "__main__":
    main()

JSON解析输出 #

VM ID: 43
Install Data: [
    "{\"id\":4,\"name\":\"Google Chrome\",\"package_name\":\"googlechrome\",\"version\":\"\"}"
]
Uninstall Data: []
Playbook playbook_vm_43.yml created successfully.

数据库示例数据 #

id;vm_id;install;uninstall;created_at;updated_at
2;52;["{\"id\":4,\"name\":\"Google Chrome\",\"package_name\":\"googlechrome\",\"version\":\"\"}"];;2024-07-10 08:03:29;2024-07-10 08:03:29

Jinja2模板 #

---
- name: Playbook for VM {{ vm_id }}
  hosts: all
  become: true
  tasks:
{% for item in install %}
    - name: Install {{ item.name }}
      win_chocolatey:
        name: "{{ item.package_name }}"
        state: present
{% endfor %}
{% for item in uninstall %}
    - name: Uninstall {{ item.name }}
      win_chocolatey:
        name: "{{ item.package_name }}"
        state: absent
{% endfor %}

期望的Playbook输出 #

---
- name: Playbook for VM 52
  hosts: all
  become: true
  tasks:

    - name: Install Google Chrome
      win_chocolatey:
        name: "googlechrome"
        state: present

核心问题是：数据库中install字段存储的是嵌套JSON结构——外层是JSON数组，数组内的元素又是转义后的JSON字符串。当前代码仅解析了外层数组，但数组内的字符串未被解析为字典，导致Jinja2无法通过item.name访问属性。

修改main函数中install和uninstall的解析逻辑，对数组内的每个元素再执行一次JSON解析：

def main():
    data = fetch_data_from_mysql()
    
    for record in data:
        vm_id = record['vm_id']
        # 解析外层数组后，逐个解析内部的JSON字符串
        install = [json.loads(item) for item in json.loads(record['install'])] if record['install'] else []
        uninstall = [json.loads(item) for item in json.loads(record['uninstall'])] if record['uninstall'] else []

        # Print statements to verify the JSON parsing
        print(f"VM ID: {vm_id}")
        print(f"Install Data: {json.dumps(install, indent=4)}")
        print(f"Uninstall Data: {json.dumps(uninstall, indent=4)}")   
        
        context = {
            'vm_id': vm_id,
            'install': install,
            'uninstall': uninstall
        }

        playbook_filename = f'playbook_vm_{vm_id}.yml'
        create_dynamic_playbook('playbook_template.yml.j2', playbook_filename, context)
        print(f"Playbook {playbook_filename} created successfully.")

修改后，install会被解析为字典数组，Jinja2模板就能正确访问item.name和item.package_name，生成符合预期的Playbook。

内容的提问来源于stack exchange，提问作者thevirtualnomad