为什么当使用 os.system() 或 subprocess.Popen() 从 Python 调用时我的 MySQL 存储过程不执行？

发布于 2024-10-15 08:47:01 字数 1036 浏览 1 评论 0原文

我有一个创建并执行存储过程的 .sql 文件。 .sql 文件的结构如下：

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

call myProcedure(10);
$$

当按照以下方式从 shell 执行该文件时，将创建存储过程并正确执行末尾的 call 语句。

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

但是，当我从 python 脚本执行上述命令时，最后的 call 语句没有被执行。这就是我在 Python 脚本中执行上述命令的方式：

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

没有错误或返回输出（stdout_val 和 stderr_val 只是空字符串）。我尝试围绕 call myProcedure(10); $$ 也带有 BEGIN 和 END $$ 块，但这也没有帮助。有什么原因导致这可能成为问题吗？

原文

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 problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美人骨 2024-10-22 08:47:01

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

不要使用 shell=True （它应该是 False，默认情况下）。另外，将命令作为列表传递。

这样做：

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

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

回复收藏 0 原文

~没有更多了~