我需要帮助了解以下情况。我想调用存储的 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.我们可能必须更好地记录这一点。
