MySQL database back up using Python

Question

I'm trying to write a python script which backs up the database every midnight. The code i am using is below:

from subprocess import call

call (["mysqldump", "-u", "root", "-p*****", "normalisation", ">", "date_here.sql"])

The first problem i came across is that mysql thinks the ">" is a table when it is not, the query works fine when i run it from the command line (see below)

$ mysqldump -u root -p*** normalisation > date_here.sql
$ ls
backup.py date_here.sql
$

Sencondly, how do i get the script to automatically run everymidnight?
Thirdly, i need the .sql file to be saved as the date of the back up.

Answer 1

use a shell script. there's a million that do this task already online. you can generate the filename using the date command with the right format string, and you can make it run at a scheduled time using cron.

Answer 2

Your command is failing because output redirection is a function of the shell, not mysqldump. Try using Popen instead of call, as follows:

from subprocess import Popen

f = open( "date_here.sql", "w" )
x = Popen( ["mysqldump", "-u", "root", "-p*****", "normalisation"], stdout = f )
x.wait()
f.close()

This will allow you to handle redirecting to stdout within your program.