Title: Create and run stored procedures in MariaDB with Python

[Stored procedures let you define functions inside the database]

SQL provides two kinds of reusable SQL code that you can save in the database and then call from your program: stored procedures and stored functions. This example creates one of each and then calls them.

Differences

Stored procedures and stored functions both let you store pre-packaged SQL code, but there are some differences. It may help to think of a procedure as code that does something and a function as code that returns a value. That's not quite correct, but it gives the general flavor.

The following table highlights the differences more precisely.

	Stored Procedure	Function
Return Value	Returns 0, 1, or more rows	Returns 1 result
DML (Data Manipulation Language)	Can use DML	Cannot use DML
In SQL	Must be called with EXEC or CALL	Can be used in SQL statements (like SELECT)
Parameters	Can have input and output parameters	Normally has only input parameters
Transactions	Can manage transactions	Cannot manage transactions
Function Calls	Can call other stored procedures and functions	Can call other stored functions

In general, you should use a stored function if you want to use a calculated value in a SELECT statement and don't want to modify any data.

You should use a stored procedure if you want to return multiple results or perform more complicated operations on the data. In particular, you can use stored procedures to encapsulate business logic. For example, you could write a procedure to create a new customer's records while performing validation and creating records in multiple tables.

Procedures and Functions

This example includes a bunch of code that builds and populates a small database. You've seen that kind of code before so I won't dwell on it here.

The following make_procedures function creates a stored procedure and a stored function.

def make_procedures(cur): '''Create a stored procedure or function.''' # Requires CREATE ROUTINE privilege. Seems to require ALTER ROUTINE? # Create a stored procedure. command = ''' CREATE OR REPLACE PROCEDURE get_total_sales_sp( IN input_name VARCHAR(50), OUT total_sales DECIMAL(10,2)) BEGIN SELECT SUM(Amount) INTO total_sales FROM Sales WHERE Name = input_name; END ''' cur.execute(command) # Create a function. command = ''' CREATE OR REPLACE FUNCTION get_total_sales_func(input_name VARCHAR(50)) RETURNS DECIMAL(20, 2) BEGIN SELECT SUM(Amount) INTO @Total FROM Sales WHERE Name = input_name; RETURN @Total; END ''' cur.execute(command) print('Procedures created')

WARNING: Don't give the parameters names that match column names. For example, if you call the procedure's or function's input parameter name, then the database will think you're referring to the table column Name. Python is case-sensitive but the database is not, so it thinks the two are the same. It compares the Name column to the Name column and thus selects every record.

The first SQL command creates a stored procedure named get_total_sales_sp. The IN and OUT clauses define the procedure's input and output parameter types. This function takes a single VARCHAR(50) as an input and returns a DECIMAL(10,2).

The code between the BEGIN and END statements forms the body of the procedure. This code selects Amount values from the Sales table where the record's Name value matches the name input parameter. It uses the SUM function to add up the values and saves the result in the output parameter total_sales. The procedure returns that value when it's finished.

Note: If you use a script to create a stored procedure, you may have to change the delimiter character. Normally a program that runs scripts uses the semi-colon for a delimiter and that's a problem if the stored procedure also includes semi-colons. To avoid confusing the script parser, you can use the DELIMITER command to change the delimiter before defining the procedure. After you define the procedure, you can use the DELIMITER statement again to change the delimiter back to a semi-colon.

After it defines the CREATE PROCEDURE statement, the code uses a cursor to execute it and create the procedure.

Next, the function defines the get_total_sales_func stored function. The function's parameter list only includes input parameters. Then the RETURNS clause specifies the return type.

The function works much as the procedure. It uses SUM to add up sales amounts and saves the total in the user-defined variable @Total. It then returns @Total.

Users and Privileges

As I mentioned in an earlier post, it's a bad idea to run code while logged in as root. If you make any mistakes while you're root, you can inflict all manner of mischief on the database.

This example uses the following code to create the user sp_runner just to run the stored procedure and function.

def make_sp_runner(cur): # Create the sp_runner user. command = ''' CREATE OR REPLACE USER 'sp_runner'@'localhost' IDENTIFIED BY 'sp_runner_password' ''' cur.execute(command) # Give sp_runner permission to execute the procedure. command = ''' GRANT EXECUTE ON PROCEDURE sales_db.get_total_sales_sp TO 'sp_runner'@'localhost' ''' cur.execute(command) # Give sp_runner permission to execute the function. command = ''' GRANT EXECUTE ON FUNCTION sales_db.get_total_sales_func TO 'sp_runner'@'localhost' ''' cur.execute(command) print('User created')

