Archive for the ‘Python Developer’ tag
Using Python’s getopt
A couple of my students wanted me to write a switch and parameter handler for Python scripts. I wrote it just to show them it’s possible but I also show them how to do it correctly with the Python getopt library, which was soft-deprecated in Python 3.13 and replaced by the Python argparse library. The debate is which one I show you first in the blog.
This is the getops.py script that uses Python’s getopt library. There is a small trick to the options and long options values. You append a colon (:) to the option when it has a value, and append an equal (=) to the long option when it has a value.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | #!/usr/bin/python # Import libraries. import getopt, sys import mysql.connector from mysql.connector import errorcode # Define local function. def help(): # Declare display string. display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" # Return string. return display # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ display = False log = [] output_file = '' query_file = '' verbose = False opts = "ho:q:v" long_opts = ["help","output=","query=","verbose"] # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # Use a try-except block. # ============================================================ try: # Assign the results of the getopt function. params, values = getopt.getopt(args, opts, long_opts) # Loop through the parameters. for curr_param, curr_value in params: if curr_param in ("-h","--help"): print(help()) elif curr_param in ("-o","--output"): output_file = curr_value elif curr_param in ("-q","--query"): query_file = curr_value elif curr_param in ("-v","--verbose"): verbose = True # Append entry to log. log.append('[' + curr_param + '][' + curr_value + ']') # Print verbose parameter handling. if verbose: print(" Parameter Diagnostics\n-------------------------") for i in log: print(i) # Exception block. except getopt.GetoptError as e: # output error, and return with an error code print (str(e)) |
You can run the program in Linux or Unix with the following syntax provided that you’ve already set the parameters to 755. That means granting the file owner with read, write, and execute privileges, and group and other with read and execute privileges.
./getopts.py -h -o output.txt -q query.sql -v |
It would return the following:
Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+ Parameter Diagnostics ------------------------- [-h][] [-o][output.txt] [-q][query.sql] [-v][] |
If you didn’t notice, I also took the opportunity to write the help display in such a way that a maintenance programmer could add another switch or parameter easily. This way the programmer only needs to add a new row of text and add an elif statement with the new switch or parameter.
I think using Python’s getopt library is the cleanest and simplest way to implement switch and parameter handling, after all it’s the basis for so many C derived libraries. However, if you must write your own, below is an approach that would work:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | #!/usr/bin/python # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ help = False display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" log = [] output = '' query = '' verbose = False # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # If one or more args exists and the first one is an # a string that can cast to an int, convert it to an int, # assign it to a variable, and ignore any other args # in the list. # ============================================================ if len(args) > 1 and args[0].isdigit(): powerIn = int(args[0]) # Check for switches and parameters. if isinstance(args,list) and len(args) >= 1: # Set the limit of switches and parameters. argc = len(args) # Enumerate through switches first and then parameters. for i in range(argc): if args[i][0] == '-': # Evaluate switches and ignore any parameter value. if args[i] in ['-h','--help']: help = True # Append entry to log. log.append('[' + str(args[i]) + ']') elif args[i] in ['-v','--verbose']: verbose = True # Append entry to log. log.append('[' + str(args[i]) + ']') # Evaluate parameters. elif i < argc and not args[i+1][0] == '-': if args[i] in ['-q','--query']: query = args[i+1] elif args[i] in ['-o','--output']: output = args[i+1] # Append entry to log. log.append('[' + str(args[i]) + '][' + args[i+1] + ']') else: continue continue # Print the help display when if help: print(display) # Print the parameter handling collected in the log variable. if verbose: for i in log: print(i) |
As you can see from the example, I didn’t give it too much effort. I think it should prove you should use the approach adopted by the general Python community.
Parametric Queries
In 2021, I wrote a MySQL example for my class on the usefulness of Common Table Expressions (CTEs). When discussing the original post, I would comment on how you could extend the last example to build a parametric reporting table.
Somebody finally asked for a concrete example. So, this explains how to build a sample MySQL parametric query by leveraging a filter cross join and tests the parameter use with a Python script.
You can build this in any database you prefer but I used a studentdb database with the sakila sample database installed. I’ve granted privileges to both databases to the student user. The following SQL is required for the example:
-- Conditionally drop the levels table. DROP TABLE IF EXISTS levels; -- Create the levels list. CREATE TABLE levels ( level_id int unsigned primary key auto_increment , parameter_set enum('Three','Five') , description varchar(20) , min_roles int , max_roles int ); -- Insert values into the list table. INSERT INTO levels ( parameter_set , description , min_roles , max_roles ) VALUES ('Three','Hollywood Star', 30, 99999) ,('Three','Prolific Actor', 20, 29) ,('Three','Newcommer',1,19) ,('Five','Newcommer',1,9) ,('Five','Junior Actor',10,19) ,('Five','Professional Actor',20,29) ,('Five','Major Actor',30,39) ,('Five','Hollywood Star',40,99999); |
The sample lets you use the three or five value labels while filtering on any partial full_name value as the result of the query below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Query the data. WITH actors AS (SELECT a.actor_id , a.first_name , a.last_name , COUNT(*) AS num_roles FROM sakila.actor a INNER JOIN sakila.film_actor fa ON a.actor_id = fa.actor_id GROUP BY actor_id) SELECT CONCAT(a.last_name,', ',a.first_name) full_name , l.description , a.num_roles FROM actors a CROSS JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles AND l.parameter_set = 'Five' AND a.last_name LIKE CONCAT('H','%') ORDER BY a.last_name , a.first_name; |
They extends a concept exercise found in Chapter 9 on subqueries in Alan Beaulieu’s Learning SQL book.
This is the parametric Python program, which embeds the function locally (to make it easier for those who don’t write a lot of Python). You could set the PYTHONPATH to a relative src directory and import your function if you prefer.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | #!/usr/bin/python # Import the libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Define function to check and replace arguments. def check_replace(argv): # Set defaults for incorrect parameter values. defaults = ("Three","_") # Declare empty list variables. inputs = [] args = () # Check whether or not parameters exist after file name. if isinstance(argv,list) and len(argv) != 0: # Check whether there are at least two parameters. if len(argv) >= 2: # Loop through available command-line arguments. for element in argv: # Check first of two parameter values and substitute # default value if input value is an invalid option. if len(inputs) == 0 and (element in ('Three','Five')) or \ len(inputs) == 1 and (isinstance(element,str)): inputs.append(element) elif len(inputs) == 0: inputs.append(defaults[0]) elif len(inputs) == 1: inputs.append(defaults[1]) # Assign arguments to parameters. args = (inputs) # Check whether only one parameter value exists. elif len(argv) == 1 and (argv[0] in ('Three','Five')): args = (argv[0],"_") # Assume only one parameter is valid and substitute an # empty string as the second parameter. else: args = (defaults[0],"_") # Substitute defaults when missing parameters. else: args = defaults # Return parameters as a tuple. return args # ============================================================ # Assign command-line argument list to variable by removing # the program file name. # ============================================================ params = check_replace(sys.argv[1:]) # ============================================================ # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.first_name " " , a.last_name " " , COUNT(*) AS num_roles " " FROM sakila.actor a INNER JOIN sakila.film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.first_name " " , a.last_name ) " " SELECT CONCAT(a.last_name,', ',a.first_name) AS full_name " " , l.description " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " AND l.parameter_set = %s " " AND a.last_name LIKE CONCAT(%s,'%') " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query, params) # Display the rows returned by the query. for (full_name, description, num_roles) in cursor: print('{0} is a {1} with {2} films.'.format( full_name.title() , description.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.
VSCode & $PYTHONPATH
About 4 years ago, I demonstrated how to develop Python functions with a relative src directory in this old blog post. I thought it might be possible to do with VSCode. Doing a bit of research, it appeared all that was required was adding the PythonPath to VSCode’s Python settings in:
/home/student/.vscode/extensions/ms-python.python-2023.22.0/pythonFiles/.vscode/settings.json |
It contained:
{"files.exclude":{"**/__pycache__/**":true,"**/**/*.pyc":true},"python.formatting.provider":"black"} |
I added a configuration for the PYTHONPATH, as shown:
{"files.exclude":{"**/__pycache__/**":true,"**/**/*.pyc":true},"python.formatting.provider":"black","python.pythonPath": "/home/student/Lib"} |
As you can tell from the embedded VSCode Terminal output below, the PYTHONPATH is not found. You can manually enter it and retest your code successfully. There is no way to use a relative PYTHONPATH like the one you can use from an shell environment file.
This is the hello_whom5.py code:
#!/usr/bin/python # Import the basic sys library. import sys from input import parse_input # Assign command-line argument list to variable. whom = parse_input(sys.argv) # Check if string isn't empty and use dynamic input. if len(whom) > 0: # Print dynamic hello salutation. print("Hello " + whom + "!\n") else: # Print default saluation. print("Hello World!") |
This is the input.py library module:
# Parse a list and return a whitespace delimited string. def parse_input(input_list): # Assign command-line argument list to variable. cmd_list = input_list[1:] # Declare return variable. result = "" # Check whether or not their are parameters beyond the file name. if isinstance(input_list,list) and len(input_list) != 0: # Loop through the command-line argument list and print it. for element in cmd_list: if len(result) == 0: result = element else: result = result + " " + element # Return result variable as string. return result |
This is the Terminal output from VSCode:
student@student-virtual-machine:~$ /bin/python /home/student/Code/python/hello_whom5.py Traceback (most recent call last): File "/home/student/Code/python/hello_whom5.py", line 5, in <module> from input import parse_input ModuleNotFoundError: No module named 'input' student@student-virtual-machine:~$ export set PYTHONPATH=/home/student/Lib student@student-virtual-machine:~$ /bin/python /home/student/Code/python/hello_whom5.py Hello World! student@student-virtual-machine:~$ /bin/python /home/student/Code/python/hello_whom5.py Katniss Everdeen Hello Katniss Everdeen! student@student-virtual-machine:~$ |
The VSCode image for the test follows below:
As always, I hope this helps somebody working the same issue. However, if somebody has a better solution, please let me know.
OracleDB Python Tutorial 1
This shows you how to get Python working with the Oracle Database 23c in Docker or Podman on Ubuntu. You can find useful connection strings for this in Oracle Database Free Get Started.
- First step requires you to install the pip3/span> utility on Ubuntu.
sudo apt install -y python3-pip
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: binutils binutils-common binutils-x86-64-linux-gnu build-essential dpkg-dev fakeroot g++ g++-11 gcc gcc-11 javascript-common libalgorithm-diff-perl libalgorithm-diff-xs-perl libalgorithm-merge-perl libasan6 libbinutils libc-dev-bin libc-devtools libc6-dev libcc1-0 libcrypt-dev libctf-nobfd0 libctf0 libdpkg-perl libexpat1-dev libfakeroot libfile-fcntllock-perl libgcc-11-dev libitm1 libjs-jquery libjs-sphinxdoc libjs-underscore liblsan0 libnsl-dev libpython3-dev libpython3.10-dev libquadmath0 libstdc++-11-dev libtirpc-dev libtsan0 libubsan1 linux-libc-dev lto-disabled-list make manpages-dev python3-dev python3-distutils python3-setuptools python3-wheel python3.10-dev rpcsvc-proto zlib1g-dev Suggested packages: binutils-doc debian-keyring g++-multilib g++-11-multilib gcc-11-doc gcc-multilib autoconf automake libtool flex bison gcc-doc gcc-11-multilib gcc-11-locales apache2 | lighttpd | httpd glibc-doc bzr libstdc++-11-doc make-doc python-setuptools-doc The following NEW packages will be installed: binutils binutils-common binutils-x86-64-linux-gnu build-essential dpkg-dev fakeroot g++ g++-11 gcc gcc-11 javascript-common libalgorithm-diff-perl libalgorithm-diff-xs-perl libalgorithm-merge-perl libasan6 libbinutils libc-dev-bin libc-devtools libc6-dev libcc1-0 libcrypt-dev libctf-nobfd0 libctf0 libdpkg-perl libexpat1-dev libfakeroot libfile-fcntllock-perl libgcc-11-dev libitm1 libjs-jquery libjs-sphinxdoc libjs-underscore liblsan0 libnsl-dev libpython3-dev libpython3.10-dev libquadmath0 libstdc++-11-dev libtirpc-dev libtsan0 libubsan1 linux-libc-dev lto-disabled-list make manpages-dev python3-dev python3-distutils python3-pip python3-setuptools python3-wheel python3.10-dev rpcsvc-proto zlib1g-dev 0 upgraded, 53 newly installed, 0 to remove and 9 not upgraded. Need to get 62.2 MB of archives. After this operation, 220 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 binutils-common amd64 2.38-4ubuntu2.3 [222 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libbinutils amd64 2.38-4ubuntu2.3 [662 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libctf-nobfd0 amd64 2.38-4ubuntu2.3 [107 kB] Get:4 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libctf0 amd64 2.38-4ubuntu2.3 [103 kB] Get:5 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 binutils-x86-64-linux-gnu amd64 2.38-4ubuntu2.3 [2,327 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 binutils amd64 2.38-4ubuntu2.3 [3,190 B] Get:7 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libc-dev-bin amd64 2.35-0ubuntu3.5 [20.3 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 linux-libc-dev amd64 5.15.0-91.101 [1,332 kB] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libcrypt-dev amd64 1:4.4.27-1 [112 kB] Get:10 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 rpcsvc-proto amd64 1.4.2-0ubuntu6 [68.5 kB] Get:11 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libtirpc-dev amd64 1.3.2-2ubuntu0.1 [192 kB] Get:12 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libnsl-dev amd64 1.3.0-2build2 [71.3 kB] Get:13 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libc6-dev amd64 2.35-0ubuntu3.5 [2,098 kB] Get:14 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libcc1-0 amd64 12.3.0-1ubuntu1~22.04 [48.3 kB] Get:15 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libitm1 amd64 12.3.0-1ubuntu1~22.04 [30.2 kB] Get:16 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libasan6 amd64 11.4.0-1ubuntu1~22.04 [2,282 kB] Get:17 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 liblsan0 amd64 12.3.0-1ubuntu1~22.04 [1,069 kB] Get:18 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libtsan0 amd64 11.4.0-1ubuntu1~22.04 [2,260 kB] Get:19 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libubsan1 amd64 12.3.0-1ubuntu1~22.04 [976 kB] Get:20 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libquadmath0 amd64 12.3.0-1ubuntu1~22.04 [154 kB] Get:21 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libgcc-11-dev amd64 11.4.0-1ubuntu1~22.04 [2,517 kB] Get:22 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 gcc-11 amd64 11.4.0-1ubuntu1~22.04 [20.1 MB] Get:23 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 gcc amd64 4:11.2.0-1ubuntu1 [5,112 B] Get:24 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libstdc++-11-dev amd64 11.4.0-1ubuntu1~22.04 [2,101 kB] Get:25 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 g++-11 amd64 11.4.0-1ubuntu1~22.04 [11.4 MB] Get:26 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 g++ amd64 4:11.2.0-1ubuntu1 [1,412 B] Get:27 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 make amd64 4.3-4.1build1 [180 kB] Get:28 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libdpkg-perl all 1.21.1ubuntu2.2 [237 kB] Get:29 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 lto-disabled-list all 24 [12.5 kB] Get:30 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 dpkg-dev all 1.21.1ubuntu2.2 [922 kB] Get:31 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 build-essential amd64 12.9ubuntu3 [4,744 B] Get:32 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libfakeroot amd64 1.28-1ubuntu1 [31.5 kB] Get:33 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 fakeroot amd64 1.28-1ubuntu1 [60.4 kB] Get:34 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 javascript-common all 11+nmu1 [5,936 B] Get:35 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libalgorithm-diff-perl all 1.201-1 [41.8 kB] Get:36 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libalgorithm-diff-xs-perl amd64 0.04-6build3 [11.9 kB] Get:37 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libalgorithm-merge-perl all 0.08-3 [12.0 kB] Get:38 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libc-devtools amd64 2.35-0ubuntu3.5 [28.9 kB] Get:39 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libexpat1-dev amd64 2.4.7-1ubuntu0.2 [147 kB] Get:40 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libfile-fcntllock-perl amd64 0.22-3build7 [33.9 kB] Get:41 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libjs-jquery all 3.6.0+dfsg+~3.5.13-1 [321 kB] Get:42 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libjs-underscore all 1.13.2~dfsg-2 [118 kB] Get:43 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libjs-sphinxdoc all 4.3.2-1 [139 kB] Get:44 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 zlib1g-dev amd64 1:1.2.11.dfsg-2ubuntu9.2 [164 kB] Get:45 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libpython3.10-dev amd64 3.10.12-1~22.04.3 [4,762 kB] Get:46 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libpython3-dev amd64 3.10.6-1~22.04 [7,166 B] Get:47 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 manpages-dev all 5.10-1ubuntu1 [2,309 kB] Get:48 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3.10-dev amd64 3.10.12-1~22.04.3 [507 kB] Get:49 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3-distutils all 3.10.8-1~22.04 [139 kB] Get:50 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3-dev amd64 3.10.6-1~22.04 [26.0 kB] Get:51 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3-setuptools all 59.6.0-1.2ubuntu0.22.04.1 [339 kB] Get:52 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 python3-wheel all 0.37.1-2ubuntu0.22.04.1 [32.0 kB] Get:53 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 python3-pip all 22.0.2+dfsg-1ubuntu0.4 [1,305 kB] Fetched 62.2 MB in 35s (1,781 kB/s) Extracting templates from packages: 100% Selecting previously unselected package binutils-common:amd64. (Reading database ... 221117 files and directories currently installed.) Preparing to unpack .../00-binutils-common_2.38-4ubuntu2.3_amd64.deb ... Unpacking binutils-common:amd64 (2.38-4ubuntu2.3) ... Selecting previously unselected package libbinutils:amd64. Preparing to unpack .../01-libbinutils_2.38-4ubuntu2.3_amd64.deb ... Unpacking libbinutils:amd64 (2.38-4ubuntu2.3) ... Selecting previously unselected package libctf-nobfd0:amd64. Preparing to unpack .../02-libctf-nobfd0_2.38-4ubuntu2.3_amd64.deb ... Unpacking libctf-nobfd0:amd64 (2.38-4ubuntu2.3) ... Selecting previously unselected package libctf0:amd64. Preparing to unpack .../03-libctf0_2.38-4ubuntu2.3_amd64.deb ... Unpacking libctf0:amd64 (2.38-4ubuntu2.3) ... Selecting previously unselected package binutils-x86-64-linux-gnu. Preparing to unpack .../04-binutils-x86-64-linux-gnu_2.38-4ubuntu2.3_amd64.deb ... Unpacking binutils-x86-64-linux-gnu (2.38-4ubuntu2.3) ... Selecting previously unselected package binutils. Preparing to unpack .../05-binutils_2.38-4ubuntu2.3_amd64.deb ... Unpacking binutils (2.38-4ubuntu2.3) ... Selecting previously unselected package libc-dev-bin. Preparing to unpack .../06-libc-dev-bin_2.35-0ubuntu3.5_amd64.deb ... Unpacking libc-dev-bin (2.35-0ubuntu3.5) ... Selecting previously unselected package linux-libc-dev:amd64. Preparing to unpack .../07-linux-libc-dev_5.15.0-91.101_amd64.deb ... Unpacking linux-libc-dev:amd64 (5.15.0-91.101) ... Selecting previously unselected package libcrypt-dev:amd64. Preparing to unpack .../08-libcrypt-dev_1%3a4.4.27-1_amd64.deb ... Unpacking libcrypt-dev:amd64 (1:4.4.27-1) ... Selecting previously unselected package rpcsvc-proto. Preparing to unpack .../09-rpcsvc-proto_1.4.2-0ubuntu6_amd64.deb ... Unpacking rpcsvc-proto (1.4.2-0ubuntu6) ... Selecting previously unselected package libtirpc-dev:amd64. Preparing to unpack .../10-libtirpc-dev_1.3.2-2ubuntu0.1_amd64.deb ... Unpacking libtirpc-dev:amd64 (1.3.2-2ubuntu0.1) ... Selecting previously unselected package libnsl-dev:amd64. Preparing to unpack .../11-libnsl-dev_1.3.0-2build2_amd64.deb ... Unpacking libnsl-dev:amd64 (1.3.0-2build2) ... Selecting previously unselected package libc6-dev:amd64. Preparing to unpack .../12-libc6-dev_2.35-0ubuntu3.5_amd64.deb ... Unpacking libc6-dev:amd64 (2.35-0ubuntu3.5) ... Selecting previously unselected package libcc1-0:amd64. Preparing to unpack .../13-libcc1-0_12.3.0-1ubuntu1~22.04_amd64.deb ... Unpacking libcc1-0:amd64 (12.3.0-1ubuntu1~22.04) ... Selecting previously unselected package libitm1:amd64. Preparing to unpack .../14-libitm1_12.3.0-1ubuntu1~22.04_amd64.deb ... Unpacking libitm1:amd64 (12.3.0-1ubuntu1~22.04) ... Selecting previously unselected package libasan6:amd64. Preparing to unpack .../15-libasan6_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking libasan6:amd64 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package liblsan0:amd64. Preparing to unpack .../16-liblsan0_12.3.0-1ubuntu1~22.04_amd64.deb ... Unpacking liblsan0:amd64 (12.3.0-1ubuntu1~22.04) ... Selecting previously unselected package libtsan0:amd64. Preparing to unpack .../17-libtsan0_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking libtsan0:amd64 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package libubsan1:amd64. Preparing to unpack .../18-libubsan1_12.3.0-1ubuntu1~22.04_amd64.deb ... Unpacking libubsan1:amd64 (12.3.0-1ubuntu1~22.04) ... Selecting previously unselected package libquadmath0:amd64. Preparing to unpack .../19-libquadmath0_12.3.0-1ubuntu1~22.04_amd64.deb ... Unpacking libquadmath0:amd64 (12.3.0-1ubuntu1~22.04) ... Selecting previously unselected package libgcc-11-dev:amd64. Preparing to unpack .../20-libgcc-11-dev_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking libgcc-11-dev:amd64 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package gcc-11. Preparing to unpack .../21-gcc-11_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking gcc-11 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package gcc. Preparing to unpack .../22-gcc_4%3a11.2.0-1ubuntu1_amd64.deb ... Unpacking gcc (4:11.2.0-1ubuntu1) ... Selecting previously unselected package libstdc++-11-dev:amd64. Preparing to unpack .../23-libstdc++-11-dev_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking libstdc++-11-dev:amd64 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package g++-11. Preparing to unpack .../24-g++-11_11.4.0-1ubuntu1~22.04_amd64.deb ... Unpacking g++-11 (11.4.0-1ubuntu1~22.04) ... Selecting previously unselected package g++. Preparing to unpack .../25-g++_4%3a11.2.0-1ubuntu1_amd64.deb ... Unpacking g++ (4:11.2.0-1ubuntu1) ... Selecting previously unselected package make. Preparing to unpack .../26-make_4.3-4.1build1_amd64.deb ... Unpacking make (4.3-4.1build1) ... Selecting previously unselected package libdpkg-perl. Preparing to unpack .../27-libdpkg-perl_1.21.1ubuntu2.2_all.deb ... Unpacking libdpkg-perl (1.21.1ubuntu2.2) ... Selecting previously unselected package lto-disabled-list. Preparing to unpack .../28-lto-disabled-list_24_all.deb ... Unpacking lto-disabled-list (24) ... Selecting previously unselected package dpkg-dev. Preparing to unpack .../29-dpkg-dev_1.21.1ubuntu2.2_all.deb ... Unpacking dpkg-dev (1.21.1ubuntu2.2) ... Selecting previously unselected package build-essential. Preparing to unpack .../30-build-essential_12.9ubuntu3_amd64.deb ... Unpacking build-essential (12.9ubuntu3) ... Selecting previously unselected package libfakeroot:amd64. Preparing to unpack .../31-libfakeroot_1.28-1ubuntu1_amd64.deb ... Unpacking libfakeroot:amd64 (1.28-1ubuntu1) ... Selecting previously unselected package fakeroot. Preparing to unpack .../32-fakeroot_1.28-1ubuntu1_amd64.deb ... Unpacking fakeroot (1.28-1ubuntu1) ... Selecting previously unselected package javascript-common. Preparing to unpack .../33-javascript-common_11+nmu1_all.deb ... Unpacking javascript-common (11+nmu1) ... Selecting previously unselected package libalgorithm-diff-perl. Preparing to unpack .../34-libalgorithm-diff-perl_1.201-1_all.deb ... Unpacking libalgorithm-diff-perl (1.201-1) ... Selecting previously unselected package libalgorithm-diff-xs-perl. Preparing to unpack .../35-libalgorithm-diff-xs-perl_0.04-6build3_amd64.deb ... Unpacking libalgorithm-diff-xs-perl (0.04-6build3) ... Selecting previously unselected package libalgorithm-merge-perl. Preparing to unpack .../36-libalgorithm-merge-perl_0.08-3_all.deb ... Unpacking libalgorithm-merge-perl (0.08-3) ... Selecting previously unselected package libc-devtools. Preparing to unpack .../37-libc-devtools_2.35-0ubuntu3.5_amd64.deb ... Unpacking libc-devtools (2.35-0ubuntu3.5) ... Selecting previously unselected package libexpat1-dev:amd64. Preparing to unpack .../38-libexpat1-dev_2.4.7-1ubuntu0.2_amd64.deb ... Unpacking libexpat1-dev:amd64 (2.4.7-1ubuntu0.2) ... Selecting previously unselected package libfile-fcntllock-perl. Preparing to unpack .../39-libfile-fcntllock-perl_0.22-3build7_amd64.deb ... Unpacking libfile-fcntllock-perl (0.22-3build7) ... Selecting previously unselected package libjs-jquery. Preparing to unpack .../40-libjs-jquery_3.6.0+dfsg+~3.5.13-1_all.deb ... Unpacking libjs-jquery (3.6.0+dfsg+~3.5.13-1) ... Selecting previously unselected package libjs-underscore. Preparing to unpack .../41-libjs-underscore_1.13.2~dfsg-2_all.deb ... Unpacking libjs-underscore (1.13.2~dfsg-2) ... Selecting previously unselected package libjs-sphinxdoc. Preparing to unpack .../42-libjs-sphinxdoc_4.3.2-1_all.deb ... Unpacking libjs-sphinxdoc (4.3.2-1) ... Selecting previously unselected package zlib1g-dev:amd64. Preparing to unpack .../43-zlib1g-dev_1%3a1.2.11.dfsg-2ubuntu9.2_amd64.deb ... Unpacking zlib1g-dev:amd64 (1:1.2.11.dfsg-2ubuntu9.2) ... Selecting previously unselected package libpython3.10-dev:amd64. Preparing to unpack .../44-libpython3.10-dev_3.10.12-1~22.04.3_amd64.deb ... Unpacking libpython3.10-dev:amd64 (3.10.12-1~22.04.3) ... Selecting previously unselected package libpython3-dev:amd64. Preparing to unpack .../45-libpython3-dev_3.10.6-1~22.04_amd64.deb ... Unpacking libpython3-dev:amd64 (3.10.6-1~22.04) ... Selecting previously unselected package manpages-dev. Preparing to unpack .../46-manpages-dev_5.10-1ubuntu1_all.deb ... Unpacking manpages-dev (5.10-1ubuntu1) ... Selecting previously unselected package python3.10-dev. Preparing to unpack .../47-python3.10-dev_3.10.12-1~22.04.3_amd64.deb ... Unpacking python3.10-dev (3.10.12-1~22.04.3) ... Selecting previously unselected package python3-distutils. Preparing to unpack .../48-python3-distutils_3.10.8-1~22.04_all.deb ... Unpacking python3-distutils (3.10.8-1~22.04) ... Selecting previously unselected package python3-dev. Preparing to unpack .../49-python3-dev_3.10.6-1~22.04_amd64.deb ... Unpacking python3-dev (3.10.6-1~22.04) ... Selecting previously unselected package python3-setuptools. Preparing to unpack .../50-python3-setuptools_59.6.0-1.2ubuntu0.22.04.1_all.deb ... Unpacking python3-setuptools (59.6.0-1.2ubuntu0.22.04.1) ... Selecting previously unselected package python3-wheel. Preparing to unpack .../51-python3-wheel_0.37.1-2ubuntu0.22.04.1_all.deb ... Unpacking python3-wheel (0.37.1-2ubuntu0.22.04.1) ... Selecting previously unselected package python3-pip. Preparing to unpack .../52-python3-pip_22.0.2+dfsg-1ubuntu0.4_all.deb ... Unpacking python3-pip (22.0.2+dfsg-1ubuntu0.4) ... Setting up python3-distutils (3.10.8-1~22.04) ... Setting up javascript-common (11+nmu1) ... Setting up manpages-dev (5.10-1ubuntu1) ... Setting up lto-disabled-list (24) ... Setting up python3-setuptools (59.6.0-1.2ubuntu0.22.04.1) ... Setting up libfile-fcntllock-perl (0.22-3build7) ... Setting up libalgorithm-diff-perl (1.201-1) ... Setting up binutils-common:amd64 (2.38-4ubuntu2.3) ... Setting up linux-libc-dev:amd64 (5.15.0-91.101) ... Setting up libctf-nobfd0:amd64 (2.38-4ubuntu2.3) ... Setting up python3-wheel (0.37.1-2ubuntu0.22.04.1) ... Setting up libfakeroot:amd64 (1.28-1ubuntu1) ... Setting up libasan6:amd64 (11.4.0-1ubuntu1~22.04) ... Setting up fakeroot (1.28-1ubuntu1) ... update-alternatives: using /usr/bin/fakeroot-sysv to provide /usr/bin/fakeroot (fakeroot) in auto mode Setting up libtirpc-dev:amd64 (1.3.2-2ubuntu0.1) ... Setting up rpcsvc-proto (1.4.2-0ubuntu6) ... Setting up make (4.3-4.1build1) ... Setting up libquadmath0:amd64 (12.3.0-1ubuntu1~22.04) ... Setting up python3-pip (22.0.2+dfsg-1ubuntu0.4) ... Setting up libdpkg-perl (1.21.1ubuntu2.2) ... Setting up libubsan1:amd64 (12.3.0-1ubuntu1~22.04) ... Setting up libnsl-dev:amd64 (1.3.0-2build2) ... Setting up libcrypt-dev:amd64 (1:4.4.27-1) ... Setting up libjs-jquery (3.6.0+dfsg+~3.5.13-1) ... Setting up libbinutils:amd64 (2.38-4ubuntu2.3) ... Setting up libc-dev-bin (2.35-0ubuntu3.5) ... Setting up libalgorithm-diff-xs-perl (0.04-6build3) ... Setting up libcc1-0:amd64 (12.3.0-1ubuntu1~22.04) ... Setting up liblsan0:amd64 (12.3.0-1ubuntu1~22.04) ... Setting up libitm1:amd64 (12.3.0-1ubuntu1~22.04) ... Setting up libc-devtools (2.35-0ubuntu3.5) ... Setting up libjs-underscore (1.13.2~dfsg-2) ... Setting up libalgorithm-merge-perl (0.08-3) ... Setting up libtsan0:amd64 (11.4.0-1ubuntu1~22.04) ... Setting up libctf0:amd64 (2.38-4ubuntu2.3) ... Setting up libjs-sphinxdoc (4.3.2-1) ... Setting up libgcc-11-dev:amd64 (11.4.0-1ubuntu1~22.04) ... Setting up libc6-dev:amd64 (2.35-0ubuntu3.5) ... Setting up binutils-x86-64-linux-gnu (2.38-4ubuntu2.3) ... Setting up binutils (2.38-4ubuntu2.3) ... Setting up dpkg-dev (1.21.1ubuntu2.2) ... Setting up libexpat1-dev:amd64 (2.4.7-1ubuntu0.2) ... Setting up libstdc++-11-dev:amd64 (11.4.0-1ubuntu1~22.04) ... Setting up zlib1g-dev:amd64 (1:1.2.11.dfsg-2ubuntu9.2) ... Setting up gcc-11 (11.4.0-1ubuntu1~22.04) ... Setting up g++-11 (11.4.0-1ubuntu1~22.04) ... Setting up gcc (4:11.2.0-1ubuntu1) ... Setting up libpython3.10-dev:amd64 (3.10.12-1~22.04.3) ... Setting up python3.10-dev (3.10.12-1~22.04.3) ... Setting up g++ (4:11.2.0-1ubuntu1) ... update-alternatives: using /usr/bin/g++ to provide /usr/bin/c++ (c++) in auto mode Setting up build-essential (12.9ubuntu3) ... Setting up libpython3-dev:amd64 (3.10.6-1~22.04) ... Setting up python3-dev (3.10.6-1~22.04) ... Processing triggers for man-db (2.10.2-1) ... Processing triggers for libc-bin (2.35-0ubuntu3.5) ...
- Second step requires that you pip3 install the oracledb library:
sudo pip3 install oracledb --upgrade
Display detailed console log →
Defaulting to user installation because normal site-packages is not writeable Collecting oracledb Downloading oracledb-1.4.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (8.6 MB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 8.6/8.6 MB 2.7 MB/s eta 0:00:00 Requirement already satisfied: cryptography>=3.2.1 in /usr/lib/python3/dist-packages (from oracledb) (3.4.8) Installing collected packages: oracledb Successfully installed oracledb-1.4.2 - Third step requires you write a Python program to test your connection to Oracle Database 23c Free, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
#!/usr/bin/python # Import the Oracle library. import oracledb try: # Create a connection to local Docker or Podman installation. db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE') # Print a connection message. print("Connected to the Oracle", db.version, "database.") except oracledb.DatabaseError as e: error, = e.args print(sys.stderr, "Oracle-Error-Code:", error.code) print(sys.stderr, "Oracle-Error-Message:", error.message) finally: # Close connection. db.close()
The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.
It should print:
Connected to the Oracle 23.3.0.23.9 database.
- Fourth step requires you write a Python program to test querying data from an Oracle Database 23c Free instance. I created the following avenger table and seeded it with six Avengers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
/* Conditionally drop the table. */ DROP TABLE IF EXISTS avenger; /* Create the table. */ CREATE TABLE avenger ( avenger_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20) , character_name VARCHAR2(20)); /* Seed the table with data. */ INSERT INTO avenger VALUES (1,'Anthony','Stark','Iron Man'); INSERT INTO avenger VALUES (2,'Thor','Odinson','God of Thunder'); INSERT INTO avenger VALUES (3,'Steven','Rogers','Captain America'); INSERT INTO avenger VALUES (4,'Bruce','Banner','Hulk'); INSERT INTO avenger VALUES (5,'Clinton','Barton','Hawkeye'); INSERT INTO avenger VALUES (6,'Natasha','Romanoff','Black Widow');
Then, I extended the program logic to include a cursor and for loop to read the values from the avenger table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
#!/usr/bin/python # Import the Oracle library. import oracledb try: # Create a connection to local Docker or Podman installation. db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE') # Create a cursor. cursor = db.cursor() # Execute a query. cursor.execute("SELECT character_name " + ", first_name " + ", last_name " + "FROM avenger " + "ORDER BY character_name") # Read the contents of the cursor. for row in cursor: print(row[0] + ':',row[2] + ',',row[1]) except oracledb.DatabaseError as e: error, = e.args print(sys.stderr, "Oracle-Error-Code:", error.code) print(sys.stderr, "Oracle-Error-Message:", error.message) finally: # Close cursor and connection. cursor.close() db.close()
The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.
It should print:
Black Widow: Romanoff, Natasha Captain America: Rogers, Steven God of Thunder: Odinson, Thor Hawkeye: Barton, Clinton Hulk: Banner, Bruce Iron Man: Stark, Anthony
- Fifth step requires you write a Python program to test querying data filtered by a local variable from an Oracle Database 23c Free instance. This example looks only for the Hulk among the six Avengers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
#!/usr/bin/python # Import the Oracle library. import oracledb try: # Create a connection to local Docker or Podman installation. db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE') # Create a cursor. cursor = db.cursor() # Execute a query. stmt = "SELECT character_name " \ ", first_name " \ ", last_name " \ "FROM avenger " \ "WHERE character_name = :avenger " \ "ORDER BY character_name" # Execute with bind variable. cursor.execute(stmt, avenger = "Hulk") # Read the contents of the cursor. for row in cursor: print(row[0] + ':',row[2] + ',',row[1]) except oracledb.DatabaseError as e: error, = e.args print(sys.stderr, "Oracle-Error-Code:", error.code) print(sys.stderr, "Oracle-Error-Message:", error.message) finally: # Close cursor and connection. cursor.close() db.close()
It should print:
Hulk: Banner, Bruce
As always, I hope this puts everything together for setting up Python with Oracle Database 23c Free.
Python Objects
I promised to give my students a full example of how to write and execute a Python object. There were two motivations for this post. The first was driven by my students trying to understand the basics and the second my somebody else saying Python couldn’t deliver objects. Hopefully, this code is simple enough for both audiences. I gave them this other tutorial on writing and mimicking overloaded Python functions earlier.
This defines a Ball object type and a FilledBall object subtype of Ball. It incorporates the following elements:
- A special __init__ function, which is a C/C++ equivalent to a constructor.
- A special __str__ function represents a class object instance as a string. It is like the getString() equivalent in the Java programming language.
- A bounce instance function, which means it acts on any instance of the Ball object type or FilledBall object subtype.
- A get_direction instance function and it calls the __format local object function, which is intended to mimic a private function call, like other object-oriented programming languages.
- A private name __format function (Private name mangling: When an identifier that textually occurs in a class definition begins with two or more underscore characters and does not end in two or more underscores, it is considered a private name of that class.)
You can test this code by creating the $PYTHONPATH (Unix or Linux) or %PYTHONPATH% (Windows) as follows with all the code inside the present working directory, like this in Unix or Linux:
export set $PYTHONPATH=. |
Then, you create the Ball.py file with this syntax:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | # Creates a Ball object type and FilledBall object subtype. class Ball: # User-defined constructor with required parameters. def __init__(self, color = None, radius = None, direction = None): # Assign a default color value when the parameter is null. if color is None: self.color = "Blue" else: self.color = color.lower() # Assign a default radius value when the parameter is null. if radius is None: self.radius = 1 else: self.radius = radius # Assign a default direction value when the parameter is null. if direction is None: self.direction = "down" else: self.direction = direction.lower() # Set direction switch values. self.directions = ("down","up") # User-defined standard function when printing an object type. def __str__(self): # Build a default descriptive message of the object. msg = "It's a " + self.color + " " + str(self.radius) + '"' + " ball" # Return the message variable. return msg # Define a bounce function. def bounce(self, direction = None): # Set direction on bounce. if not direction is None: self.direction = direction else: # Switch directions. if self.directions[0] == self.direction: self.direction = self.directions[1] elif self.directions[1] == self.direction: self.direction = self.directions[0] # Define a bounce function. def getDirection(self): # Return current direction of ball. return self.__format(self.direction) # User-defined pseudo-private function, which is available # to instances of the Ball object and any of its subtypes. def __format(self, msg): return "[" + msg + "]" # This is the object subtype, which takes the parent class as an # argument. class FilledBall(Ball): def __init__(self, filler = None): # Instantiate the parent class and then any incremental # parameter values. Ball.__init__(self,"Red",2) # Add a default value or the constructor filler value. if filler is None: self.filler = "Air".lower() else: self.filler = filler # User-defined standard function when printing an object type, which # uses generalized invocation. def __str__(self): # Build a default descriptive message of the object. msg = Ball.__str__(self) + str(" filled with " + self.filler) # Return the message variable. return msg |
Next, let’s test instantiating the Ball object type with the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | #!/usr/bin/python # Import the Ball class into its own namespace. import Ball # Assign an instantiated class to a local variable. myBall = Ball.Ball() # Check whether the local variable holds a valid Ball instance. if not myBall is None: print(myBall, "instance.") else: print("No Ball instance.") # Loop through 10 times changing bounce direction. for i in range(1,10): # Find dirction of ball. print(myBall.getDirection()) # Bounce the ball. myBall.bounce() |
Next, let’s test instantiating the FilledBall object subtype with the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | #!/usr/bin/python # Import the Ball class into its own namespace. import Ball # Assign an instantiated class to a local variable. myBall = Ball.FilledBall() # Check whether the local variable holds a valid FilledBall instance. if not myBall is None: print(myBall, "instance.") else: print("No Ball instance.") # Loop through 10 times changing bounce direction. for i in range(1,10): # Find dirction of ball. print(myBall.getDirection()) # Bounce the ball. myBall.bounce() |
As always, I hope this helps those looking to learn and extend their knowledge.
Quick Python Bit #1
A student asked for a Python example that would find a string starting with a vowel and prepend an “An” or starting with a non-vowel and prepend an “A”. As a rule, this one seems to make sense to most beginning programmers.
#/usr/bin/python # Import the regular expression library. import re # Create a list of words to check. haystack = ['anything','something','nothing'] # Create a regular expression for a leading vowel. needle = "^[AEIOUaeiou].*$" # Read through the haystacks and use the right article. for i in range(0,len(haystack),1): if re.search(needle,haystack[i],re.IGNORECASE): print("An",haystack[i]+".") else: print("A",haystack[i]+".") |
However, this loop is much cleaner:
#/usr/bin/python # Import the regular expression library. import re # Create a list of words to check. haystack = ['anything','something','nothing'] # Create a regular expression for a leading vowel. needle = "^[AEIOUaeiou].*$" # Read through the haystacks and use the right article. for i in haystack: if re.search(needle,i,re.IGNORECASE): print("An",i+".") else: print("A",i+".") |
Both programs print the following:
An anything. A something. A nothing. |
As always, happy to help write another example.
MySQL with CTEs
As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.
These bullets describe the examples:
- Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
- Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
- Uses a table to hold the variables necessary to evaluate and add the element to the query result set.
This is the first Python program:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Declare a list of tuples. dict = [{'level':'Hollywood Star','min_roles':30,'max_roles':99999} ,{'level':'Prolific Actor','min_roles':20,'max_roles':29} ,{'level':'Newcomer','min_roles':1,'max_roles':19}] # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("SELECT a.actor_id " ", a.first_name " ", a.last_name " ", COUNT(fa.actor_id) AS films " "FROM actor a INNER JOIN film_actor fa " "ON a.actor_id = fa.actor_id " "GROUP BY a.actor_id " ", a.first_name " ", a.last_name " "ORDER BY a.last_name " ", a.first_name") # Execute cursor. cursor.execute(query) # Display the rows returned by the query. for (actor_id, first_name, last_name, films) in cursor: for i in range(len(dict)): if films >= dict[i]["min_roles"] and films <= dict[i]["max_roles"]: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , dict[i]["level"] , films)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
The Python dictionary on lines 7 thru 9 and range for loop and if statement on lines 41 and 42 can be eliminated by putting the literal values in a Common Table Expression (CTE). That’s because a CROSS JOIN matches all rows in the CTE against the base table before filtering them.
The match of all rows in the CTE against the base table effectively replaces the range for loop in the original code. The WHERE clause replaces the if statement in the original code.
Another optimization for readability of the final query puts the grouped query into a CTE as well. That way the final query simply demonstrates the filtering process.
This is the second Python program, and it converts the Python dictionary to a list of lists and assigns the lists to param tuple:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Declare a list of lists. list = [['Hollywood Star',30,99999] ,['Prolific Actor',20,29] ,['Newcomer',1,19]] # Declare a tuple of the set of lists. param = (list[0] + list[1] + list[2]) # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.actor_id " " , a.first_name " " , a.last_name " " , COUNT(fa.actor_id) AS num_roles " " FROM actor a INNER JOIN film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.actor_id " " , a.first_name " " , a.last_name ) " " , levels AS " "(SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles " " UNION ALL " " SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles " " UNION ALL " " SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles) " " SELECT a.first_name " " , a.last_name " " , l.level " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query, param) # Display the rows returned by the query. for (first_name, last_name, level, num_roles) in cursor: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , level.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
This is the third Python program requires some SQL setup. You should run this script inside the sakila database first. It basically takes the variables out of the code and stores them in a table. This is more likely what you would do to ensure maintainability of ever changing range values like these if you built a solution like this in a real application. It leaves the aggregation process inside a CTE and simplifies the final query.
-- Conditionally drop the levels table. DROP TABLE IF EXISTS levels; -- Create the levels list. CREATE TABLE levels ( level VARCHAR(16) , min_roles INT , max_roles INT ); -- Insert values into the list table. INSERT INTO levels ( level, min_roles, max_roles ) VALUES ('Hollywood Star', 30, 99999) ,('Prolific Actor', 20, 29) ,('Newcommer',1,19); |
After seeding the data in the levels table, you can test the query natively in MySQL, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- Query the data. WITH actors AS (SELECT a.actor_id , a.first_name , a.last_name , COUNT(*) AS num_roles FROM actor a INNER JOIN film_actor fa ON a.actor_id = fa.actor_id GROUP BY actor_id) SELECT a.first_name , a.last_name , l.level , a.num_roles FROM actors a CROSS JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles ORDER BY a.last_name , a.first_name; |
There’s also a syntax that makes this type of query appear to be an INNER JOIN when it’s actually a filtered CROSS JOIN. If you adopt that syntax, you would rewrite lines 14 and 15:
14 15 | FROM actors a INNER JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles; |
Then, you can run this version without the second CTE element:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.actor_id " " , a.first_name " " , a.last_name " " , COUNT(fa.actor_id) AS num_roles " " FROM actor a INNER JOIN film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.actor_id " " , a.first_name " " , a.last_name ) " " SELECT a.first_name " " , a.last_name " " , l.level " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query) # Display the rows returned by the query. for (first_name, last_name, level, num_roles) in cursor: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , level.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.
Quick Python Flask
A quick example of installing and running a sample hello.py file with the Flask application with the Command Line Interface (CLI) documentation on the Flask website. The first thing you need to do is install the flask module with the pip3 utility on Fedora Linux (or most other platforms):
pip3 install flask --user student |
You should see a successful log like this for student user:
Requirement already satisfied: flask in /usr/local/lib64/python3.7/site-packages (1.1.2) Collecting student Downloading https://files.pythonhosted.org/packages/b5/af/be416c18e4fe63a582e06fb0d47bf059bd0f4f413e5a6cfe893747ebaf79/Student-0.0.1-py3-none-any.whl Requirement already satisfied: click>=5.1 in /usr/lib/python3.7/site-packages (from flask) (7.1.1) Requirement already satisfied: itsdangerous>=0.24 in /usr/local/lib/python3.7/site-packages (from flask) (1.1.0) Requirement already satisfied: Werkzeug>=0.15 in /usr/local/lib/python3.7/site-packages (from flask) (1.0.1) Requirement already satisfied: Jinja2>=2.10.1 in /usr/lib/python3.7/site-packages (from flask) (2.10.1) Requirement already satisfied: MarkupSafe>=0.23 in /usr/lib64/python3.7/site-packages (from Jinja2>=2.10.1->flask) (1.1.1) Installing collected packages: student Successfully installed student-0.0.1 |
The smallest footprint hello.py program is:
# Import statement. from flask import Flask # Application defintion. app = Flask(__name__) # Basic URI rotuing. @app.route("/") # Define a hello function. def hello(): return "Hello World!" # Run the program. if __name__ == "__main__": app.run() |
You can run the program from the command line with two commands:
export FLASK_APP=hello.py flask run |
It will show you the following in the terminal session:
* Serving Flask app "hello.py" * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) |
Type the following URL in a local browser:
http://localhost:5000/ |
Flask will add the following to the console log:
127.0.0.1 - - [26/Oct/2020 00:37:49] "GET / HTTP/1.1" 200 - |
You can jazz the hello.py program up with a an argument list, like this modified example based on this comparison article on Python frameworks:
# Import libraries. from flask import Flask, escape, request # Define the application. app = Flask(__name__) # Define a base URI route and function. @app.route('/') def index(): return "Index Page" # Define an application URI route and function. @app.route("/hello") def hello(): name = request.args.get("name","Simon") return f'Hello {escape(name)}!' # Define an about URI route and function. @app.route("/about") def about(): return "About Page" # Run the file. if __name__ == "__main__": app.run() |
It prints 'Hello Simon!' in a web page. If you try to stop your Flask server with a Ctrl+Z instead of a Ctrl+C, the next time you go to start it you will receive an error message like:
OSError: [Errno 98] Address already in use |
You’ll need to find the Linux process ID and kill the process with prejudice. You can find the process with the following Linux command:
sudo netstat -nlp | grep 5000 [sudo] password for student: |
It prompts you for your sudoer password, and then returns a line like:
tcp 0 0 127.0.0.1:5000 0.0.0.0:* LISTEN 76802/python3 |
You kill the process with the following Linux command:
kill -9 76802 |
As always, I hope this helps those looking for the missing pieces.
Python List & Dictionaries
The following two sample programs are used in an Python programming course that I teach. I find them useful in qualifying how to work with loops, couple loops, and queues. The first example uses two lists and coupled loops, while the second example uses a single dictionary and FILO queue approach.
The Twelve Days of Christmas lyrics can be printed like so with coupled loops:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | days = ['first','second','third','fourth' \ ,'fifth','sixth','seventh','eighth' \ ,'nineth','tenth','eleventh','twelveth'] verse = ['partridge in a pear tree.' \ ,'Two turtle doves,' \ ,'Three French hens,' \ ,'Four calling birds,' \ ,'Five gold rings,' \ ,'Six geese a-laying,' \ ,'Seven swans a-swimming,' \ ,'Eight maids a-milking,' \ ,'Nine ladies dancing,' \ ,'Tenth lords a-leaping,' \ ,'Eleven pipers piping,' \ ,'Twelve drummers drumming,'] # Loop forward, couple inner loop, and loop backward through list. for i in range(0,len(days), 1): print("On the",str(days[i]),"day of Christmas my true love sent to me") for j in range(i, -1, -1): if (j > 0): print(" ",verse[j]) elif (i == j): print(" A",verse[j]) else: print(" and a",verse[j]) |
Recreating the problem into a single dictionary, you can solve by approaching it as a FILO queue. Here’s the approach:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | lyric = {'first':'partridge in a pear tree.' ,'second':'Two turtle doves,' ,'third':'Three French hens,' ,'fourth':'Four calling birds,' ,'fifth':'Five gold rings,' ,'sixth':'Six geese a-laying,' ,'seventh':'Seven swans a-swimming,' ,'eighth':'maids a-milking,' ,'nineth':'Nine ladies dancing,' ,'tenth':'Ten lords a-leaping,' ,'eleventh':'Eleven pipers piping,' ,'twelfth':'Twelve drummers drumming,'} # Intiate a list for collecting stanza. stanza = list() # Generate a list of keys. for i in lyric.keys(): # Append keys to list of stanza. stanza.append(i) # Print the first line of each stanza. print("On the",i,"day of Christmas my true love sent to me") # Print the progressive stanza. for j in reversed(stanza): if (j not in ['first','twelveth']): print(" ",lyric[j]) elif (i == j): print(" A",lyric[j]) else: print(" and a",lyric[j]) |
As always, I hope this helps for approaches and solutions.
Developing Python Libraries
I put this together to show my students how to simplify writing and testing Python library files. The trick requires that you learn how to set a relative $PYTHONPATH environment file.
export set PYTHONPATH=./lib |
After setting the $PYTHONPATH environment variable, connect to Python’s IDLE environment and run the following code:
import os print(os.environ['PYTHONPATH']) |
It prints the following:
./lib |
You can also discover all the standard libraries and your $PYTHONPATH value in your environment with the following command:
for i in sys.path: print(i) |
It prints the following, which lists the one set by the $PYTHONPATH first:
/home/student/Code/python/path/lib /usr/lib64/python37.zip /usr/lib64/python3.7 /usr/lib64/python3.7/lib-dynload /home/student/.local/lib/python3.7/site-packages /usr/lib64/python3.7/site-packages /usr/lib/python3.7/site-packages |
You create a test my_module.py library file in the relative ./lib directory, like the following:
# Define a hello function that accept a name and prints a salutation. def hello(whom): return "Hello " + whom + "!" |
Next, you can create a testlib.py program:
# Import the hello function into the local namesapce from the my_module. from my_module import hello # Call the module hello, which returns a formatted string. print(hello("Suzie Q")) |
It imports the hello(whom) function into the local namespace and then calls the hello(whom) function with the string literal "Susie". It prints:
Hello Suzie Q! |
If you import the my_module module, you must refer to the hello(whom) function by prefacing it with my_module., like the following example:
# Import the hello function into the local namesapce from the my_module. import my_module # Call the module hello, which returns a formatted string. print(my_module.hello("Suzie Q")) |
A direct import doesn’t add the method to the local namespace. It remains in the my_module‘s namespace.
It’s probably important to note where my_module.pyc files are written for the those migrating from Python 2.7 to Python 3. In Python 2.7 they would be written to the ./lib directory, but in Python 3 they’re written to the ./lib/__pycache__ directory.
As always, I hope this helps those who find it and read it.
Subscribe via RSS