Python subprocess, mysqldump and pipes

I've got a problem trying to build a easy backup/upgrade database script.

The error is in the mysqldump call using subprocess:

cmdL = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb + "|", "gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]
print "%s: backup database %s \n\t[%s]" % (domain, domaindb, ' '.join(cmdL))
total_log.write("%s: backup database %s \n\t[%s] \n" % (domain, domaindb, ' '.join(cmdL)))
p = subprocess.Popen(cmdL, stdout=subprocess.PIPE, stderr=subprocess开发者_运维技巧.STDOUT)

Before that cose, i redirect sys.stdout and sys.stderr to files, in order to have a log system.

In those log, i find the error:

[mysqldump --user=xxxxxx --password=yyyyyyyy database_name | gzip > /home/drush-backup/2010-08-30.15.37/db/database_name.sql] [Error]: mysqldump: Couldn't find table: "|"

Seem that the | character is seen as an mysqldump arguments, instead as a pipe.

Looking into the python subprocess documentation, this is normal, but how can i obtain what i need (call the command mysqldump --user=xxxxxx --password=yyyyyyyy database_name | gzip > /home/drush-backup/2010-08-30.15.37/db/database_name.sql)?

EDIT I just see this example on python docs:

output=`dmesg | grep hda`
==>
p1 = Popen(["dmesg"], stdout=PIPE)
p2 = Popen(["grep", "hda"], stdin=p1.stdout, stdout=PIPE)
output = p2.communicate()[0]

and i've edit my script:

command = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb, "|", "gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]
cmdL1 = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb]
cmdL2 = ["gzip", ">", databases_path + "/" + domaindb + ".sql.gz"]

print "%s: backup database %s \n\t[%s]" % (domain, domaindb, ' '.join(command))
total_log.write("%s: backup database %s \n\t[%s] \n" % (domain, domaindb, ' '.join(command)))

p1 = subprocess.Popen(cmdL1, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
p2 = subprocess.Popen(cmdL2, stdin=p1.stdout, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
cmdError, cmdData = p2.communicate()

now the command variable is just used for convenience in logs.

This go a step next but it stops in the > stream, with this error:

[Error]: gzip: >: No such file or directory
gzip: /path/to/backups/dir/natabase_name.sql.gz: No such file or directory

Obviously, if i try the command in a terminal it works.

I'm not sure of how the pipe will get interpreted. If that's a problem, you can programatically create a pipelilne.

from: http://docs.python.org/library/subprocess.html#replacing-shell-pipeline

p1 = Popen(["dmesg"], stdout=PIPE)
p2 = Popen(["grep", "hda"], stdin=p1.stdout, stdout=PIPE)
output = p2.communicate()[0]

edit

As for file redirection, you can direct stdout to a file..

stdin, stdout and stderr specify the executed programs’ standard input, standard output and standard error file handles, respectively. Valid values are PIPE, an existing file descriptor (a positive integer), an existing file object, and None.

Example:

out_file = open(out_filename, "wb")
gzip_proc = subprocess.Popen("gzip", stdout=out_file)
gzip_proc.communicate()

or if you take Alex's advice and use Python's standard library gzip module, you can do something like this:

import gzip
import subprocess

...
#out_filename = path to gzip file

cmdL1 = ["mysqldump", "--user=" + db_user, "--password=" + db_pass, domaindb]
p1 = subprocess.Popen(cmdL1, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
dump_output = p1.communicate()[0]

f = gzip.open(out_filename, "wb")
f.write(dump_output)
f.close()

With path, user, pswd and dbname given, the following works like a charm:

import gzip
from subprocess import Popen, PIPE

cmd = "mysqldump --user={user} --password={pswd} {dbname}".format(**locals())        
p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE)
with gzip.open(path, "wb") as f:
    f.writelines(p.stdout)

using f as stdout argument in subprocess.Popen() also works but does not compress the data. Before Python 2.7, the with statement does not work, so use f=gzip.open(..) and f.close(). Errors can be read with p.stderr.read(), so if this is not an empty string, you better raise an exception

cmd = "mysql --user={user} --password={pswd} {dbname}".format(**locals())
p = Popen(cmd, shell=True, stdin=PIPE, stdout=PIPE, stderr=PIPE)
with gzip.open(path, "rb") as f:
    p.stdin.write(f.read())
    p.communicate()[0]
    p.stdin.close()
    p_err = p.stderr.read()
if p_err:
    raise Exception('Error restoring database:\n{0}'.format(p_err))

Try subprocess.Popen(' '.join(cmdL), shell=True).

Pipelines (and redirections) are recognized as such and scheduled by the shell, and, by default (on Unix), subprocess avoids using a shell (it's slower and gives you less control) -- you need to explicitly force a shell to be in control, if a pipeline or redirection is what you absolutely must have.

Normally one tries to avoid pipelines (and therefore avoid shell=True and the attendand issues) by doing as much of that as possible in Python (e.g., in your case, with the gzip module of Python's standard library). Of course for this one must carefully separate stdout (which is to be further processed) from stderr, as two separate pipes.