Python User Defined Functions for SQL Server Professionals

By: Rick Dobson | Updated: 2022-07-11 | Comments (1) | Related: > Python

Problem

I am a SQL professional who recently started programming in Python. I discovered Python user defined functions, but I do not understand their role in Python programming, and I am not sure how or why to program them. Please explain what benefits user-defined functions bring to Python programming. As importantly, I present examples that help me to employ them as I grow my Python programming skills.

Solution

At a top-line level, a Python user-defined function delivers many of the same advantages as code containers in SQL, such as stored procedures. Therefore, one key role of Python user-defined functions is to contain code. This key role makes code re-usable. Employing user-defined functions can also simplify code maintenance by reducing the incidence of repeated code in a large application. Whenever an application needs to do what the user-defined function does, you can simply call the function.

Python user-defined functions facilitate segmenting code into modular units. Different teams of developers can work concurrently on different user-defined functions. This can reduce the overall development time for large applications. Also, each user-defined function for a large application can have a limited objective. Also, if an application consistently invokes a user-defined function when it needs to do what the function does, then you can readily introduce changes throughout an overall application by altering just one user-defined function.

This tip introduces you to the basics of creating and invoking Python user-defined functions with a series of examples. One special area of emphasis is how to manage parameters when invoking user-defined functions. The examples show how to return numeric, string, and Boolean values from user-defined functions. This tip also illustrates how to use iteration from within a user-defined function. The examples conclude with a generator function that reveals how to return a custom iterator object that can be re-used by other code. Most of the code and all results are displayed in IDLE; this integrated development environment ships with Python when it is downloaded from the Python.org site. This tip implements and invokes its user-defined function examples with Python 3.10, the most recent version as of the time this tip is prepared in mid-June 2022.

Hello from my first Python user defined function

A hello world example may be a good starting point for introducing user-defined functions.

The following IDLE script window creates a user-defined function with the def keyword; the function's name is my_1st_function. The line with the def keyword terminates with a colon.
The code for the user-defined function starts on the indented line below the line with the def keyword. If there is more than one line of code in the user-defined function, those lines should be indented like with the first line.
The code for the user-defined function invokes the Python built-in print function to display "Hello from my 1st function." This line can appear in the IDLE shell window, which normally appears on your computer screen.
The first line of code after the indented code following the function name in the example below invokes the my_1st_function.
Notice that open and close parentheses follow the function name when you are defining it as well as when you are calling it. When the user-defined function accepts parameters, they appear in parentheses at the time the user-defined function is called.
You can run the Python script with the specification for the code for the user-defined function by choosing Run, Run Module from the IDLE window with the code for defining and invoking the function.

The IDLE Shell window with the output from the preceding script window appears next. The last line in the window displays the output from calling the user-defined function. The line is displayed from within the my_1st_function user-defined function.

That's all there is to the first example.

To recap:

You initiate the creation of a user-defined function with the def keyword.
Lines of code for the function appear indented below the line with the def keyword.
You can invoke the user-defined function by typing the function name followed by parentheses.

All the examples in this tip have the Python code for defining a user-defined function and the code for invoking a user-defined function in a single Python file with a py file extension. However, Python also permits the importing of a user-defined function from one file while running the code to invoke the function from another file. See this article on how to accomplish this, as well as limitations of the approach.

Summing two numbers with positional parameters using Python

The example for this section shows how to define and invoke a user-defined function with positional parameters. Here's the script window with the code for defining the user-defined function, and the code attempting to invoke the user-defined function four times.

The line starting with the def keyword names the user-defined function and specifies two parameters for use in the function.
- The function name is sum_2_numbers.
- The parameter names for the function are n1 and n2
- A print function immediately after the line with the def keyword displays in the IDLE Shell window the n1 and n2 values. The values are those passed when the function is called.
- The return keyword passes back the sum of the values for the n1 and n2 parameters.
The user-defined function is called four times in the code following the user-defined function definition.
- The first user-defined function call, just like the remaining three, resides in a print function. The print function has up to two parameters.
  - The following text calls the user-defined function: sum_2_numbers (1,2). This initial parameter returns the sum from the user-defined function with two positional parameter settings. The values 1 and 2 are positional parameters. They are called positional parameters because the first and second positional parameter values are assigned, respectively, to n1 and n2 parameters in the user-defined function.
  - The second parameter for the print function is the "\n" text that trails the call of the user-defined function. This text causes a blank line to appear after the print function displays the sum of n1 and n2 in the IDLE Shell window.
