Home/python MySQL tutorial/calling mysql Stored procedures in Python calling MySQL Stored procedures in Python

f you is not familiar with MySQL stored procedures or want to review it as a refresher, you can follow the MySQL stored P Rocedures Tutorial.

We'll create the stored procedures for the demonstration in this tutorial. The first stored procedure gets all books with authors information from books and authors tables:

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 ;

find_all()the stored procedure have a SELECT statement with JOIN clauses that retrieve title, ISBN and author's full name fro M books and authors tables. When we execute find_all() the stored procedure, it returns a result as follows:

1	Call Find_all ();

The second stored procedure named find_by_isbn() that's used to find a book by its ISBN as follows:

123456789

DELIMITER $$ CREATE PROCEDURE find_by_isbn ( in P_ISBN VARCHAR), out p_title VARCHAR (255)) BEGIN SELECT title to p_title from books WHERE ISBN = p_isbn; end$$ DELIMITER ;

The find_by_isbn() accepts parameters:the first parameter is ISBN (in parameter) and second are title (out parameter). When you pass the ISBN to the stored procedure, you'll get the title of the book, for example:

12	call find_by_isbn (' 1235927658929 ', @title); SELECT @title;

Calling stored procedures from Python

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

1. Connect to MySQL database by creating a new Mysqlconnection object.
2. instantiate a new Mysqlcursor object from the Mysqlconnection object by calling the cursor () method.
3. call   callproc ()  method of the Mysqlcursor object. You pass the stored procedure ' s name as the first argument of the   Callproc ()  method. If the stored procedure requires parameters, you need to pass a list as the second argument to the   CALLPR OC ()  method. In case the stored procedure returns a result set, you can invoke the   stored_results () method of th E Mysqlcursor object to get a list iterator and iterate this result set by using the   fetchall () &NB Sp;method.
4. Close the cursor and database connection as always.

The following example demonstrates how to call the find_all() stored procedure in Python and output the result set.

123456789101112131415161718192021222324

from MySQL. Connector import mysqlconnection, Error from python_mysql_dbconfig import read_db_config def call_find_all_sp(): try: db_config = read_db_config() conn = mysqlconnection(**db_config) /c4> cursor = conn. Cursor() cursor. Callproc(' Find_all ') # Print out the resultFor result in the cursor. Stored_results(): Print(result. Fetchall()) except Error as e: Print(e) finally: cursor. Close() Conn. Close() if __name__ = = ' __main__ ': call_find_all_sp()

The following example shows the find_by_isbn() stored procedure.

1234567891011121314151617181920212223

from MySQL. Connector import mysqlconnection, Error from python_mysql_dbconfig import read_db_config def call_find_by_isbn(): try: db_config = read_db_config() conn = mysqlconnection(**db_config) /c4> cursor = conn. Cursor() args = [' 1236400967773 ', 0] result_args = cursor. Callproc(' FIND_BY_ISBN ', args) Print(result_args[1]) except Error as e: Print(e) finally: cursor. Close() Conn. Close() if __name__ = = ' __main__ ': call_find_by_isbn()

The find_by_isbn() stored procedure requires and parameters therefore we have to pass a list ( args ) that contains both 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 the p_title parameter.

The callproc() method returns a list ( result_args ) that contains the elements:the second element (Result_args[1]) holds the value of the p_title parameter.

In this tutorial, we had shown you what to call stored procedures in Python by using callproc() method of the Mysqlcursor Obje Ct.

Related tutorials

Home/python MySQL tutorial/calling mysql Stored procedures in Python calling MySQL Stored procedures in Python