Using ansible to call python script with subprocess module to run sqlplus script, Ansible shows missing sqlplus...?

[brya...@santecapital.com]

Hello all,

I am using ansible to deploy and provision some instances then runs a python script at the very end. The python script uses the subprocess module to call a sql script and generate flat text files. When I directly ssh into the instances and run remotely, everything works. However when I use ansible to call the python script it looks like commands don't get passed to the Popen construct OR sqlplus is not installed??? Which is weird cause it definitely is installed. This may be a more python oriented question but since I only have the issue when using Ansible to run the python program... I'm posting here. 

Ansible: 

ansible webservers -i inventory --vault-password-file ~/.vault_pass.txt -u ec2-user -m command -a "python script.py chdir=dir/dir1/dir2"

Python:

connect_string=username/password@connectionObject

sql_command=@sql_script.sql

def run_sql_query(sql_command, connect_string):

  """

  Run sqlCommand and return query result and error Message

  """

  session = Popen(['sqlplus', '-S', connect_string], stdin=PIPE, stdout=PIPE, stderr=PIPE)

  session.stdin.write(sql_command)

  return session.communicate()

Error:

  File "/usr/lib64/python2.7/subprocess.py", line 710, in __init__

    errread, errwrite)

  File "/usr/lib64/python2.7/subprocess.py", line 1335, in _execute_child

    raise child_exception

OSError: [Errno 2] No such file or directory

A closer look shows that one of the following is happening:

1) /bin/bash: sqlplus: command not found

 

HOW???

[brya...@santecapital.com]

Environment variables needed to be set for sqlplus + ansible interaction

using shell module with executable /bin/bash and setting specific environment variables (without referencing other env vars) fixed this. If anyone else uses ansible with sqlplus, here's a better play for you

- name: Run python script

  shell: python script.py

    chdir=dir/dir1/dir2

    executable=/bin/bash

  register: script_result

  environment:

    ORACLE_HOME: /usr/lib/oracle/11.2/client64

    PATH: "/usr/lib/oracle/11.2/client64/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/opt/aws/bin:/home/ec2-user/.local/bin:/home/ec2-user/bin"

    LD_LIBRARY_PATH: "/usr/lib/oracle/11.2/client64/lib"

  tags:

    - script

[rup]

Without looking too closely at your code/error I ran into what may be a related issue.  It's because the shell is not sourcing the profiles of the SSH user.  I had to update my shell command to source my bashrc and primarily my profile first before executing my command like so:

shell: . /path to home/.bashrc && . /path to profile/profile && [command to run here]

Hope that helps, it was a very hard journey to figure this out for my installation.

Kim