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 !!!!!

Real Interoperability

A recent Computerworld article “Microsoft loses appeal against EU antitrust ruling” contains some very good news for IT consumers everywhere. OK it took legal force, a massive fine (€497 million), and it was resisted every step of the way, but Microsoft is finally going to enable genuine interoperability.
http://computerworld.co.nz/news.nsf/news/53A9FEF5880B1C7DCC25735900739D2F?
opendocument&utm_source=topnews&utm_medium=email&utm_campaign=topnews

How is this good? It means competitors to Microsoft finally get a fair go. Monopolies are bad for consumers and bad for innovation. It’s as simple as that.

From Computerword –

Carlo Piana, FSFE’s legal counsel, described the court ruling as “a milestone for competition. It puts an end to the notion that deliberate obfuscation of standards and designed lock-in is an acceptable business model, and forces Microsoft back into competing on the grounds of software technology.”

Also check out:

http://blogs.cnet.com/8301-13505_1-9779529-16.html?part=rss&tag=feed
&subj=TheOpenRoad

http://commandline.org.uk/more/microsoft/
eu-microsoft-decision-upheld-2007-09-17-11-00.html?showcomments=yes

This is especially good for Samba:

The Court of First Instance’s judgement, like the commission’s before it, sees Samba as the means for competition, in the Work group server space (i.e. file servers, print servers, etc). All potential competitors to Microsoft are using Samba, (the commissions own research found that 98% of competing products in this space use Samba), so it is good that the commission and the CFI are keen to get the documentation from Microsoft in a form that open source projects such as samba can use.

http://yro.slashdot.org/comments.pl?threshold=5&mode=thread&
commentsort=0&op=Change&sid=299827