Craig L Craig L - 1 year ago 175
MySQL Question

python issue with popen and mysql

I'm new to Python, and haven't used Linux in years, so I'm not sure where I'm getting tangled up. I'm trying to use Popen to run sql files in MySQL on Ubuntu.

Here is the relevant code:

command = ['mysql', '-uUSER', '-pPWD','-h192.168.1.132', '--database=dbName', '<', './1477597236_foo.sql' ]
print("command is: "+subprocess.list2cmdline(command))

proc = subprocess.Popen(
command, stderr=subprocess.PIPE, stdout=subprocess.PIPE, cwd='.'

the output from this is the same as if had run 'mysql --help'. The puzzling thing to me is that if i take the command output by subprocess.list2cmdline and run it directly, it runs perfectly. Also, if i replace
'< file.sql'
'-e select * from foo'
, it runs. So, the
and file are causing my problem. I know WHAT is causing the problem, but nothing I've tried so far has fixed it.

tia, Craig

Answer Source

When a redirection or pipe or built-in command is present in the command line, shell=True is required. However, in simple cases like this, shell=True is overkill. There's a much cleaner way in order to avoid shell=True which gives better control on the input file.

  • if the input file doesn't exist, you get an exception before reaching the subprocess, which is easier to handle
  • the process runs without the shell: better portability & performance

the code:

command = ['mysql', '-uUSER', '-pPWD','-h192.168.1.132',  '--database=dbName' ]

with open('./1477597236_foo.sql') as input_file:
    proc = subprocess.Popen(
        command, stdin = input_file, stderr=subprocess.PIPE, stdout=subprocess.PIPE )
    output,error = proc.communicate()

(I added the next line which should be a communicate call: since both stdout & stderr are redirected, it's the only simple way to avoid deadlocks between both output streams)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download