Home/Python MySQL Tutorial/Calling MySQL Stored Procedures in Python, storedprocedures

Home/Python MySQL Tutorial/Calling MySQL Stored Procedures in Python, storedprocedures

F you are not familiar with MySQL stored procedures or want to review it as a refresher, you can follow the MySQL stored procedures tutorial.

We will create two stored procedures for the demonstration in this tutorial. The first stored procedure gets all books with authors information frombooksAndauthorsTables:

 

12345678910111213

DELIMITER $ USE python_mysql $ create procedure find_all () begin select title, isbn, CONCAT (first_name, '', last_name) AS author FROM books inner join book_author ON book_author.book_id = books. id inner join authors on book_author.author_id = authors. id; END $ DELIMITER;

Thefind_all()Stored procedure has a SELECT statement with JOIN clses that retrieve title, isbn and author's full name frombooksAndauthorsTables. When we executefind_all()Stored procedure, it returns a result as follows:

 

1	CALL find_all ();

The second stored procedure namedfind_by_isbn()That is used to find a book by its ISBN as follows:

 

123456789

DELIMITER $ create procedure find_by_isbn (IN p_isbn VARCHAR (13), OUT p_title VARCHAR (255) begin select title INTO p_title FROM books WHERE isbn = p_isbn; END $ DELIMITER;

Thefind_by_isbn()Accepts two parameters: the first parameter is isbn (IN parameter) and second is title (OUT parameter ). when you pass the isbn to the stored procedure, you will get the title of the book, for example:

 

12	CALL find_by_isbn ('20140901', @ title); SELECT @ title;

 

Calling stored procedures from Python

To call a stored procedure in Python, you follow the steps below:

The following example demonstrates how to callfind_all()Stored procedure in Python and output the result set.

 

123456789101112131415161718192021222324

From mysql. connector import MySQLConnection, Errorfrom python_mysql_dbconfig import read_db_config def call_find_all_sp (): try: db_config = read_db_config () conn = MySQLConnection (** db_config) cursor = conn. cursor () cursor. callproc ('Find _ all') # print out the result for result in cursor. stored_results (): print (result. fetchall () failed T Error as e: print (e) finally: cursor. close () conn. close () if _ name _ = '_ main _': call_find_all_sp ()

The following example shows you how to callfind_by_isbn()Stored procedure.

 

1234567891011121314151617181920212223

From mysql. connector import MySQLConnection, Errorfrom python_mysql_dbconfig import read_db_config def call_find_by_isbn (): try: db_config = read_db_config () conn = MySQLConnection (** db_config) cursor = conn. cursor () args = ['20170101', 0] result_args = cursor. callproc ('Find _ by_isbn ', args) print (result_args [1]) failed T Error as e: print (e) finally: cursor. close () conn. close () if _ name _ = '_ main _': call_find_by_isbn ()

Thefind_by_isbn()Stored procedure requires two parameters therefore we have to pass a list (args) That contains two elements: the first one is isbn (1236400967773) and the second is 0. The second element of the args list (0) is just a placeholder to holdp_titleParameter.

Thecallproc()Method returns a list (result_args) That contains two elements: the second element (result_args [1]) holds the value ofp_titleParameter.

In this tutorial, we have shown you how to call stored procedures in Python by usingcallproc()Method of the MySQLCursor object.

Related Tutorials