This code first uses a CREATE USER statement to create the user. It then grants the EXECUTE privilege for the user on the stored procedure and function. Without that privilege, the user could not execute the procedure or function.

Note that the program does not give the user any other privileges. In particular, it does not give the user permission to create, update, delete, or even view the data in the Sales table.

This is one of the cooler features of stored procedures and functions. You can allow a user to use them but not to see the data that they use. For example, you can imagine an employee who is allowed to see the total salary budget of a department but who is not allowed to see the salaries of individual employees.

Tests

Here's the example's main program.

# Main program. prepare_database(True) try: conn = mariadb.connect( user='sp_runner', password='sp_runner_password', host='localhost', port=3306 # Default MariaDB port ) except mariadb.Error as e: print(f'Error connecting to MariaDB: {e}') else: try: conn.autocommit = True with conn.cursor() as cur: cur.execute('USE sales_db') # Call the stored procedure. Requires EXECUTE privilege. print('Stored procedure with CALL:') cur.execute('CALL get_total_sales_sp("Alice", @total)') cur.execute('SELECT @total') results = cur.fetchone() print(f' Alice earned ${results[0]}\n') # Call it again using callproc. Requires EXECUTE privilege. print('Stored procedure with callproc:') input_name = 'Bobby' output_total = 0.0 params = (input_name, output_total) cur.callproc('get_total_sales_sp', params) results = cur.fetchone() print(f' Bobby earned ${results[0]}\n') # Call the function using SELECT. Requires EXECUTE privilege. print('Function with SELECT:') params = ('Cindy', ) cur.execute('SELECT get_total_sales_func(?)', params) result = cur.fetchone() print(f' Cindy earned ${result[0]}\n') # Try to use a SELECT statement to display totals. query = 'SELECT * FROM Sales' cur.execute(query) for row in cur: print(f' {row}') except mariadb.Error as e: print(f'MariaDB error: {e}')

After setting up the database connection and creating a cursor, the program uses a CALL command to call the stored procedure. It passes the procedure the input parameter "Alice". For the procedure's output parameter, the code passes the user-defined SQL variable @total. The stored procedure executes and stores its result in @total. The code then uses a SELECT statement to get the value stored in @total, uses fetchone to pull the value out of the cursor, and displays the result.

Next, the code calls the stored procedure again, this time by using the callproc function. It creates Python variables to hold the input and output, and then uses callproc to invoke the procedure. The program uses fetchone to pull the return value out of the cursor and displays the result. Notice that the code doesn't use the value output_total, but it must pass something in for that parameter to keep the procedure happy.

The program then uses a SELECT statement to use the function. It parameterizes the statement (you learned about that in my post on SQL injection) but you can build the input parameter into the command string if you like. (But read the SQL injection post first!) The program executes the SELECT statement, uses fetchone to extract the result from the cursor, and displays the result.

The program's last test shows that the user cannot view the records in the Sales table. The code creates a simple SELECT statement and tries to execute it. At that point, the database engine throws a tantrum and raises an error.

Output

The following text shows the program's output.

Stored procedure with CALL: Alice earned $195.00 Stored procedure with callproc: Bobby earned $177.00 Function with SELECT: Cindy earned $267.00 MariaDB error: SELECT command denied to user 'sp_runner'@'localhost' for table `sales_db`.`sales`

The program performs these tasks:

Uses CALL to invoke the stored procedure and displays Alice's sales total.
Uses callproc to call the stored procedure and displays Bobby's sales total.
Uses a SELECT statement to call the stored function and displays Cindy's sales total.
Tries to select records from the Sales table and is denied.

Conclusion

Stored procedures and functions are useful for several reasons. They let you provide a way for a user to see information without giving them permission to view an entire table. Similarly they can let the user modify data without UPDATE or DELETE privileges.

Stored procedures and functions are executed on the database, so that can save some communication overhead (and hence time) if they need to perform very complicated tasks.

In larger projects, stored procedures and functions let you divide the work. For example, subject matter experts (SMEs) can put business logic inside the database without requiring you to write Python code. Later, if the business logic changes, they can update the stored procedures/functions without requiring changes to the Python code. (Assuming the updates don't require changes to parameters.)

All in all, stored procedures and functions are a useful addition to your database programming toolkit.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.