- The second invocation of the sum_2_numbers function is nearly the same as the first invocation, except that it reverses the position of the parameter values of 1 and 2 right after the function name. In other words, the revised parameter value order assigns the value 2 to n1 and the value 1 to n2.
- The third invocation is like the first and second invocations, except it uses floating-point (real) numbers as parameter values. The value for the first parameter is 1.1, and the value for the second parameter is 2.2.
- The fourth invocation is distinctly different than the preceding three invocations in that it does not specify positional parameter values when invoking the user-defined function. Also, there is no inclusion of "\n" in the parentheses for the print function.

The IDLE Shell window image in the screenshot below is generated by running the preceding script. The output from all four invocations of the sum_2_numbers function starts immediately after the line with RESTART followed by the path name and filename for a Python script file containing the preceding script. There are two lines for each of the first three invocations of the user-defined function. The fourth invocation generates an error message that will be discussed below.

The first two lines of output are from the first invocation of the sum_2_numbers function.
- The first line reads "1 + 2 equals ". This line is from the inside of the user-defined function.
- The second line contains the integer value 3, which is the sum of 1 and 2.
The second two lines of output are from the second invocation of the sum_2_numbers function.
- The first of these second two lines reads "2 + 1 equals". Notice that the order of the parameter values flips from 1 before 2 to 1 trailing 2. This is because 2 precedes 1 as a parameter value in the second function call.
- Despite the flipped positional parameter values, the sum value is the same – namely, 3.
The third two lines of output are from the third invocation of the sum_2_numbers function.
- The parameter values for this invocation are floating-point values.
- When using floating-point values in arithmetic, it is not unusual to encounter rounding errors from a computation.
- In this case, the sum is reported as 3.3000000000000003, which has a rounding error of 0.0000000000000003. This kind of rounding error is not specific to just Python, but it occurs because computers represent real numbers as base 2 fractions, and base 2 fractions do not represent all real values precisely. This imprecise representation manifests itself as rounding errors for computations with floating-point values.
The fourth set of output in the following screenshot displays an error message about line 20 in the preceding script; this is the last line in the preceding script. The error message explains that 2 required parameter values are missing in the code to invoke the sum_2_numbers user-defined function.

Using Default Parameter Values and Named Parameters with Python

Using default parameter values is one approach to circumventing the error message that results from the fourth user-defined function call within the preceding section. That function call failed because of two missing parameters. By designating a default parameter value for a parameter within a function, Python can use the default parameter value when there is no parameter value passed from a function call.

If the call supplies a value for a parameter, then the supplied parameter value is used when invoking the function.
If the call does not designate a value for a parameter, then the default parameter value replaces the missing parameter value in the function call.
You can specify default parameter values for all, some, or none of the parameters in a user-defined function.

Another technique for managing parameters is to use named parameter values when calling a user-defined function. The user-defined function calls to this point in the tip all use positional parameters instead of named parameters. The syntax for named parameters is to assign them when you are calling a user-defined function. This syntax differs from the syntax for default parameter values where the values are assigned inside the function.

Here is a script that illustrates the use of both named parameters and default parameter values.

The code begins with a def keyword that assigns a name of sum_2_numbers_w_defaults to the user-defined function.
Default parameter values are designated within the parentheses following the function name.
- A value of 1 is the default parameter value for n1.
- A value of 2 is the default parameter value for n2.
Aside from the new name for the user-defined function and the default parameter value assignments, the code for the sum_2_numbers_w_defaults user-defined function is identical to the code for the sum_2_numbers user-defined function in the preceding section.
However, the call statements for the two user-defined functions are different. This is because the calls for the sum_2_numbers user-defined function in the preceding section use positional parameters values, but the call statements for the sum_2_numbers_w_defaults user-defined function in this section use named parameter values.
- With positional parameter value assignments, the order of the values in the function call must match those in the user-defined function statement.
- With named parameter value assignments, the order of the parameter values in the function call is immaterial. This is because the assignment of a value to a parameter name in a call statement is how the parameters in the function derive their values.

Here is a shell window with output from the preceding script.

The first pair of result lines are for the first function call that uses both default parameter value assignments. Recall the default parameter values are 1 and 2, respectively, for parameters n1 and n2.
The second pair of result lines are for the second function call that uses a named parameter value of 3 for n1 and a default parameter value for n2.
The third pair of result lines are for the third function call that uses a default parameter assignment for n1 but a named parameter value of 4 for the n2 parameter.

The description of the scripts for invoking the sum_2_numbers user-defined function in the preceding section showed that the sum does not depend on the order of the parameters, but that is not the case for all user-defined functions. For example, the following script generates two different outcomes depending on the order of the parameter values. However, by updating the following script with named parameter calls for a second version of the script, we can make the order of the parameter value assignments immaterial. The next pair of script listings illustrate this point.

The script for a user-defined function named x_raised_to_the_yth_power appears next. Notice that the function call assigns parameter values based on position. In the first function call, the x parameter value is 2, and the y parameter value is 3. In contrast, the second function call assigns the x parameter a value of 3 and the y parameter a value of 2.

