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.<\/p>\n
Some things worked fine from the DOS prompt but failed from within python (and RUN for that matter).<\/p>\n
Here is the answer in the form of a simple function (NB to get your indentation right !):<\/p>\n
def run_sql_script(scriptname):\r\n \"Run a script in MySQL\"\r\n import subprocess\r\n import time\r\n #this next line is too long for this blog but you will need it on one line to run\r\n args = \"\\\"C:\\\\Program Files\\\\MySQL\\\\MySQL Server 5.0\\\\bin\\\\mysql.exe\\\" \r\n -h%s -u%s -p%s --database=databasename < C:\/Projects\/projectname\/3_scripts\/%s\" \r\n % (DB_HOST, DB_USER, DB_PWD, scriptname)\r\n #print args\r\n child = subprocess.Popen(args=args, shell=True, executable=\"C:\\\\windows\\\\system32\\\\cmd.exe\")\r\n #need to check whether finished or not every so often\r\n i = True\r\n elapsed = 0\r\n while i == True:\r\n time.sleep(10)\r\n elapsed = elapsed + 10\r\n if child.poll() == None:\r\n elapsed_mins = float(elapsed)\/60\r\n print \"%.2f minutes elapsed running %s\" % (elapsed_mins, scriptname)\r\n else:\r\n print \"Finished running script \" + scriptname\r\n i = False\r\n<\/pre>\nKey points: can't use call, must use full Popen and explicitly name the shell (and use it!).<\/p>\n
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.<\/p>\n
If you want to kill it, open Task Manager and kill mysql.exe.<\/p>\n
In Windows there are apparently some horrible compromises to be made when doing some simple things. See ...adventures-in-python-launching-subprocesses\/<\/a><\/p>\n
There is an alternative approach but it doesn't seem to ever end the subprocess:<\/p>\n
#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)\nprint args #http:\/\/www.realtechnews.com\/posts\/2777 re: the \/k !!!!!\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading