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

**URL:** <https://forum.ansible.com/t/using-ansible-to-call-python-script-with-subprocess-module-to-run-sqlplus-script-ansible-shows-missing-sqlplus/20544>\
**Category:** Ansible Project\
**Created:** [February 11, 2016, 7:04pm UTC](https://forum.ansible.com/t/using-ansible-to-call-python-script-with-subprocess-module-to-run-sqlplus-script-ansible-shows-missing-sqlplus/20544 "2016-02-11T19:04:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![bryan.yu](https://avatars.discourse-cdn.com/v4/letter/b/ed655f/32.png) [@bryan.yu](https://forum.ansible.com/u/bryan.yu)\
**Post date:** [February 11, 2016, 7:04pm UTC](https://forum.ansible.com/t/using-ansible-to-call-python-script-with-subprocess-module-to-run-sqlplus-script-ansible-shows-missing-sqlplus/20544/1 "2016-02-11T19:04:47Z")

</div>

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](https://docs.python.org/2/library/subprocess.html#popen-constructor) 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???

---

<div class="post-metadata">

**Author:** ![bryan.yu](https://avatars.discourse-cdn.com/v4/letter/b/ed655f/32.png) [@bryan.yu](https://forum.ansible.com/u/bryan.yu)\
**Post date:** [February 11, 2016, 8:26pm UTC](https://forum.ansible.com/t/using-ansible-to-call-python-script-with-subprocess-module-to-run-sqlplus-script-ansible-shows-missing-sqlplus/20544/2 "2016-02-11T20:26:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rup](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rup](https://forum.ansible.com/u/rup)\
**Post date:** [February 12, 2016, 4:17pm UTC](https://forum.ansible.com/t/using-ansible-to-call-python-script-with-subprocess-module-to-run-sqlplus-script-ansible-shows-missing-sqlplus/20544/3 "2016-02-12T16:17:46Z")

</div>

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