Here's the output from the preceding script. Unlike the sum_2_numbers function, the order of the parameters matters for the x_raised_to_the_yth_power function.

When 2 is raised to the third power, the function's return value is 8
However, when 3 is raised to the second power, the function's return value is 9.

If a user inadvertently meant to raise 2 to the third power in the second function call, then the outcome is at variance with what a user really wants. Positional parameter value assignments make it easy to make this kind of error occur when calling a function. In contrast, named parameter value assignments make it much more difficult to inadvertently assign the wrong value to a parameter. Here's a script for the same user-defined function (x_raised_to_the_yth_power). The first and second calls flip the order of the parameter value assignments. However, because we are using named parameter value assignments, both calls yield the same result.

Here's the script. Focus especially on the difference in the order of the named parameter value assignments.

Here is the shell window with the output. As you can see, the value returned by the function is the same for the first and second function calls although the order of the parameter value assignments is different in the first and second function calls.

Searching a list of strings with an iterator and any operator using Python

A common use case is to determine if another string matches at least one item in a list of string values. A typical example is the code in this section to determine if a specific username resides in a list of usernames. You can use a for loop to implement this task, but Python also offers an iterator, which can successively traverse the items in a list without a loop. By comparing the iterator value to another string value inside of an any operator, you can write a single line of code to assess if a string value resides within a list of strings.

The next user-defined function example compares a string, named fname, to the items in a list of strings. The name for the list is grandson_list. The user-defined function returns a value of True if fname matches any of the items in the grandson list. Otherwise, the user-defined function returns a value of False.

Here's the list for the user-defined function and two calls to the function with two different values of fname.

The grandson_list object is a list of strings with four items in it: 'Harry', 'William', 'James', 'Peter'. The grandson_list object can be accessed from inside the user-defined function without being passed as a parameter because grandson_list is implicitly a global object.
The user-defined function name is is_fname_in_grandson_list.
- Its parameter is fname.
- Its return object has a boolean data type, which can have values of True or False.
A one-line expression in the function populates a boolean object (fname_is_in) with a value of True or False.
- When fname is in grandson_list, then the expression populates fname_is_in with a value of True.
- Else, when fname is not in grandson_list, then the expression populates fname_is_in with a value of False.
- The iterator (grandson) in the expression serves two roles
  - It successively returns list items from the grandson_list object.
  - Also, inside of the any operator, the current value of grandson for an iteration through the grandson_list is compared to fname.
- The iterator comparison to fname in the any operator assigns a value of True or False to fname_is_in.
  - When the value of grandson matches fname for at least one item in the grandson_list, then the any operator returns a value of True.
  - Otherwise, the any operator returns a value of False.
The return statement at the end of the user-defined value passes back the value of fname_is_in to the calling routine.

After the user-defined function ends with the return keyword, there are two sets of four lines – one set for each of two distinct fname values.

In the first set of four lines, fname is set equal to "Rick".
Then, a print function returns the items in the grandson_list object.
Next, a print function displays the current value of fname.
Finally, a print function embeds three items. The second item calls the is_fname_in_grandson_list user-defined function with a parameter of fname. The print function returns as its second value either True or False depending on whether the fname value matches any string in grandson_list.
- For the first set of four lines, the print function responds that Rick is not in grandson_list.
- The second set of four lines assigns "William" to fname. In this case, the last of the four lines indicate William is in grandson_list.

Here are the results that Python displays for the preceding script. There are three lines of output for each fname value.

When fname equals Rick, then "Is fname in grandson _list" corresponds to a value of False.
When fname equals William, then "Is fname in grandson_list" corresponds to a value of True.

Using generator Python functions

Python provides a generator function capability so that developers can create their own custom iterator objects. Unlike a regular user-defined function that often returns a single value, a generator function returns an iterator object with a sequence of values that can extend over a custom range. On the topic of returning values, a generator function passes back its sequence of values via the yield keyword whereas a regular user-defined function passes back a value through the return keyword.

The yield keyword in a generator function preserves the internal state of the generator function in between successive calls, a generator function passes back one item from its source of items on each successive call to the function.
The return keyword within a user-defined function re-initializes the user-defined function after returning a value to another set of code that uses the output from the user-defined function.

The remainder of this section presents and describes the operation of a generator function as well as the process for creating it. If this section motivates you to learn more about generator functions, you may read any of these references (Python - Generator Functions, Python Generators from Programiz, or Python Generators from Scaler Topics).

The example in this section takes a string and displays it one character at a time in forward and backward order. The characters in the string are the source of items for the generator function. I used several different strings in testing the example, but the one that shows in this tip is for the name Harry for Prince Harry, one of the grandsons of Queen Elizabeth II. The application example in this section uses two different, but similar, functions – one generator to display the letters in a string in forward order, and a second generator to display the letters in a string in backward order.

