MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Fedora’ Category

Fedora for macOS ARM64

without comments

I’m always updating VMs, and I was gratified to notice that there’s a Fedora arm64 ISO. If you’re interested in it, you can download the Live Workstation from here or the Fedora Server from here.

Unfortunately, I only have macOS running on i7 and i9 Intel Processors. It would be great to hear back how it goes for somebody one of the new Apple M1 chip.

I typically install the workstation version because it meets my needs to run MySQL and other native Linux development tools. However, the server version is also available. Fedora is a wonderful option, as a small footprint for testing things on my MacBookPro.

Written by maclochlainn

May 7th, 2022 at 1:34 pm

Bash Debug Function

without comments

My students working in Linux would have a series of labs to negotiate and I’d have them log the activities of their Oracle SQL scripts. Many of them would suffer quite a bit because they didn’t know how to find the errors in the log files.

I wrote this SQL function for them to put in their .bashrc files. It searches all the .txt files for errors and organizes them by log file, line number, and descriptive error message.

errors () 
{ 
  label="File Name:Line Number:Error Code";
  list=`ls ./*.$1 | wc -l`;
  if [[ ${list} -eq 1 ]]; then
    echo ${label};
    echo "----------------------------------------";
    filename=`ls *.txt`;
    echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`;
  else
    if [[ ${list} -gt 1 ]]; then
      echo ${label};
      echo "----------------------------------------";
      find . -type f | grep --color=auto -in *.txt -e ora\- -e pls\- -e sp2\-;
    fi;
  fi
}

I hope it helps others now too.

Written by maclochlainn

May 4th, 2022 at 10:51 pm

Record Type Arrays

with one comment

Another question that I was asked today: “Can you create an array of a record type in PL/pgSQL?” The answer is yes.

You first have to create a type, which is what you do when you want to create a table with an embedded table. This is a simple full_name record type:

CREATE TYPE full_name AS
( first_name   VARCHAR(20)
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20));

The following DO block shows you how to create a record type array and then print it’s contents in a FOR-LOOP:

DO
$$
DECLARE
  -- An array of full_name records.
  list  full_name[] = 
          array[('Harry','James','Potter')
               ,('Ginevra','Molly','Potter')
               ,('James','Sirius','Potter')
               ,('Albus','Severus','Potter')
               ,('Lily','Luna','Potter')];
BEGIN
  -- Loop through the integers.
  FOR i IN 1..CARDINALITY(list) LOOP
    RAISE NOTICE '%, % %', list[i].last_name, list[i].first_name, list[i].middle_name;
  END LOOP;
END;
$$;

Since you typically only have a single dimension array with record-type structure, using CARDINALITY is clearer than ARRAY_LENGTH(list,1). If you don’t agree use the latter.

It prints the following:

NOTICE:  Potter, Harry James
NOTICE:  Potter, Ginevra Molly
NOTICE:  Potter, James Sirius
NOTICE:  Potter, Albus Severus
NOTICE:  Potter, Lily Luna
DO

As always, I hope this helps those looking for a solution to this type of problem.

PL/pgSQL Array Listing

without comments

Somebody asked me how to navigate a collection in PostgreSQL’s PL/pgSQL and whether they supported table and varray data types, like Oracle’s PL/SQL. The most important thing to correct was that PostgreSQL supports only array types.

The only example that I found with a google search used a FOREACH-loop, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DO
$$
DECLARE
  /* An array of integers. */
  list  int[] = array[1,2,3,4,5];
  /* Define a local variable for array members. */
  i     int;
BEGIN
  /* Loop through the integers. */
  FOREACH i IN ARRAY list LOOP
    RAISE NOTICE '[%]', i;
  END LOOP;
END;
$$;

It prints:

NOTICE:  [1]
NOTICE:  [2]
NOTICE:  [3]
NOTICE:  [4]
NOTICE:  [5]

As I suspected the student didn’t want to use a FOREACH-loop. The student wanted to use a for-loop, which was much closer to the Oracle PL/SQL syntax with which they were most familiar. That example is:

1
2
3
4
5
6
7
8
9
10
11
12
DO
$$
DECLARE
  /* An array of integers. */
  list  int[] = array[1,2,3,4,5];
BEGIN
  /* Loop through the integers. */
  FOR i IN 1..5 LOOP
    RAISE NOTICE '[%]', list[i];
  END LOOP;
END;
$$;

However, it’s bad form to use a literal for the upper number in a range for-loop, and you should use the CARDINALITY function in PostgreSQL because there is no collection API, like Oracle’s COUNT method. There is an ARRAY_LENGTH function but it’s really only necessary when you use a multidimensional array.

The modified code is:

1
2
3
4
5
6
7
8
9
10
11
12
DO
$$
DECLARE
  -- An array of integers.
  list  int[] = array[1,2,3,4,5];
BEGIN
  /* Loop through the integers. */
  FOR i IN 1..CARDINALITY(list) LOOP
    RAISE NOTICE '[%]', list[i];
  END LOOP;
END;
$$;

If you use the ARRAY_LENGTH function, line #8 would look like:

7
8
  /* Loop through the integers, and determines the length of the first dimension. */
  FOR i IN 1..ARRAY_LENGTH(list,1) LOOP

As always, I hope this helps those looking for a clear solution to basic activities.

Written by maclochlainn

April 27th, 2022 at 1:21 am

Drop Overloaded Routine

without comments

In October 2019, I wrote a post with anonymous block programs to drop tables, sequences, routines, and triggers. Two weeks later, I wrote another post to drop all overloaded routines. However, I recognized the other day that I should have written a function that let you target which function or procedure you want to drop.

The older code only let you drop all of your functions or procedures. That was overkill when you’re working on new functions or procedures.

This post provides a utility for those writing functions and procedures in a public schema of any database in a PostgreSQL installation. It is designed to drop functions or procedures from the public schema.

The code follows below:

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
CREATE OR REPLACE
  FUNCTION drop_routine( IN pv_routine_name    VARCHAR(64)
                       , IN pv_routine_type    VARCHAR(64))
  RETURNS INTEGER AS
  $$
  DECLARE
    /* Declare the current catalog. */
    lv_local_catalog  VARCHAR(64) := current_database();
 
    /* Declare return type variable. */
    lv_retval  INTEGER := 1;
 
    /* Manage data dictionary case mechanics:
       ======================================
	     routine_name is always in lowercase.
	     routine_type is always in uppercase. */
    lv_routine_name  VARCHAR(64) := LOWER(pv_routine_name);
    lv_routine_type  VARCHAR(64) := UPPER(pv_routine_type);
 
    /* Declare an indefinite length string for SQL statement. */
    sql  VARCHAR;
 
    /* Declare variables to manage cursor return values. */
    row  RECORD;
    arg  VARCHAR;
 
    /* Declare parameter list. */
    list VARCHAR;
 
    /* Declare a routine cursor. */
    routine_cursor CURSOR( cv_routine_name  VARCHAR
                         , cv_routine_type  VARCHAR ) FOR
      SELECT r.routine_name
      ,      r.specific_name
      ,      r.routine_type
      FROM   information_schema.routines r
      WHERE  r.specific_catalog = current_database()
      AND    r.routine_schema = 'public'
      AND    r.routine_type = cv_routine_type
      AND    r.routine_name = cv_routine_name;
 
    /* Declare a parameter cursor. */
    parameter_cursor CURSOR( cv_specific_name  VARCHAR ) FOR
      SELECT args.data_type
      FROM   information_schema.parameters args
      WHERE  args.specific_catalog = current_database()
      AND    args.specific_schema = 'public'
      AND    args.specific_name = cv_specific_name;
 
  BEGIN
    /* Open the cursor. */
    OPEN routine_cursor(lv_routine_name, lv_routine_type);
    <<row_loop>>
    LOOP
      /* Fetch table names. */
      FETCH routine_cursor INTO row;
 
      /* Exit when no more records are found. */
      EXIT row_loop WHEN NOT FOUND;
 
      /* Initialize parameter list. */
      list := '(';
 
      /* Open the parameter cursor. */
      OPEN parameter_cursor(row.specific_name::varchar);
      <<parameter_loop>>
      LOOP
        FETCH parameter_cursor INTO arg;
 
        /* Exit the parameter loop. */
        EXIT parameter_loop WHEN NOT FOUND;
 
        /* Add parameter and delimit more than one parameter with a comma. */
        IF LENGTH(list) > 1 THEN
          list := CONCAT(list,',',arg);
        ELSE
          list := CONCAT(list,arg);
        END IF;
      END LOOP;
 
      /* Close the parameter list. */
      list := CONCAT(list,')');
 
      /* Close the parameter cursor. */
      CLOSE parameter_cursor;
 
      /* Concatenate together a DDL to drop the table with prejudice. */
      sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name||list;
 
      /* Execute the DDL statement. */
      EXECUTE sql;
 
      /* Assign success flag of 0. */
      lv_retval := 0;
 
    END LOOP;
 
    /* Close the routine_cursor. */
    CLOSE routine_cursor;
 
    /* Return the output text variable. */
    RETURN lv_retval;
  END
  $$ LANGUAGE plpgsql;

If you now create a series of hello overloaded functions, like:

CREATE OR REPLACE
  FUNCTION hello()
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT 'Hello World!' INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(id int, whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('[',id,'] Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;

After you create the overloaded functions, you can query their status from the information_schema.routines table in the data dictionary:

SELECT routine_name
,      specific_name
,      routine_type
FROM   information_schema.routines
WHERE  specific_catalog = current_setting('videodb.catalog_name')
AND    routine_schema = 'public'
AND    routine_name = 'hello';

Which shows you the three versions of the hello function:

 routine_name | specific_name | routine_type
--------------+---------------+--------------
 hello        | hello_18100   | FUNCTION
 hello        | hello_18101   | FUNCTION
 hello        | hello_18102   | FUNCTION
(3 rows)

You can drop all versions of the hello functions by calling the drop_routine function:

SELECT CASE
         WHEN drop_routine('hello','function') = 0
         THEN 'Success'
         ELSE 'Failure'
       END AS drop_routine;

It returns the following:

 drop_routine
--------------
 Success
(1 row)

As always, I hope this helps those looking for how to routinely test new functions and procedures.

Written by maclochlainn

March 6th, 2022 at 11:53 pm

Python on PostgreSQL

without comments

The ODBC library you use when connecting Python to PostgreSQL is the psycopg2 Python library. This blog post will show use how to use it in Python and install it on your Fedora Linux installation. It leverages a videodb database that I show you how to build in this earlier post on configuring PostgreSQL 14.

You would import psycopg2 as follows in your Python code:

import psycopg2

Unfortunately, that only works on Linux servers when you’ve installed the library. That library isn’t installed with generic Python libraries. You get the following error when the psycopg2 library isn’t installed on your server.

Traceback (most recent call last):
  File "python_new_hire.sql", line 1, in <module>
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

You can install it on Fedora Linux with the following command:

yum install python3-psycopg2

It will install:

====================================================================================
 Package                  Architecture   Version               Repository      Size
====================================================================================
Installing:
 python3-psycopg2         x86_64         2.7.7-1.fc30          fedora         160 k
 
Transaction Summary
====================================================================================
Install  1 Package
 
Total download size: 160 k
Installed size: 593 k
Is this ok [y/N]: y
Downloading Packages:
python3-psycopg2-2.7.7-1.fc30.x86_64.rpm            364 kB/s | 160 kB     00:00    
------------------------------------------------------------------------------------
Total                                               167 kB/s | 160 kB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                            1/1 
  Installing       : python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
  Running scriptlet: python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
  Verifying        : python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
 
Installed:
  python3-psycopg2-2.7.7-1.fc30.x86_64                                              
 
Complete!

Here’s a quick test case that you can run in PostgreSQL and Python to test all the pieces. The first SQL script creates a new_hire table and inserts two rows, and the Python program queries data from the new_hire table.

The new_hire.sql file creates the new_hire table and inserts two rows:

-- Environment settings for the script.
SET SESSION "videodb.table_name" = 'new_hire';
SET CLIENT_MIN_MESSAGES TO ERROR;
 
--  Verify table name.
SELECT current_setting('videodb.table_name');
 
-- ------------------------------------------------------------------
--  Conditionally drop table.
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS new_hire CASCADE;
 
-- ------------------------------------------------------------------
--  Create table.
-- -------------------------------------------------------------------
CREATE TABLE new_hire
( new_hire_id  SERIAL
, first_name   VARCHAR(20)  NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)  NOT NULL
, hire_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
, PRIMARY KEY (new_hire_id));
 
-- Alter the sequence by restarting it at 1001.
ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001;
 
-- Display the table organization.
SELECT   tc.table_catalog || '.' || tc.constraint_name AS constraint_name
,        tc.table_catalog || '.' || tc.table_name AS table_name
,        kcu.column_name
,        ccu.table_catalog || '.' || ccu.table_name AS foreign_table_name
,        ccu.column_name AS foreign_column_name
FROM     information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
AND      ccu.table_schema = tc.table_schema
WHERE    tc.constraint_type = 'FOREIGN KEY'
AND      tc.table_name = current_setting('videodb.table_name')
ORDER BY 1;
 
SELECT c1.table_name
,      c1.ordinal_position
,      c1.column_name
,      CASE
         WHEN c1.is_nullable = 'NO' AND c2.column_name IS NOT NULL THEN 'PRIMARY KEY'
         WHEN c1.is_nullable = 'NO' AND c2.column_name IS NULL THEN 'NOT NULL'
       END AS is_nullable
,      CASE
         WHEN data_type = 'character varying' THEN
           data_type||'('||character_maximum_length||')'
         WHEN data_type = 'numeric' THEN
           CASE
             WHEN numeric_scale != 0 AND numeric_scale IS NOT NULL THEN
               data_type||'('||numeric_precision||','||numeric_scale||')'
             ELSE
               data_type||'('||numeric_precision||')'
             END
         ELSE
           data_type
        END AS data_type
FROM    information_schema.columns c1 LEFT JOIN
          (SELECT trim(regexp_matches(column_default,current_setting('videodb.table_name'))::text,'{}')||'_id' column_name
           FROM   information_schema.columns) c2
ON       c1.column_name = c2.column_name
WHERE    c1.table_name = current_setting('videodb.table_name')
ORDER BY c1.ordinal_position;
 
-- Display primary key and unique constraints.
SELECT constraint_name
,      lower(constraint_type) AS constraint_type
FROM   information_schema.table_constraints
WHERE  table_name = current_setting('videodb.table_name')
AND    constraint_type IN ('PRIMARY KEY','UNIQUE');
 
-- Insert two test records.
INSERT INTO new_hire
( first_name, middle_name, last_name, hire_date )
VALUES
 ('Malcolm','Jacob','Lewis','2018-2-14')
,('Henry',null,'Chabot','1990-07-31');

You can put it into a local directory, connect as the student user to a videodb database, and run the following command (or any database you’ve created).

\i new_hire.sql

The new_hire.py file creates the new_hire table and inserts two rows:

# Import the PostgreSQL connector library.
import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT new_hire_id, first_name, last_name " \
          "FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Fetch all rows from a table.
  records = cursor.fetchall()
 
  # Read through and print the rows as tuples.
  for row in range(0, len(records)):
    print(records[row]) 
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

You run it from the command line, like:

python3 ./new_hire.py

It should print:

(1001, 'Malcolm', 'Lewis')
(1002, 'Henry', 'Chabot')

As always, I hope this helps those trying to sort out how to connect Python to PostgreSQL.

Written by maclochlainn

March 2nd, 2022 at 1:06 am

Oracle Container User

without comments

After you create and provision the Oracle Database 21c Express Edition (XE), you can create a c##student container user with the following two step process.

  1. Create a c##student Oracle user account with the following command:

    CREATE USER c##student IDENTIFIED BY student
    DEFAULT TABLESPACE users QUOTA 200M ON users
    TEMPORARY TABLESPACE temp;

  2. Grant necessary privileges to the newly created c##student user:

    GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
    ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
    ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
    ,     CREATE VIEW TO c##student;

As always, I hope this helps those looking for how to do something that’s less than clear because everybody uses tools.

Written by maclochlainn

January 31st, 2022 at 5:58 pm

Linux sqlplus wrapper

without comments

Here’s a quick way to ensure you can use the up-arrows and navigation keys when using the sqlplus command-line interface. You can just add it to your .bashrc file.

sqlplus ()
{ 
    path=`which rlwrap 2>/dev/null`;
    file='';
    if [ -n ${path} ]; then
        file=${path##/*/};
    fi;
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}";
    else
        echo "Command-line history unavailable: Install the rlwrap package.";
        $ORACLE_HOME/bin/sqlplus "${@}";
    fi
}

As always, I hope this helps those looking of solutions.

Written by maclochlainn

November 12th, 2021 at 11:34 pm

Waking up the Network

without comments

Interesting problems seem to plague me from time to time. The current problem isn’t yet solved but I’m working on it. After a sleep cycle, IPV6 networking isn’t starting.

No internet

Try:

  • Checking the network cables, modem, and router
  • Reconnecting to Wi-Fi
ERR_INTERNET_DISCONNECTED

In the broken Fedora 30 VM, I checked the status with the nmcli tool:

sudo nmcli general status

It returned:

STATE   CONNECTIVITY  WIFI-HW  WIFI     WWAN-HW  WWAN    
asleep  none          enabled  enabled  enabled  enabled

The STATE should return connected and connectivity return full. Unfortunately, that’s not the case.

There was little surprise that the next check:

sudo nmcli device

Returned the following:

DEVICE      TYPE      STATE      CONNECTION 
virbr0      bridge    unmanaged  --         
ens33       ethernet  unmanaged  --         
lo          loopback  unmanaged  --         
virbr0-nic  tun       unmanaged  --

In a working instance, it should return:

DEVICE      TYPE      STATE      CONNECTION 
ens33       ethernet  connected  ens33      
virbr0      bridge    connected  virbr0     
lo          loopback  unmanaged  --         
virbr0-nic  tun       unmanaged  --

I’m currently troubleshooting what failed by leveraging an article on How to Configure Network Connection Using ‘nmcli’ Tool and the Gnome nmcli documentation. Naturally, when I get it fixed, I’ll finish this article.

Written by maclochlainn

November 8th, 2021 at 12:45 am

Posted in Fedora,Linux,nmcli,Unix

Tagged with

MySQL with CTEs

without comments

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:

  1. 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.
  2. 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.
  3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.
  4. 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.

Written by maclochlainn

November 3rd, 2021 at 10:01 am