by Arup Nanda
Part 4 of a five-part series that presents an easier way to learn Python by comparing and contrasting it to PL/SQL.
You may follow along with the Online Presentation: Learning Python for PL/SQL Developers - Part 4
Functions, Modules and File Operations
As is the case in most languages, Python provides repeatable code segments, similar to procedures and functions in PL/SQL. As you already know, in PL/SQL, a procedure does not return anything (although it can have an OUT parameter; but that's not a return, so it's not the same thing) and a function returns a single value. In Python, the equivalents of both PL/SQL procedures and functions is called simply a function. A Python function may or may not return anything. In this article, we will cover how to write functions and use them in your programs. As in the previous article in this series, we will see how to do something in PL/SQL and then do the equivalent in Python.
A function definition in PL/SQL has this general syntax format:
function FunctionName (
Parameter1Name in DataType,
Parameter2Name in DataType,
...
)
return ReturnDatatype
is
localVariable1 datatype;
localVariable2 datatype;
begin
... function code ...
return ReturnVariable;
end;
A procedure definition in PL/SQL has this general syntax:
procedure ProcedureName(
Parameter1Name in DataType,
Parameter2Name in DataType,
...
)
is
localVariable1 datatype;
localVariable2 datatype;
begin
... procedure code ...
end;
A function definition in Python follows this simple syntax:
def FunctionName (Parameter1Name,Parameter2Name, ...):
... function code ...
return ReturnVariable
Note some important properties of the Python function definition compared to the PL/SQL equivalent:
- The definition starts with
def followed by the name of the function.
- The parameters come inside the parentheses.
- Only the parameter names are listed. The datatypes are not mentioned, unlike with PL/SQL. Parameters are optional; a function doesn't need to have parameters.
- There is a colon (:) at the end of the function name to mark the end of the name and beginning of the definition. Unlike PL/SQL, there is no "BEGIN ... END" construct.
- There is no mention of what the function returns at the definition level. If you notice there is no mention of whether the function even returns anything at the definition level.
- The indentation after the colon shows the code of the function. This is the same style followed in Python to mark "IF ... THEN ... ELSE" blocks or loops. Indentations mark the beginning and end of function code, not "begin ... end," as in PL/SQL.
- The function may optionally return something at the final line. The syntax is the same as in PL/SQL:
return ReturnVariable.
Now that you've got the basic idea about the syntax vis-a-vis PL/SQL, let's start with a very simple procedure in PL/SQL that accepts a principal amount and interest rate, computes the interest amount and the new principal after the interest is added, and displays the new principal.
Here is how we do it in PL/SQL. Note that I deliberately chose to use the Python naming convention, for example, pPrincipal, not a PL/SQL-style variable name such as p_principal.
PL/SQL
-- pl1.sql
declare
procedure calcInt (
pPrincipal number,
pIntRate number
) is
newPrincipal number;
begin
newPrincipal := pPrincipal * (1+(pIntRate/100));
dbms_output.put_line ('New Principal is '||newPrincipal);
end;
begin
calcInt(100,10);
end;
/
Here is the output:
New Principal is 110
PL/SQL procedure successfully completed.
Python
#py1.txt
def calcInt(pPrincipal, pIntRate):
newPrincipal = pPrincipal * (1+(pIntRate/100))
print ("New Principal is " + str(newPrincipal))
calcInt(100,10)
Executing it produces this:
C:\>python py1.txtNew Principal is 110.00000000000001
This shows an example of a very basic Python function that does not return anything, similar to a PL/SQL procedure. Now that you know the basics, let's explore some more intricate details of the syntax.
Default Value of Parameters
Sometimes you need to pass a default value to a parameter. This value is in effect if the user does not explicitly pass the parameter. Building on the previous procedure, suppose we want to make the parameter pIntRate optional, that is, make it a certain value (such as 5, when the user does not explicitly mention it). In PL/SQL, you mention the parameter this way:
ParameterName DataType := DefaultValue
In Python, it's exactly the same, but since the assignment operator in Python is "=" (not ":="), that's what you need to use. Besides, remember, you don't mention the datatype for parameters. Here is the general syntax:
ParameterName = DefaultValue
You can write the PL/SQL function this way (the changes are in bold):
PL/SQL
--pl2.sql
declare
procedure calcInt (
pPrincipal number,
pIntRate number := 5
) is
newPrincipal number;
begin
newPrincipal := pPrincipal *(1+(pIntRate/100));
dbms_output.put_line('New Principal is '||newPrincipal);
end;
begin
-- don't mention the pIntRate parameter.
-- defaults to 5
calcInt(100);
end;
/
The output is:
New Principal is 105
PL/SQL procedure successfully completed.
Python
#py2.txt
def calcInt(pPrincipal, pIntRate = 5):
newPrincipal = pPrincipal * (1+(pIntRate/100))
print ("New Principal is " + str(newPrincipal))
# Don't pass the 2nd parameter. Defaults to 5
calcInt(100)
Executing it produces this:
C:\>python py2.txt
New Principal is 105.0
If you need to have a string as a default variable, use double quotes:
# py2a.txt
def calcInt(pPrincipal, pIntRate = 5, pAccType = "Checking"):
vIntRate = pIntRate
if (pAccType == 'Savings'):
# eligible for bonus interest
vIntRate = pIntRate + 5
newPrincipal = pPrincipal * (1+(vIntRate/100))
print ("New Principal is " + str(round(newPrincipal)))
calcInt(100,10,'Savings')
Executing it produces this:
C:\>python py2a.txt
New Principal is 115
One important property of functions in Python is that the default values can be variables as well. This is not possible in PL/SQL. For instance, in PL/SQL the following will be illegal:
-- pl4.sql
declare
defIntRate number := 5;
procedure calcInt (
pPrincipal number,
pIntRate number := defIntRate;
) is
...
But it's perfectly valid in Python. Let's see how:
# py4.txt
defIntRate = 5
def calcInt(pPrincipal, pIntRate = defIntRate):
newPrincipal = pPrincipal * (1+(pIntRate/100))
print ("New Principal is " + str(round(newPrincipal)))
calcInt(100)
Another important property of this variable assignment is that the assignment occurs only at the time of the declaration of the function, and is not affected afterwards. Take for instance the following:
# py4a.txt
# Assign the value to the variable
defIntRate = 5
# define the function
def calcInt(pPrincipal, pIntRate = defIntRate):
newPrincipal = pPrincipal * (1+(pIntRate/100))
print ("New Principal is " + str(round(newPrincipal)))
# change the variable value
defIntRate = 10
# call the function
calcInt(100)
What value will be printed? Will the function take the value of pIntRate as 5 or 10?
The answer is it will take the value of pIntRate as 5, not 10. Why? It's because when the function was defined, the value was 5. When the function was called, the value of pIntRate was 10; but that will not be considered. This is a very important property you need to keep in mind when learning Python as a PL/SQL developer. It's a major source of bugs if not taken into consideration.
Positional Parameters
You already know that in PL/SQL you do not have to provide the parameter values in the order in which they were defined in the procedure. You can pass values by specifying the parameter by name. For instance, if a procedure F1 assumes the parameters P1 and P2—in that _order_—you can call the procedure this way with the parameter values Val1 and Val2 respectively:
F1 (Val1, Val2);
But you can also call them with explicit parameter name assignments:
F1 (P2 => Val2, P1 => Val1);
This explicit naming allows you to order the parameters any way you want while calling the procedure. It also allows you to skip some non-mandatory parameters. In Python the equivalent syntax is this:
F1 (P2=Val2, P1=Val1)
So, just the operator "=>" is changed to "=." Let's see examples in both PL/SQL and Python.
PL/SQL
--pl3.sql
declare
procedure calcInt (
pPrincipal number,
pIntRate number := 5
) is
newPrincipal number;
begin
newPrincipal := pPrincipal *(1+(pIntRate/100));
dbms_output.put_line('New Principal is '||newPrincipal);
end;
begin
calcInt(pIntRate=>10, pPrincipal=>100);
end;
/
The output is this:
New Principal is 110
PL/SQL procedure successfully completed.
Python
#py3.txt
def calcInt(pPrincipal, pIntRate = 5, pAccType = "Checking"):
vIntRate = pIntRate
if (pAccType == 'Savings'):
# eligible for bonus interest
vIntRate = pIntRate + 5
newPrincipal = pPrincipal * (1+(vIntRate/100))
print ("New Principal is " + str(round(newPrincipal)))
calcInt(pAccType="Savings", pIntRate=10, pPrincipal=100)
One of the useful cases in PL/SQL is to define a default value only when the value is not explicitly provided. Take for instance, when the user didn't specify anything for the interest rate, and you want the default values to be based on something else, for example, the account type. If the account type is Savings (the default), the interest rate should should be 10 percent; otherwise, it should be 5 percent. Here is how you will need to write the function:
-- pl3b.sql
declare
procedure calcInt (
pPrincipal number,
pIntRate number := null,
pAccType varchar2 := 'Savings'
) is
newPrincipal number;
vIntRate number;
begin
if (pAccType = 'Savings') then
if (pIntRate is null) then
vIntRate := 10;
else
vIntRate := pIntRate;
end if;
else
if (pIntRate is null) then
vIntRate := 5;
else
vIntRate := pIntRate;
end if;
end if;
newPrincipal := pPrincipal * (1+(vIntRate/100));
dbms_output.put_line('New Principal is '|| newPrincipal);
end;
begin
calcInt(100);
calcInt(100, pAccType => 'Checking');
end;
/
The equivalent of this:
pIntRate number := null,
in Python is this:
pIntRate = None
(Note the capitalization of None). Here is the complete Python code:
# py3b.txt
def calcInt(pPrincipal, pIntRate = None, pAccType = "Savings"):
vIntRate = pIntRate
if (pAccType == 'Savings'):
if (pIntRate == None):
vIntRate = 10
else:
vIntRate = pIntRate
else:
if (pIntRate == None):
vIntRate = 5
else:
vIntRate = pIntRate
newPrincipal = pPrincipal * (1+(vIntRate/100))
print ("New Principal is " + str(round(newPrincipal)))
calcInt(100)
calcInt(100, 20)
calcInt(100, pAccType="Checking")
Here is the output when we execute the code:
C:\>python py3b.txtNew Principal is 110New Principal is 120New Principal is 105
Returning Values
So far we have talked about the equivalent procedures in PL/SQL, which do not return anything. In contrast, functions in PL/SQL return a value. Here is a simple example of a function that returns the interest rate for the account type, which is the parameter passed to it:
--pl5
declare
function getIntRate
(
pAccType in varchar2
)
return number
is
vRate number;
begin
case pAccType
when 'Savings' then vRate := 10;
when 'Checking' then vRate := 5;
when 'MoneyMarket' then vRate := 15;
end case;
return vRate;
end;
begin
dbms_output.put_line('Int Rate = '||getIntRate('Savings'));
dbms_output.put_line('Int Rate = '||getIntRate('Checking'));
dbms_output.put_line('Int Rate = '||getIntRate('MoneyMarket'));
end;
/
The equivalent of this:
return vRate;
in Python, fortunately, is exactly the same:
return vRate
Let's see how we can write the Python code. Remember, there is no CASE equivalent in Python; so we have to resort to the if ... elif ...else construct.
# py5.txt
def getIntRate (pAccType):
if (pAccType == 'Savings'):
vIntRate = 10
elif (pAccType == 'Checking'):
vIntRate = 5
elif (pAccType == 'MoneyMarket'):
vIntRate = 15
return vIntRate
print('Int Rate = ', getIntRate ('Savings'))
print('Int Rate = ', getIntRate ('Checking'))
print('Int Rate = ', getIntRate ('MoneyMarket'))
Executing it produces this:
C:\>python py5.txt
Int Rate = 10
Int Rate = 5
Int Rate = 15
Documentation
When you write a lot of functions, you might lose track of what each one does. Others may be even more confused. You can add documentation for the function to show what the function does. In PL/SQL, you use this syntax:
/* ... documentation ... */
In Python, the equivalent is to put lines between a pair of three double quotes. Anything inside the pair of three double quotes will be considered documentation.
# py6.txt
def myFunc (pParam1, pParam2):
""""Version : 2.0
Purpose : The purpose comes here
Created : mm/dd/yyyy
Author : XXX
Last Changed : mm/dd/yyyy
Change History:
Date Ver Made By Description
------- --- ------- -----------------------------
mm/dd/yy 1.0 XXX Created
mm/dd/yy 2.0 yyy Changed something
"""
vInt = pParam1 + pParam2
return vInt
print(myFunc(1,2))
But, unlike PL/SQL, which interprets the text between /* and */ as comments, Python interprets this correctly as function documentation and not as mere comments. There is a special attribute called __doc__ of any function that holds this documentation, and if it is defined it can be called later. Here is how you can reference this attribute:
# py7.txt
def myFunc (pParam1, pParam2):
"""Version : 2.0
Purpose : The purpose comes here
Created : mm/dd/yyyy
Author : XXX
Last Changed : mm/dd/yyyy
Change History:
Date Ver Made By Description
------- --- ------- -----------------------------
mm/dd/yy 1.0 XXX Created
mm/dd/yy 2.0 yyy Changed something
"""
vInt = pParam1 + pParam2
return vInt
print(myFunc.__doc__)
If we execute it, we get this:
C:\>python py7.txtVersion : 2.0 Purpose : The purpose comes here Created : mm/dd/yyyy Author : XXX Last Changed : mm/dd/yyyy Change History: Date Ver Made By Description ------- --- ------- ----------------------------- mm/dd/yy 1.0 XXX Created mm/dd/yy 2.0 yyy Changed something
The documentation is helpful in many ways for documenting your programs and checking them later. The documentation comes in very handy when you write modules and classes (explained later in this installment).
Annotations
PL/SQL functions are strongly and unmistakably typed; that is, you have given the datatype of input parameters and return values explicitly at the time of defining the function. When you check the function later, all you need to do is to describe the function in SQL*Plus as shown below:
SQL> describe myFunc
However, there is no such facility in Python. What if you need to know what the datatypes are? There is a special attribute called annotations for functions. This allows you to document the datatypes of the input parameters and return values. This is the generalized syntax:
def FunctionName (ParameterName: DataType, ParameterName: DataType) -> ReturnDataType
When you want to see the annotations, a special attribute of the function, __annotations__, holds the value. Let's see a simple example where a function accepts two parameters of int type and returns a value of int type as well.
# py8.txt
def myFunc (pParam1 : int, pParam2 : int) -> int:
vInt = pParam1 + pParam2
return vInt
print(myFunc(1,2))
print("Let's see the annotations")
print(myFunc.__annotations__)
When we execute it, we get this:
C:\>python py8.txt3Let's see the annotations{'pParam1': <class 'int'>, 'pParam2': <class 'int'>, 'return': <class 'int'>}
It shows the class, which is analogous (but not exactly the same as) the datatype, of the input parameters (int in this case) and the return value (also int). However, please note a very important property of annotations. This is merely a suggestion; nothing else. It's not binding. There is nothing that prevents you from changing the datatype of the Python code after declaring the parameter annotations. Here is a modified version of the code shown earlier, but instead of integers we pass strings.
# py8a.txt
def myFunc (pParam1 : int, pParam2 : int) -> int:
vInt = pParam1 + pParam2
return vInt
print(myFunc("My","World"))
print("Let's see the annotations")
print(myFunc.__annotations__)
When you execute it, this is the output:
C:\>python py8a.txtMyWorldLet's see the annotations{'pParam1': <class 'int'>, 'return': <class 'int'>, 'pParam2': <class 'int'>}
Not only does it execute just fine; it's grossly misleading. Its annotations show that the input and outputs are int; but they are actually strings. So, annotations are merely suggestive, not definitive, and—do not forget—optional.
Global and Local Variables
In PL/SQL packages, or even in procedures, you can define variables where the scope of that variable is important. Take this simple procedure for instance. Here a variable named v1 is defined in two places—inside and outside the function—and assigned values in two places. Which value is shown where? Let's see:
-- pl9.sql
declare
v1 number;
function myFunc (
p1 number,
p2 number
)
return number
is
v1 number;
begin
v1 := p1+p2;
dbms_output.put_line('inside the function');
dbms_output.put_line ('v1='||v1);
return v1;
end;
begin
dbms_output.put_line('outside the function');
v1 := 10;
dbms_output.put_line ('v1='||v1);
dbms_output.put_line ('the output of the function is '||myfunc(20,30));
end;
/
Executing it produces this:
outside the function
v1=10
inside the function
v1=50
the output of the function is 50
The values are differently assigned and maintained. So, when you reference a variable named v1, you have to pay attention to its scope, that is, where it is defined. That is where the value will be changed. In the case above, values set inside the function will be different from the value outside. It's exactly the same way in Python:
#py9.txt
def myFunc (p1, p2):
v1 = p1 + p2
print('inside the function')
print ('v1 = ', v1)
return v1
print('outside the function')
v1 = 10
print('v1=', v1)
print('The output of the function is ',myFunc(20,30))
Executing it produces this:
C:\>python py9.txt
outside the function
v1= 10
inside the function
v1 = 50
The output of the function is 50
In fact, the scope of the local variables is strictly inside the function. It's completely independent; so they can be even different datatypes.
#py9a.txt
def myFunc (p1, p2):
v1 = p1 + p2
print('inside the function')
print ('local v1 = ', v1)
return v1
print('outside the function')
v1 = 10
print('global v1=', v1)
p1 = 'Hello'
p2 = 'World'
print('The output of the function is ',myFunc(20,30))
print('Global p1 and p2 = ',p1,p2,sep=' ')
Executing it produces this:
C:\>python py9a.txt
outside the function
global v1= 10
inside the function
local v1 = 50
The output of the function is 50
Global p1 and p2 = Hello World
inside the function
v1 = 50
The output of the function is 50
The scope is something you should pay attention to. The scope of a variable is determined at the time of its first appearance. Since there is no such thing called "declaration of variables" (variables are declared at the time of the assignment of values), whether a variable is local or global depends on when it was first referenced. Consider the following Python code.
#py10a.txt
1 v1 = 100
2 def myFunc (p1, p2):
3 print ('v1=',v1)
4 v2 = p1 + p2
5 print('inside the function')
6 print ('v2=', v2)
7 return v2
8
9 print('The output of the function is ',myFunc(20,30))
What do you think the output will be? On line 9, we call the function, which in turn goes to line 2 where the function is declared. Immediately inside the function, we print the variable v1; but wait, there is no variable v1 defined inside the function at that point. In fact, there is no variable called v1 in that function. Will this print statement (line 3) work, then? Let's see:
C:\>python py10a.txt
v1= 100
inside the function
v2= 50
The output of the function is 50
It may defy logic, but it did work. The fact is that there is no variable called v1 at all in the function; therefore, whenever there is a reference to variable named v1, Python will check if there is a global variable of that name. In this case, there is a global variable called v1 (line 1); therefore, Python assumes that's what you meant on line 3.
However, what happens if we have a local variable called v1 as well in the function?
#py10b.txt
1 v1 = 100
2 def myFunc (p1, p2):
3 print ('v1=',v1)
4 v1 = p1 + p2
5 print('inside the function')
6 print ('v1=', v1)
7 return v1
8
9 print('The output of the function is ',myFunc(20,30))
When we execute this code, here is the output:
C:\>python py10b.txt
Traceback (most recent call last):
File "py10b.txt", line 9, in <module>
print('The output of the function is ',myFunc(20,30))
File "py10b.txt", line 3, in myFunc
print ('v1=',v1)
UnboundLocalError: local variable 'v1' referenced before assignment
What happened? Why didn't it work this time? The error is pretty clear; it's in line 3.
print ('v1=',v1)
UnboundLocalError: local variable 'v1' referenced before assignment
This is because v1 is a local variable in this function, defined in line 4. However, before we defined it, we referenced it in line 3; hence, the error. Pay particular attention to this behavior. In summary, if you reference a local variable with the same name as a global variable, you must have declared a local variable before referencing it in the function. If you don't have have a local variable of the same name, a reference to that will not fail; it will succeed. The global variable will be referenced instead.
Argument Array
Suppose you are writing a small function to add all the numbers. There is a sum function already available; but assume it wasn't and you write your own function. The problem is that you have to accept a series of numbers as arguments and—worse yet—the number of arguments is not known at the declaration time of the function. For instance, the name of function is mySum. To get the sum of three numbers—1, 2 and 3—you would call this:
mySum (1,2,3)
It has three arguments. To get the sum of four numbers—1, 2, 3, and 4—you would call this:
mySum (1,2,3,4)
This time, there are four arguments, and so on. The number of arguments is not known at the time of the definition of the function. How would you write the function then? This is something for which there is no PL/SQL equivalent. Readers familiar with C language may remember pointers by reference. C accepts an array of numbers or strings. However, you have to use an array, not pure primitive datatypes such as numbers. Python excels in this case. It allows an array of arguments as well, but since it doesn't require a prior declaration, arguments passed will be implicitly be treated as an array. The trick is to prefix the parameter name with an asterisk Let's see an example of the mySum function that accepts any amount of numbers and returns the sum:
#py11.txt
1 def mySum(*numList):
2 tot = 0
3 for n in numList:
4 print n
5 tot = tot + n
6 return tot
7
8 print ('Total=',mySum(1,2,3))
Notice the * before the parameter name, numList, which indicates that it is an array. Therefore in line 3, we can extract each element of the array and print them as a demo (line 4). We add each element to a variable called tot and finally return the variable. When we execute it, we get this:
C:\>python py11.txt
1
2
3
Total= 6
You can pass any number of parameters in this case. This is a very powerful feature of Python functions.
Named Argument
Remember passing options in a UNIX shell script? You can use something as simple as argument1=value1 argument2=value2, and so on. Sometimes you may need to write a similar program in Python as well. Not only will you not know the number of arguments in advance; you may not know even the argument names while declaring the parameters as well. Once again, there is no equivalency in PL/SQL. Fortunately it's a breeze in Python. You can prefix a double asterisk (**) before the name of the parameter, which indicates a dictionary collection in Python. Remember the dictionary datatype? If you don't, go back to Part 1 of this series to refresh your memory. In summary, a dictionary in Python is a set of key-value pairs.
Here is a small program where we accept a list of key-value pairs and check if they are twins, that is, whether the value is the same as the key.
#py12.txt
1 def checkTwins (**wordList):
2 words = sorted(wordList.keys())
3 for w in words:
4 print (w,':',wordList[w])
5 if (w==wordList[w]):
6 print('Twins Found')
7
8 checkTwins (
9 firstWord="first",
10 secondWord="second",
11 thirdWord="thirdWord"
12 )
We put the entire dictionary in a variable called words (line 2) after sorting it (sorting is not necessary; but I did it to refresh your memory on the sorted function). Then we iterate over each pair in that variable (line 3) and print each pair (line 4). In line 5, we compare the value and the key and if they are the same, we print the fact that they are twins. When we execute the program, we get the following output:
C:\>python py12.txt
firstWord : first
secondWord : second
thirdWord : thirdWord
Twins Found
Notice how the keys and their corresponding values are printed.
Stored Functions
So far, we have talked about defining functions right there in the Python script, analogous to declaring inline PL/SQL functions and procedures. However, if you use specific code a lot, you may want to create a stored procedure (or function) that is stored in the database and called as often as needed without redefining it. In Python, you can also store functions; but since there is no database, these are stored in files in groups called modules. A module is analogous to a stored package in PL/SQL. Just like packages have functions and procedures, modules contain functions.
Let's see an example of a simple module called intModule that has two functions: getIntRate() and calcInt(). To create this module, simply create a file named intModule.py with the following content:
#intModule.py
def getIntRate (pAccType):
if (pAccType in ('Savings','S')):
vIntRate = 10
elif (pAccType in ('Checking','C')):
vIntRate = 5
elif (pAccType in ('MoneyMarket','M')):
vIntRate = 15
else:
vIntRate = 0
return vIntRate
def calcInt (pPrincipal, pAccType):
vIntRate = getIntRate (pAccType)
vInt = pPrincipal * vIntRate / 100
return vInt
vAllowed = True
This is the "module," the equivalent of a package in PL/SQL. The package name equivalent is the module name, which is intModule (note the case). There is no such thing called "creating" the package. Python p