Snippet: Fetching results after calling stored procedures using MySQL Connector/Python, snippetfetching

Snippet: Fetching results after calling stored procedures using MySQL Connector/Python, snippetfetching
Https://geert.vanderkelen.org/2014/results-after-procedure-call/Problem

Using MySQL Connector/Python, you are calling a stored procedure which is also selecting data and you wowould like to fetch the rows of the result.

Solution

For this example we create a stored procedure which is executingSHOW SLAVE STATUS.

'''Python cnx = mysql. connector. connect (user = 'Scott ', password = 'tiger', database = 'mining') cur = cnx. cursor () cur.exe cute ("drop procedure if exists slave_status") proc = "create procedure slave_status () begin show slave status; END" cur.exe cute () cur. call ("slave_status ")

For result_cursor in cur. stored_results (): for row in result_cursor: print (row [0]) '''

The result from the above wocould be:

shell $ python foo.py Waiting for master to send event

Discussion

Thestored_results() Method of cursor object is retiring an iterator object which will go over the results proceeded after calling the stored procedure. Each result is actually a MySQLCursorBuffered object.

You coshould also usewith_rowsCursor property to check if the cursor actually cocould return rows or not (for example, for SELECT statements). An example is provided in the documentation.