Using subprocess to get output

Question

Using the subprocess module how do I get the following command to work?

isql -v -b -d, DSN_NAME "DOMAIN\username" password <<< 
"SELECT column_name, data_type 
 FROM database_name.information_schema.columns 
 WHERE table_name = 'some_table';"

This command works perfectly when I run it in a bash shell but I can't get it to work when running from within Python. I'm trying to do this from within Python because I need to be able to modify the query and get different result sets back and then process them in Python. I can't use one of the nice Python database connectors for various reasons which leaves me trying to pipe output from isql.

My code currently looks similar to the following:

bash_command = '''
               isql -v -b -d, DSN_NAME "DOMAIN\username" password <<< 
               "SELECT column_name, data_type 
                FROM database_name.information_schema.columns 
                WHERE table_name = 'some_table';" 
               '''
process = subprocess.Popen(bash_command,
                           shell=True,
                           stdout=subprocess.PIPE,
                           stderr=subprocess.PIPE)
output, error = process.communicate()

However I have tried lots of variations:

Using the entire command as a string, or as a list of strings.
Using check_output vs Popen.
Using communicate() to try and send the query to the isql command or having the query be part of the command string using a heredoc.
Using shell = True or not.
Specifying /bin/bash or using the default /bin/sh.
Lots of different quoting and escaping patterns.

And pretty much every permutation of the above.

In no case do I receive the output of the query that I'm looking for. I'm pretty sure that the command isn't being sent to the shell as is but I can't tell what is being sent to the shell.

I feel like this should be pretty simple, send a command to the shell and get the output back, but I just can't make it work. I can't even see what command is being sent to the shell, even using pdb.

Have you tested your subprocess on simple commands, like ls, whoami, etc? — Sergius
– Sergius, Commented Sep 10, 2015 at 20:10
Okay. I now have working code. I think it was some combination of using shlex, which I hadn't been before, specifying 'input=' before my query string in the communicate() function and using a raw string with a single backslash as opposed to a plain string with a double backslash. Thank you all for your help. — Gregory Arenius
– Gregory Arenius, Commented Sep 10, 2015 at 21:10

jfs · Accepted Answer · 2015-09-11 15:02:54Z

shell=True makes subprocess use /bin/sh by default. <<< "here-string" is a bash-ism; pass executable='/bin/bash':

>>> import subprocess
>>> subprocess.call(u'cat <<< "\u0061"', shell=True)
/bin/sh: 1: Syntax error: redirection unexpected
2
>>> subprocess.call(u'cat <<< "\u0061"', shell=True, executable='/bin/bash')
a
0

You should also use raw-string literals to avoid escaping backslashes: "\\u0061" == r"\u0061" != u"\u0061":

>>> subprocess.call(r'cat <<< "\u0061"', shell=True, executable='/bin/bash')
\u0061
0

Though you don't need shell=True here. You could pass the input as a string using process.communicate(input=input_string):

>>> process = subprocess.Popen(['cat'], stdin=subprocess.PIPE, stdout=subprocess.PIPE)
>>> process.communicate(br"\u0061")
('\\u0061', None)

The result could look like:

#!/usr/bin/env python
import shlex
from subprocess import Popen, PIPE

cmd = shlex.split(r'isql -v -b -d, DSN_NAME "DOMAIN\username" password')
process = Popen(cmd, stdin=PIPE, stdout=PIPE, stderr=PIPE)
output, errors = process.communicate(
    b"SELECT column_name, data_type "
    b"FROM database_name.information_schema.columns "
    b"WHERE table_name = 'some_table';")

Joe Young · Accepted Answer · 2015-09-10 21:04:52Z

Try giving this a shot:

import shlex
from subprocess import Popen, PIPE, STDOUT

sql_statement = '''"SELECT column_name, data_type 
FROM database_name.information_schema.columns 
WHERE table_name = 'some_table';"'''

isqlcommand = 'isql -v -b -d, DSN_NAME "DOMAIN\username" password'
isqlcommand_args = shlex.split(isqlcommand)
process = Popen(isqlcommand_args, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
output = process.communicate(input=sql_statement)[0]
print output

The idea here is to separate the here-string redirection from the isql command execution. This example will pipe the here-string into the stdin of process via process.communicate(). I'm also using shlex.split() to tokenize the command and its arguments.

Edit: Removed Shell=True after reviewing comment from J.F. Sebastian

@amirouche You may be correct but I could never get check_ouput to function as expected.

Collectives™ on Stack Overflow

Using subprocess to get output

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related