Summary: in this tutorial, you will learn how to call MySQL stored procedures in Python by using MySQL Connector/Python API.
This tutorial picks up where Deleting Data from a Table tutorial left off.
Before we start
If you are not familiar with stored procedures in MySQL or you 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:
find_all()
– return all books & authorsfind_book_by_isbn()
– return the title of a book based on its ISBN.
1) The find_all() stored procedure
First, create a stored procedure that retrieves all books and authors from the pub
database:
USE pub;
DELIMITER $$
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
ORDER BY title;
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
The find_all()
stored procedure has a SELECT
statement with INNER JOIN
clauses that retrieve the title, ISBN, and author’s full name from books
and authors
tables.
Second, call the find_all()
stored procedure:
CALL find_all();
Code language: SQL (Structured Query Language) (sql)
2) The find_by_isbn() stored procedure
The following creates the stored procedure find_by_isbn()
that finds a book based on an ISBN:
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 ;
Code language: SQL (Structured Query Language) (sql)
The find_by_isbn()
accepts two parameters:
- ISBN (IN parameter)
- title (OUT parameter).
When you pass an ISBN to the stored procedure, you will obtain the title of the book, for example:
CALL find_by_isbn('1235927658929',@title);
SELECT @title;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+-------------------------------------------------+
| @title |
+-------------------------------------------------+
| Debatable Land Between This World and the Next |
+-------------------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Calling stored procedures in Python
To call a stored procedure in Python, you follow these steps:
- First, connect to the database by creating a new
MySQLConnection
object. - Second, create a new cursor object from the connection object by calling the
cursor()
method. - Third, call
callproc()
method of theMySQLCursor
object to execute the stored procedure. - Finally, close the cursor and database connection as always.
1) Calling stored procedure example
The following example demonstrates how to call the find_all()
stored procedure in Python:
from mysql.connector import MySQLConnection, Error
from config import read_config
def find_all_books():
try:
# Read database configuration from the config file
config = read_config()
# Establish a connection to the MySQL database
with MySQLConnection(**config) as conn:
# Create a cursor to execute SQL queries
with conn.cursor() as cursor:
# Call the stored procedure 'find_all'
cursor.callproc('find_all')
# Process the results of the stored procedure
results = []
for result in cursor.stored_results():
results.append(result.fetchall())
return results
except Error as e:
raise e
if __name__ == '__main__':
try:
result = find_all_books()
print(result)
except Error as e:
print(e)
Code language: Python (python)
How it works.
First, import the necessary classes (MySQLConnection
and Error
) from the mysql.connector
module and a function read_config
from a module named config
:
from mysql.connector import MySQLConnection, Error
from config import read_config
Code language: Python (python)
Second, define a function called find_all_books()
that will call the stored procedure find_all()
:
def find_all_books():
# ...
Code language: Python (python)
Inside the find_all_books()
function, call the stored procedure find_all()
using the callproc()
stored procedure, iterating the result of the stored_results()
method, fetching all rows from each result set, and appending them to the results
list.
Third, call the find_all_books()
function and display the result sets:
if __name__ == '__main__':
try:
result = find_all_books()
print(result)
except Error as e:
print(e)
Code language: Python (python)
2) Calling stored procedure and passing arguments
The following example shows you how to call the find_by_isbn()
stored procedure:
from mysql.connector import MySQLConnection, Error
from config import read_config
def find_book_by_isbn(isbn):
args = (isbn, 0)
try:
# Read database configuration from the config file
config = read_config()
# Establish a connection to the MySQL database
with MySQLConnection(**config) as conn:
# Create a cursor to execute SQL queries
with conn.cursor() as cursor:
# Call the stored procedure 'find_by_isbn'
result_args = cursor.callproc('find_by_isbn', args)
book_title = result_args[1]
return book_title
except Error as e:
print(e)
raise e
if __name__ == '__main__':
try:
result = find_book_by_isbn('1236400967773')
print(result)
except Error as e:
print(e)
Code language: Python (python)
Output:
The Giant on the Hill
In this example, we also use the callproc
method of the cursor object to call a stored procedure find_by_isbn
.
Furthermore, we pass an argument to the callproc
method and get the result back as a list of tuples with the first element as ISBN and the second element as a book title.
The find_book_by_isbn()
function returns the title of the book specified by the ISBN.
Summary
- Use the
callproc()
method of a cursor object to call stored procedures from MySQL in Python programs.