Why is my MySQL stored procedure not executing when called from Python using os.system() or subprocess.Popen()?

I have a .sql file that creates and executes a stored procedure. The structure of the .sql file is like this:

delimiter $$
drop procedure if exists `myProcedure`$$
create procedure `myProcedure` (
  input INT
)
BEGIN
  ... sql statements;
END $$

call myProcedure(10);
$$

When this is executed from the shell in the following way, the stored procedure is created and the call statement at the end is executed properly.

mysql -uuser -ppassword -hhost db_name < mysql_proc.sql

However, when I execute the above command from my python script, the call statement at the end is not being executed. This is how I'm executing the above command in my Python script:

command = 'mysql -uuser -ppassword -hhost db_name < mysql_proc.sql'
mysql_cmd_proc = subprocess.Popen(command,
        shell=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE)
stdout_val, stderr_val = mysql_cmd_proc.communicate()
print 'mysql command stdout: %s' % stdout_val
print 'mysql command stderr: %s' % stderr_val

There's no error or output returned (stdout_val and stderr_val are just empty strings). I tried surrounding the call myProcedure(10); $$ with BEGIN and END $$ blocks also, but that didn't help either. Any reasons why this could be a proble开发者_开发百科m?

command = 'mysql -uuser -ppassword -hhost db_name < mysql_proc.sql'
mysql_cmd_proc = subprocess.Popen(command,
        shell=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE)

Don't use shell=True (it should be False, which it is by default). Also, pass the command as a list.

Do this:

>>> import shlex
>>> command = 'mysql -uuser -ppassword -hhost db_name < mysql_proc.sql'
>>> mysql_cmd_proc = subprocess.Popen(shlex.split(command),   # shlex.split
...                                   stdout=subprocess.PIPE,
...                                   stderr=subprocess.PIPE)

shlex.split