Python MySQL – Call Stored Procedures in Python

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 & authors
• find_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 the MySQLCursor 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.