Python subprocess.check_output() for general shell command

I am working on a simple Tkinter GUI for SQL tables. Obviously, I have had to use shell commands, and I have been struggling with the subprocess shell system.

For example, subprocess.check_output('ls') will run ls, but to run ls -l, you need to use subprocess.check_output(['ls', '-l']). I have not found a way to get the output of a more complex command, e.g. cat test.sql | sqlite3 test.db (run sqlite3 on test.db, then list out test.sql at the prompt).

Things I’ve tried

• subprocess.check_output(['cat', 'test.sql', '|', 'sqlite3', 'test.db'])

  Error (running on Python shell):

  cat: '|': No such file or directory
  cat: sqlite3: No such file or directory
  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "/usr/lib/python3.7/subprocess.py", line 395, in check_output
      **kwargs).stdout
    File "/usr/lib/python3.7/subprocess.py", line 487, in run
      output=stdout, stderr=stderr)
  subprocess.CalledProcessError: Command '['cat', 'test.sql', '|', 'sqlite3', 'test.db']' returned non-zero exit status 1.
  

• subprocess.check_output([['cat', 'test.sql'], '|', ['sqlite3', 'test.db']]) (don’t know why I expected this to work)

  Error:

  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "/usr/lib/python3.7/subprocess.py", line 395, in check_output
      **kwargs).stdout
    File "/usr/lib/python3.7/subprocess.py", line 472, in run
      with Popen(*popenargs, **kwargs) as process:
    File "/usr/lib/python3.7/subprocess.py", line 775, in __init__
      restore_signals, start_new_session)
    File "/usr/lib/python3.7/subprocess.py", line 1436, in _execute_child
      executable = os.fsencode(executable)
    File "/usr/lib/python3.7/os.py", line 809, in fsencode
      filename = fspath(filename)  # Does type-checking of `filename`.
  TypeError: expected str, bytes or os.PathLike object, not list
  

• Using bash -c command: subprocess.check_output(['bash', '-c', '"cat test.sql | sqlite3 test.db"'])

  Error:

  bash: cat test.sql | sqlite3 test.db: command not found
  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "/usr/lib/python3.7/subprocess.py", line 395, in check_output
      **kwargs).stdout
    File "/usr/lib/python3.7/subprocess.py", line 487, in run
      output=stdout, stderr=stderr)
  subprocess.CalledProcessError: Command '['bash', '-c', '"cat test.sql | sqlite3 test.db"']' returned non-zero exit status 127.
  

  (Mind you, running bash -c "cat test.sql | sqlite3 test.db" on its own worked perfectly fine.)

I ended up just using the os.system() and os.popen() commands, but people say that using these is not recommended. What should I do?

from subprocess import Popen, PIPE

p1 = Popen(["cat", "test.sql"], stdout=PIPE)
p2 = Popen(["sqlite3", "test.db"], stdin=p1.stdout, stdout=PIPE)
p1.stdout.close()  # Allow p1 to receive a SIGPIPE if p2 exits.
output = p2.communicate()[0]