Ansible create module connect MSSQL

[Karther]

Hello,

I create module in file /library/module.py with this content :

#!/usr/bin/python

# -*- coding: utf-8 -*-

from ansible.module_utils.basic import AnsibleModule

from ansible.module_utils.parsing.convert_bool import BOOLEANS

DOCUMENTATION = '''

---

module: mssql_query

author: Mohamed El Morabity

short_description: Run a SQL query on a Microsoft SQL Server database.

description:

  - Run a SQL query on a Microsoft SQL Server database.

options:

  login_user:

    description:

      - The username used to authenticate with.

    required: false

    default: ''

  login_password:

    description:

      - The password used to authenticate with.

    required: false

    default: ''

  login_host:

    description:

      - The host running the database.

    required: false

    default: ''

  port:

    description:

      - The database port to connect to.

    type: int

    required: false

    default: 1433

    aliases: ['login_port']

  db:

    description:

      - The name of the database.

    required: false

    default: ''

  query:

    description:

      - The SQL query to run.

    required: True

  autocommit:

    description:

      - Automatically commit the change only if the import succeed. Sometimes it is necessary to use autocommit=true, since some content can't be changed within a transaction.

    required: false

    default: false

  tds_version:

    description:

      - The TDS protocol version to use.

    required: false

    default: 7.1

  as_dict:

    description:

      - If true, return results as a list of dictionaries.

    type: bool

    required: false

    default: false

notes:

  - Requires the pymssql Python package on the remote host.

requirements: ['pymssql']

'''

EXAMPLES = '''

# Run SQL query

- local_action:

    module: mssql_query

    db: mydatabase

    query: SELECT * FROM myschema.mytable

'''

try:

    import pymssql

    HAS_LIB = True

except ImportError:

    HAS_LIB = False

def run_query(module, db_connection, as_dict):

    """Run a SQL query."""

    query = module.params['query']

    result = []

    try:

        cursor = db_connection.cursor(as_dict=as_dict)

        cursor.execute(query)

        try:

            result = cursor.fetchall()

        except pymssql.OperationalError as ex:

            if 'Statement not executed or executed statement has no resultset' in ex.args:

                pass

        changed = cursor.rowcount != 0

        db_connection.commit()

        cursor.close()

    except pymssql.ColumnsWithoutNamesError as ex:

        # If no column name in result, re-run without as dict

        return run_query(module, db_connection, False)

    except pymssql.Error as ex:

        if ex.args:

            module.fail_json(msg='Unable to execute query: {}'.format(ex[1]), errno=ex[0])

        module.fail_json(msg='Unable to execute query: {}'.format(ex))

    finally:

        if db_connection is not None:

            db_connection.close()

    return (changed, result, cursor.rowcount)

def main():

    """Main execution path."""

    module = AnsibleModule(

        argument_spec={

            'login_host': {'type': 'str', 'default': ''},

            'port': {'type': 'int', 'default': 1433, 'aliases': ['login_port']},

            'login_user': {'type': 'str', 'default': ''},

            'login_password': {'type': 'str', 'default': '', 'no_log': True},

            'query': {'required': True, 'type': 'str'},

            'db': {'type': 'str', 'default': ''},

            'autocommit': {'type': 'bool', 'choices': BOOLEANS, 'default': False},

            'tds_version': {'type': 'str', 'default': '7.1'},

            'as_dict': {'type': 'bool', 'choices': BOOLEANS, 'default': False},

        }

    )

    if not HAS_LIB:

        module.fail_json(msg='pymssql is required for this module')

    try:

        db_connection = pymssql.connect(host=module.params['login_host'],

                                        port=str(module.params['port']),

                                        user=module.params['login_user'],

                                        password=module.params['login_password'],

                                        database=module.params['db'],

                                        tds_version=module.params['tds_version'])

        db_connection.autocommit(module.params['autocommit'])

    except pymssql.Error as ex:

        module.fail_json(msg='Unable to connect to database: {}'.format(ex))

    (changed, result, rowcount) = run_query(module, db_connection,

                                            as_dict=module.params['as_dict'])

    module.exit_json(changed=changed, result=result, rowcount=rowcount)

if __name__ == '__main__':

    main()

#########################################################################

And in my task i have this content :

- name: "Execut my module on MSSQL"

  module: mssql_query

    login_host: my_database_name

    port: 1433

    login_user: sa_ofs

    login_password: my_password

    query: SELECT * FROM dbo.my_table

    db: my_database

    autocommit: true

    tds_version: 7.1

    as_dict: false

  delegate_to: my_database_name

The module is here : https://github.com/melmorabity/ansible-mssql-query/blob/master/mssql_query.py

And when i use this task, ansible tell me that there is problem syntaxe ... 

I don't understand whyyy ????

One expert can help me please !! :)

Regards,

Karther

[J Hawkesworth]

Worth running with -vvvvv to see if there is any more information to help you debug the problem.

Try this syntax as my guess is ansible is looking for a module called 'module' instead of your custome 'mssql_query' module

- name: "Execut my module on MSSQL"

  mssql_query:

   login_host: my_database_name

   port: 1433

   login_user: sa_ofs

   login_password: my_password

   query: SELECT * FROM dbo.my_table

   db: my_database

   autocommit: true

   tds_version: 7.1

   as_dict: false

 delegate_to: my_database_name

Hope this helps,

Jon