[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Python script question

In my Python code I want to execute commands like the following two lines:

sql1 = "SELECT f1, f2, f3 from mytab"
exitCode = subprocess.call(sql1,shell=True)

The above commands work. But the problem is that the sql1 string is often very big. I have the unix Shell script equivalent of the above two lines:
bq query .... " `cat q1.sql`"
where s1.sql is a file containing the sql statement and it works with all sql statements with line breaks, etc.

How could I change the Python code above so that it will accept a shell script "cat" like command to get the content of the file for the sql query?
Thanks a lot.
3 Solutions
Pasha KravtsovCommented:
Do you have to use cat? You can do this also:
import subprocess

with open('sqlfile.sql', 'w') as sqlFile:
  exitCode = subprocess.call(sqlFile.read(), shell=True)

Open in new window

It looks like you're using Google's BigQuery. If you are, it has a python api.  Depending on what you're doing with the results of your query run (if, for example, you plan on using it in the rest of your program) it might make sense to go down that route.   Take a look at the python examples in the quickstart:

The subprocess.call(..., shell=True) launches the shell and passes it the argument(s). This is usefull for simple commands, and for the commands interpreted by the shell (i.e. the ones that have not the form of a utility). In the case, usually one string argument (in the form that would be typed-in on concole) is passed. The shell does the parsing (splitting) the arguments.

If you know what program should consume the argument, you usually do not want to use shell=True, and you usually have the arguments separated each from the other. This way you should probably prefer the form subprocess.call(['pgm', arg1, arg2, arg3]) -- that is the list of strings. However, the shell also does searching for the program using the PATH variable.  When not using the shell, the 'pgm' must be full path to the executable.

The `cat q1.sql` just gets the content of the q1.sql file and put it "here". In Python, you can simply read the content of the file to the variable -- as Pasha has shown above, but you should use the 'r' mode (I will use a different syntax with exactly the same functionality):
f = open('q1.sql')
content = f.read()

exit_code = subprocess.call(['/full/path/to/bg', 'query', content])

Open in new window

(I do not know the command, so you should fix it as it should be.)

My advice is to debug the script with some simpler command to get the feeling you do it correctly. Then use some simpler form of bg, and finally use the full set of arguments.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now