Running MySQL scripts (.sql files) from python

This should have been more obvious. All I wanted was to run a simple script e.g. myscript.sql in MySQL from python in Windows.

Some things worked fine from the DOS prompt but failed from within python (and RUN for that matter).

Here is the answer in the form of a simple function (NB to get your indentation right !):

def run_sql_script(scriptname):
    "Run a script in MySQL"
    import subprocess
    import time
    #this next line is too long for this blog but you will need it on one line to run
    args = "\"C:\\Program Files\\MySQL\\MySQL Server 5.0\\bin\\mysql.exe\" 
        -h%s -u%s -p%s --database=databasename < C:/Projects/projectname/3_scripts/%s" 
        % (DB_HOST, DB_USER, DB_PWD, scriptname)
    #print args
    child = subprocess.Popen(args=args, shell=True, executable="C:\\windows\\system32\\cmd.exe")
    #need to check whether finished or not every so often
    i = True
    elapsed = 0
    while i == True:
        time.sleep(10)
        elapsed = elapsed + 10
        if child.poll() == None:
            elapsed_mins = float(elapsed)/60
            print "%.2f minutes elapsed running %s" % (elapsed_mins, scriptname)
        else:
            print "Finished running script " + scriptname
            i = False

Key points: can't use call, must use full Popen and explicitly name the shell (and use it!).

NB scripts can run for a long time e.g. 30 minutes so it is a good idea to make the function keep the user informed.

If you want to kill it, open Task Manager and kill mysql.exe.

In Windows there are apparently some horrible compromises to be made when doing some simple things. See ...adventures-in-python-launching-subprocesses/

There is an alternative approach but it doesn't seem to ever end the subprocess:

#args = "cmd /k \"C:\\Program Files\\MySQL\\MySQL Server 5.0\\bin\\mysql.exe\" -h%s -u%s -p%s --database=databasename < C:\\Projects\\projectname\\test.sql" % (DB_HOST, DB_USER, DB_PWD) print args #http://www.realtechnews.com/posts/2777 re: the /k !!!!!