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

Archives Ansible Project
1

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???

2

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
3

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