Skip to main content
 首页 » 编程设计

Python 3.X 访问 cursor.callproc 的结果

2024年11月01日10zhoujg

我需要帮助了解以下情况。我想调用存储的 mysql 过程并将输出存储在变量中,然后访问内容以检查是否存在某些文本。我是 python 的新手,很难理解我应该怎么做。我绝对需要调用该过程,而不是执行 mysql 查询 show slave status;。但基本上该函数执行该查询。

Python 3.3.2+ (default, Feb 28 2014, 00:52:16)  
[GCC 4.8.1] on linux 
Type "help", "copyright", "credits" or "license" for more information. 
>>> import mysql.connector 
>>> conn = mysql.connector.connect(host = "xxxx", port = xxxx, user = "xxxx", password = "xxxx", database = "xxxx") 
>>> cursor = conn.cursor() 
>>> cursor.callproc('show_slave_status') 
() 
>>> for result in cursor.stored_results(): 
...     print(result.fetchall()) 
...  
[] 

如果我尝试运行查询而不是使用 cursor.callproc,则会出现以下错误:

>>> cursor.execute("call show_slave_status") 
Traceback (most recent call last): 
  File "/usr/local/lib/python3.3/dist-packages/mysql/connector/cursor.py", line 508, in execute 
    self._handle_result(self._connection.cmd_query(stmt)) 
  File "/usr/local/lib/python3.3/dist-packages/mysql/connector/connection.py", line 640, in cmd_query 
    'Use cmd_query_iter for statements with multiple queries.') 
mysql.connector.errors.InterfaceError: Use cmd_query_iter for statements with multiple queries. 
 
During handling of the above exception, another exception occurred: 
 
Traceback (most recent call last): 
  File "<stdin>", line 1, in <module> 
  File "/usr/local/lib/python3.3/dist-packages/mysql/connector/cursor.py", line 512, in execute 
    "Use multi=True when executing multiple statements") 
mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements 

如果我设置 multi=True 它也不会变得更好。

这是我在 mysql 控制台中运行查询的函数输出

mysql> call show_slave_status; 
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+ 
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File   | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | 
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+ 
| Waiting for master to send event | localhost   | xxxx        |        xxxx |            60 | mysql-bin.000025 |             5782876 | relay-bin.000075 |       5782979 | mysql-bin.000025      | Yes              | Yes               |                 |                     |                    |                        |                         | xxxx%.%\_NUM\_MEM          |          0 |            |            0 |             5782876 |         5783275 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |             xxxx | 
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+ 
1 row in set (0.10 sec) 

这是程序定义

mysql>  SHOW CREATE PROCEDURE show_slave_status\G 
*************************** 1. row *************************** 
           Procedure: show_slave_status 
            sql_mode:  
    Create Procedure: CREATE DEFINER=`xxxx`@`localhost` PROCEDURE `show_slave_status`() 
BEGIN show slave status; END 
character_set_client: latin1 
collation_connection: latin1_swedish_ci 
  Database Collation: latin1_swedish_ci 
1 row in set (0.27 sec) 

请您参考如下方法:

您使用 stored_results() 方法正确地完成了它。没有必要(或明智地)使用多个语句。虽然我们支持,但还是一一做为好。

看到结果是空的,看起来你正在连接一个主服务器或者一个没有从属状态的MySQL。

I have blogged about getting results after calling a procedure.我们可能必须更好地记录这一点。