Ansible 中的 SQL Server 数据库查询

2021-11-11 00:00:00 ansible sql-server ansible-inventory

我想知道社区中是否有人使用 Ansible 操作过 Microsoft SQL Server 数据库?

I would like to find out if anyone in the community has used Ansible to manipulate a Microsoft SQL Server database?

我们想向我们用 Ansible 编写的环境配置脚本添加一个任务，该脚本将 INSERT 行插入到 SQL Server 表中.这些行将具有在我们的变量文件中指定的参数.

We want to add a task to our environment configuration scripts written in Ansible that will INSERT rows into a SQL Server table. The rows will have parameters that are specified in our variables files.

我一直无法找到实现此目的的特定 Ansible 模块，所以想听听是否有人在其他方面取得了成功?

I have been unable to locate a specific Ansible module for achieving this so would like to hear if anyone has has success in some other way?

推荐答案

为了完整起见，这里是 Ansible to SELECT & 中使用的解决方案的语法.插入 Microsoft SQL Server:

For completeness here is the syntax of the solution used in Ansible to SELECT & INSERT for Microsoft SQL Server:

插入

- name: 'insert row to SQL server DB'
  win_shell: "invoke-sqlcmd -username \"{{db_user}}\" -password \"{{db_pass}}\" -Query \"INSERT INTO Addresses (DoorNum,Street,Town,PostCode) VALUES ({{ item.doornum }},'{{ item.street }}','{{ item.town }}''{{ item.postcode }}')\""

选择

- name: 'select from SQL server DB'
  win_shell: "invoke-sqlcmd -username \"{{db_user}}\" -password \"{{db_pass}}\" -Query \"SELECT ID FROM Addresses WHERE PostCode = '{{ item.postcode }}'\" | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json"
  register: response

- set_fact:
    ids: "{{ response.stdout|from_json}}"

- debug:
    var: ids

相关文章