Vhv0ddlmZmZmZeZdZdZdZddl m Z m Z ddl Z ddl Z dZ ddlZdZd Zd Zd Zed k(reyy#e$re j$ZdZY1wxYw) )absolute_importdivisionprint_functiona module: mssql_script short_description: Execute SQL scripts on a MSSQL database version_added: "4.0.0" description: - Execute SQL scripts on a MSSQL database. extends_documentation_fragment: - community.general.attributes attributes: check_mode: support: partial details: - The script will not be executed in check mode. diff_mode: support: none options: name: description: Database to run script against. aliases: [db] default: '' type: str login_user: description: The username used to authenticate with. type: str login_password: description: The password used to authenticate with. type: str login_host: description: Host running the database. type: str required: true login_port: description: Port of the MSSQL server. Requires O(login_host) be defined as well. default: 1433 type: int script: description: - The SQL script to be executed. - Script can contain multiple SQL statements. Multiple Batches can be separated by V(GO) command. - Each batch must return at least one result set. required: true type: str transaction: description: - If transactional mode is requested, start a transaction and commit the change only if the script succeed. Otherwise, rollback the transaction. - If transactional mode is not requested (default), automatically commit the change. type: bool default: false version_added: 8.4.0 output: description: - With V(default) each row will be returned as a list of values. See RV(query_results). - Output format V(dict) will return dictionary with the column names as keys. See RV(query_results_dict). - V(dict) requires named columns to be returned by each query otherwise an error is thrown. choices: ["dict", "default"] default: 'default' type: str params: description: |- Parameters passed to the script as SQL parameters. (Query V('SELECT %(name\)s"') with V(example: '{"name": "John Doe"}).)'. type: dict notes: - Requires the pymssql Python package on the remote host. For Ubuntu, this is as easy as C(pip install pymssql) (See M(ansible.builtin.pip)). requirements: - pymssql author: - Kris Budde (@kbudde) aY - name: Check DB connection community.general.mssql_script: login_user: "{{ mssql_login_user }}" login_password: "{{ mssql_login_password }}" login_host: "{{ mssql_host }}" login_port: "{{ mssql_port }}" db: master script: "SELECT 1" - name: Query with parameter community.general.mssql_script: login_user: "{{ mssql_login_user }}" login_password: "{{ mssql_login_password }}" login_host: "{{ mssql_host }}" login_port: "{{ mssql_port }}" script: | SELECT name, state_desc FROM sys.databases WHERE name = %(dbname)s params: dbname: msdb register: result_params - assert: that: - result_params.query_results[0][0][0][0] == 'msdb' - result_params.query_results[0][0][0][1] == 'ONLINE' - name: Query within a transaction community.general.mssql_script: login_user: "{{ mssql_login_user }}" login_password: "{{ mssql_login_password }}" login_host: "{{ mssql_host }}" login_port: "{{ mssql_port }}" script: | UPDATE sys.SomeTable SET desc = 'some_table_desc' WHERE name = %(dbname)s UPDATE sys.AnotherTable SET desc = 'another_table_desc' WHERE name = %(dbname)s transaction: true params: dbname: msdb - name: two batches with default output community.general.mssql_script: login_user: "{{ mssql_login_user }}" login_password: "{{ mssql_login_password }}" login_host: "{{ mssql_host }}" login_port: "{{ mssql_port }}" script: | SELECT 'Batch 0 - Select 0' SELECT 'Batch 0 - Select 1' GO SELECT 'Batch 1 - Select 0' register: result_batches - assert: that: - result_batches.query_results | length == 2 # two batch results - result_batches.query_results[0] | length == 2 # two selects in first batch - result_batches.query_results[0][0] | length == 1 # one row in first select - result_batches.query_results[0][0][0] | length == 1 # one column in first row - result_batches.query_results[0][0][0][0] == 'Batch 0 - Select 0' # each row contains a list of values. - name: two batches with dict output community.general.mssql_script: login_user: "{{ mssql_login_user }}" login_password: "{{ mssql_login_password }}" login_host: "{{ mssql_host }}" login_port: "{{ mssql_port }}" output: dict script: | SELECT 'Batch 0 - Select 0' as b0s0 SELECT 'Batch 0 - Select 1' as b0s1 GO SELECT 'Batch 1 - Select 0' as b1s0 register: result_batches_dict - assert: that: - result_batches_dict.query_results_dict | length == 2 # two batch results - result_batches_dict.query_results_dict[0] | length == 2 # two selects in first batch - result_batches_dict.query_results_dict[0][0] | length == 1 # one row in first select - result_batches_dict.query_results_dict[0][0][0]['b0s0'] == 'Batch 0 - Select 0' # column 'b0s0' of first row a query_results: description: List of batches (queries separated by V(GO) keyword). type: list elements: list returned: success and O(output=default) sample: [[[["Batch 0 - Select 0"]], [["Batch 0 - Select 1"]]], [[["Batch 1 - Select 0"]]]] contains: queries: description: - List of result sets of each query. - If a query returns no results, the results of this and all the following queries will not be included in the output. - Use the V(GO) keyword in O(script) to separate queries. type: list elements: list contains: rows: description: List of rows returned by query. type: list elements: list contains: column_value: description: - List of column values. - Any non-standard JSON type is converted to string. type: list example: ["Batch 0 - Select 0"] returned: success, if output is default query_results_dict: description: List of batches (queries separated by V(GO) keyword). type: list elements: list returned: success and O(output=dict) sample: [[[["Batch 0 - Select 0"]], [["Batch 0 - Select 1"]]], [[["Batch 1 - Select 0"]]]] contains: queries: description: - List of result sets of each query. - If a query returns no results, the results of this and all the following queries will not be included in the output. Use V(GO) keyword to separate queries. type: list elements: list contains: rows: description: List of rows returned by query. type: list elements: list contains: column_dict: description: - Dictionary of column names and values. - Any non-standard JSON type is converted to string. type: dict example: {"col_name": "Batch 0 - Select 0"} returned: success, if output is dict ) AnsibleModulemissing_required_libNTFct|SN)str)os r/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/general/plugins/modules/mssql_script.py clean_outputr s q6Mc~ttddgdttdtdtdd tdtd d d g td tdd  }td}t|d}ts |jt dt |j d}|j d}|j d}|j d}|j d}|j d}|j d} |j d} |j d} |} |d k7r|d|} |||jd  tj||| |!} | j} j| d&}| d k(r| jd'}d(}g}g}|jdD]k}|jd)k(r|jj!d*k7r|j#|J|j#dj%|g}mt'|d+kDr |j#dj%|d|d,<|j(r|j*d3i|g}|D]^} j-|| g}|j/}|r$|j#||j/}|r$|j#|`| r| j7t9j:t9j<|t>2}|||<|j*d3i|y#t$rR}d"t|vr(|j\}}|jd#|d$| n|jd% Yd}~d}~wwxYw#t$r}t1|j2d-k(r t|d.k(r|j#gnK| r| j5t1|j2d/t|}|jd3d0||d1|Yd}~d}~wwxYw)4NFdb)requiredaliasesdefaultT)no_log)rinti)typerrdict)rchoices)rbool) name login_userlogin_password login_host login_portscriptoutputparams transaction)changed) argument_specsupports_check_modepymssql)msg exceptionrrrrrr r!r"r#:zHwhen supplying login_user argument, login_password must also be provided)r()userpasswordhostdatabasezUnknown databasezERROR:  zunable to connect, check login_user and login_password are correct, or alternatively check your @sysconfdir@/freetds.conf / ${HOME}/.freetds.conf query_results)as_dictquery_results_dictuGOrr$OperationalErrorz=Statement not executed or executed statement has no resultsetz: z query failed)r(queryerror)r) rr MSSQL_FOUND fail_jsonrPYMSSQL_IMP_ERRr"r'connectcursor Exceptionr args autocommit splitlinesstripupperappendjoinlen check_mode exit_jsonexecutefetchallr__name__rollbackcommitjsonloadsdumpsr ) module_argsresultmodulerrrrrr r! sql_paramsr#login_querystringconnr<eerrnoerrstrquery_results_keyqueries current_batch statementr0r5 qry_resultrows error_msg qry_resultss r run_moduleras! 54&" =64(&UD1T"I /BC fe4 KF! F 1 "1  3 v B|,J]]#34N|,J|,J ]]8 $F ]]8 $Fx(J-- .K"T'1:>."8Z  \ Vn;LWY[ OO O$' T*0GM&&t,  ??  (  ??  " " $ ,   + NN277=1 2M  =Arww}-.F9 "6"M] ] NN5* -J??$D!!$'(   ,]4 **TZZ |LMK +F FvQ V Q 'FFME6   5&!A  B   "U  V Vb ]Q  $66AYY$$R(MMO(,Q(8(8#a&A    \^5 \U[\ ]s9 )MA N%N% N"ANN"% P<.BP77P<ctyr )rar7rr mainrclsLr__main__) __future__rrrr __metaclass__ DOCUMENTATIONEXAMPLESRETURNansible.module_utils.basicrr tracebackrMr:r'r8 ImportError format_excr rarcrJr7rr rnsCB K ZN `7 rK  Kxv zFU*i**,OKsAAA