MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Parametric Queries

without comments

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.

Written by maclochlainn

March 1st, 2024 at 12:30 am

Posted in MySQL,MySQL 8,Python,Python 3.x,sql

Tagged with ,

«
»