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