Capture output from a mysql select statement using python

Question

Using Python2.4 I want to capture output from a mysql command. One caveat is that I need to pipe the SQL statement using an echo.

echo 'SELECT user FROM mysql.user;' | mysql

I see example using call, os.system, popen but what is best to use for my version of python and capturing the output in a tuple.

Thanks

• Ignacio Vazquez-Abrams: And you're doing this via the CLI tool instead of DB-API why again?
• Clutch: I will be executing random CLI commands not just this one and didn't want to bother with any DB module. This string would probably be the most complected.
• Mike Graham: Using a library like oursql for this would be simpler, more direct, more secure, and more robust. It will not prevent you from using subprocesses for tasks for which they are appropriate.

Answer 1

The subprocess module is the most flexible tool for running commands and controlling the input and output. The following runs a command and captures the output as a list of lines:

import subprocess
p = subprocess.Popen(['/bin/bash', '-c', "echo 'select user from mysql.user;' | mysql" ],
 stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
lines = [line for line in p.stdout]

On Windows, bash -c would be replaced with cmd /c.

• Mike Graham: There is no reason to run /bin/bash. If there was a reason to use the shell, you'd simply use shell=True, but in this case there is no reason to use the shell, you can simply pass the query directly to the process. (Which is all small stuff compared to the fact it would be better to use a module like oursql or MySQLdb to begin with.)
• Mike Graham: Also, [line for line in p.stdout] is a pretty silly way to do that when there are options like list(p) and p.communicate().