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:
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:
- Connect to MySQL database by creating a new Mysqlconnection object.
- instantiate a new Mysqlcursor object from the Mysqlconnection object by calling the cursor () method.
- 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.
- 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