Here is the code for the example. There are three segments to the example; a line of comment markers (#'s) separates the segments from one another.

The first segment assigns a value for the string object named string_value and then displays string_value via a print function.
The second segment is for displaying the characters in string_value in forward order. The code accomplishes this goal with the help of a generator function named reg_str.
The third segment is for displaying the characters in string_value in backward order. The code accomplishes this goal with the help of a generator function named rev_str.

Here are some comments for the code within each segment.

An equal sign in the first segment assigns "Harry" to string_value.
Then, a print function displays
- the text "string_value" followed by an equal sign
- the string_value object and
- the text for a new line ("\n")
The code for the second segment starts with a def keyword to begin defining the reg_str generator. The reg_str generator displays the characters in the my_str object at run time for the generator, but the code first needs to configure the generator.
- The code uses Python's len function on the my_str object to define the length object value. The length object has an integer data type.
- Next, a for statement creates a sequence of iterator values with corresponding numbers in the range of 0 through the length object value. There is one corresponding integer number for each character in string_value.
  - The Python range function returns a sequence of numbers starting with its initial argument value (0), ceasing when the number of characters matches the number of characters in string_value, and successively changing sequential numbers by a third increment value(1)
  - The string characters in the string_value object is passed into the reg_str generator at run time.
- The yield keyword pauses the reg_str generator for each of the passes through the for statement.
- After the generator is prepared starting with the def keyword and ending with the last pass through the for statement, a print function displays the name and location in memory of the reg_str generator. The call for the reg_str generator function uses string_value as a parameter.
- Two additional for statements each followed by print functions display, respectively
  - the numbers corresponding to the characters in the reg_str generator function and
  - the characters from within the reg_str generator function
  - the characters appear in a forward order because of the order of the numbers in the reg_str generator code – namely, 0, 1, 2, 3, 4 – for the string_value argument. There is one number per character in string_value.
The code for the third segment is very similar to the code in the second segment, except the generator function has the name rev_str and the numbers matching the generator characters start with a value of the length object and end with a value of 0.
The output from the script reveals the forward order and the backward order of the characters, which are, respectively, from the second and third segments.

# assign a value to string_value
# for character enumeration by generator functions
string_value = "Harry"
 
# display string_value to have its characters enumerated
print ("string_value = ", string_value, "\n")
 
##############################################################################
 
# generator code for enumerating the characters
# in a string in the order they appear
def reg_str(my_str):
    length = len(my_str)
    for i in range(0, length, 1):
        yield my_str[i]
        
# display the name and location of the generator function
print("name and location of the generator", reg_str(string_value), "\n")
 
#this code returns the i values in the generator
print ("display i values in order for generator")
for i in range(0, len(string_value), 1):
    print (i)
print()
 
# For loop to enumerate string characters
print ("display enumerated values in order from the generator")
for char in reg_str(string_value):
    print(char)
print()
 
##############################################################################
 
# generator code for enumerating the characters
# in a string in the reverse order they appear
def rev_str(my_str):
    length = len(my_str)
    for i in range(length - 1, -1, -1):
        yield my_str[i]
 
# display the name and location of the generator function
print("name and location of the generator", rev_str(string_value), "\n")
 
#this code returns the i values in the generator
print ("display i values in order for generator")
for i in range(len(string_value) - 1, -1, -1):
    print (i)
print()
 
# For loop to enumerate string characters
print ("display enumerated values in order from the generator")
for char in rev_str(string_value):
    print(char)
print()

Here are the results from the preceding script.

The value of the string_value object appears on the first line of output.
The next line of output indicates the generator function name for a forward order for characters. The name reg_str is derived from the def keyword in the preceding script. An octal number for the location of the generator function in memory follows the function name.
The next two sets of output are the numbers corresponding to the characters in the reg_str function followed by the characters in the reg_str function. The characters appear successively as one character per line.
The next set of output is for the rev_str generator function.
- First, you can see the generator function name followed by an octal number designating its location in memory.
- Second, you can see the numbers for the characters in reverse order from the rev_str function.
- Third, you can see the characters in reverse order.

Next Steps

The download for this tip contains all the Python scripts for creating and calling user-defined functions discussed in this tip.
The best next step is to run the Python script files on your computer. Then, try changing the scripts by altering parameter values for the functions. Verify that the function output is valid for your new parameter values. If necessary, alter the code for the scripts to help you see the underlying operation of the script files for defining and running the functions.
Finally, search the internet for more ideas on how to craft Python code for the tasks that you want to implement with Python functions.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-07-11

Introducing Python User Defined Functions to SQL Server Professionals