MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

SQL Calculations #1

without comments

This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:

1

SELECT 2 + 2 AS result;

It will display the result of the addition to the column alias result as a derived table, or the following result:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single x column in the SELECT-list.

The CTE runs first, then a subsequent query may use the CTE’s derived table results. Below is a query that uses the value in the struct.x derived table (or references the struct tuple’s x element) twice while assigning the value to a new column alias, labelled result. The FROM clause places the struct tuple in the queries namespace, which lets you reference it in the SELECT-list.

1
2
3
4

WITH struct AS
 (SELECT 2 AS x)
SELECT struct.x + struct.x AS result
FROM  struct;

Like the literal example, it will display the result of the addition to the column alias result as a derived table of one row:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Having laid a basis for a simple calculation in one row, let’s expand the example and demonstrate how to perform row-by-row calculations. The example requires introducing some new concepts. One uses the UNION ALL set operator to fabricate a CTE derived table with three rows. Another uses a comma within the WITH clause to create two derived tables or CTEs. The last uses the CROSS JOIN to add the single row CTE’s single y column to each of the rows returned by the multiple row CTE.

The CROSS JOIN is a Cartesian product, which multiplies the rows in one table against the rows in another table while adding the columns from each table. That means fabricating a table of one column and one row lets you put a variable into all the rows of another table or set of tables combined through an equijoin or non-equijoin operation.

The query below takes a struct1 derived table of one column and three rows and a struct2 derived table of one column and one row, then uses a CROSS JOIN to create a new derived table, which would be a table of two columns and three rows. The Cartesian product only provides the two columns that we will multiply to create new data.

The SELECT-list lets us fabricate a new column where we multiply the values of column x and column y to create a set of new results in column result.

1
2
3
4
5
6
7
8
9
10

WITH struct1 AS
 (SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x)
, struct2 AS
 (SELECT 10 AS y)
SELECT struct1.x AS x
,      struct2.y AS y
,      struct1.x * struct2.y AS result
FROM  struct1 CROSS JOIN struct2;

The query returns the following results, which show the values used to calculate the result and the result:

+---+----+--------+
| x | y  | result |
+---+----+--------+
| 1 | 10 |     10 |
| 2 | 10 |     20 |
| 3 | 10 |     30 |
+---+----+--------+
3 rows in set (0.00 sec)

As a rule, the columns x and y would not be displayed in the final derived table. You would only see the result columns’ values.

Let’s use an example from Alan Bwaulieu’s Learning SQL book with a twist. Rather than manually fabricating the ordinal numbers twice, let’s use the scope reference of a subsequent CTE to reference an earlier CTE. That would create two ten row tables of one column each, or a Cartesian product of a 100 row table with two columns. Then, let’s use the SELECT-list lets us fabricate only a new column, which will add 1 to the numbers 0 to 99 to give us the numbers 1 to a 100.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT x * 10 AS x FROM ones)
SELECT ones.x + tens.x + 1 AS ordinal
FROM   ones CROSS JOIN tens
ORDER BY ordinal;

It returns the following result set:

+---------+
| ordinal |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
|      11 |
 ...
|      98 |
|      99 |
|     100 |
+---------+
100 rows in set (0.00 sec)

Moving on to more complex math, let’s create a numerals table with the result from our prior query. It will enable calculating the factors of exponents. The easiest way to create the table is shown below (only caveat is that it will build it with a biting rather than an int data type).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE TABLE numerals AS
WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT x * 10 AS x FROM ones)
SELECT ones.x + tens.x + 1 AS ordinal
FROM   ones CROSS JOIN tens
ORDER BY ordinal;

Line #15 sets the column alias that determines the name of the column for the numerals table. It can be described after running the foregoing script in MySQL as:

+---------+--------+------+-----+---------+-------+
| Field   | Type   | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+-------+
| ordinal | bigint | NO   |     | 0       |       |
+---------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

The next query accepts a substitution variable into the WITH clause, which means an external program will call it. (Although, you could use a session level variable, which I would discourage.) This query returns the factors for any given exponent:

1
2
3
4
5
6
7
8
9

WITH magic AS
(SELECT %s AS vkey)
SELECT   CONCAT(magic.vkey,'^',LOG(magic.vkey,n.ordinal)) AS powers
,        n.ordinal AS result
FROM     numerals n CROSS JOIN magic
WHERE    MOD(n.ordinal,magic.vkey) = 0
AND      LOG(magic.vkey,n.ordinal) REGEXP '^[0-9]*$' 
OR       n.ordinal = 1
ORDER BY n.ordinal;

FYI, in the WHERE clause the regular expression is guarantees only rows returning integer values, and the 1 returns the identity property of an exponent raised to the zero power.

Assuming you created the numerals table, put the foregoing query in a query.sql file (because I was to lazy to write the full parameter handling), and you run it in the same directory as this Python program, it’ll take any valid integer as 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
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

#!/usr/bin/python
# ------------------------------------------------------------
#  Name: power.py
#  Date: 19 Oct 2024
# ------------------------------------------------------------
#  Purpose:
#  -------
#    The program shows you how to provide a single agrument
#    to a query and print the formatted output.
#
#    You can call the program:
#
#    ./power.py 3
#
# ------------------------------------------------------------
 
# Import libraries.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# ============================================================
#  Define a local padding function.
# ============================================================
def pad(valueIn):
  # Define local variable.
  padding = ''
 
  # Convert single digit numbers to strings.
  if isinstance(valueIn,int) and len(str(valueIn)) == 1:
    padding = ' '
 
  # Return padding space.
  return padding
# ============================================================
#  End local function defintion.
# ============================================================
 
# Define any local variables.
powerIn = 2
query = ""
 
# ============================================================
#  Capture argument list minus the program name.
# ============================================================
arguments = sys.argv[1:]
 
# ============================================================
#  If one or more arguments 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 arguments
#  in the list.
# ============================================================
if len(arguments) >= 1 and arguments[0].isdigit():
  powerIn = int(arguments[0])
 
# ============================================================
#  Use a try-catch block to read and parse a query from a
#  a file found in the same local directory as the Python
#  program.
# ============================================================
try:
  file = open('query.sql','r')
  query = file.read().replace('\n',' ').replace(';','')
  file.close()
 
except IOError:
  print("Could not read file:", fileName)
 
# ============================================================
#  Attempt connection in a try-catch block.
# ============================================================
# --------------------------------------------------------
#  Open connection, bind variable in query and format
#  query output before closing the cursor.
# --------------------------------------------------------
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
 
  # Create cursor.
  cursor = cnx.cursor()
 
  # Execute cursor, and coerce string to tuple.
  cursor.execute(query, (powerIn,))
 
  # Display the rows returned by the query.
  for (powers, result) in cursor:
    print((" {} is: {}").format(powers, pad(result) + str(result)))
 
  # Close cursor.
  cursor.close()
 
# --------------------------------------------------------
#  Handle MySQL exception 
# --------------------------------------------------------
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 connection after try-catch completes.
# --------------------------------------------------------
# Close the connection when the try block completes.
else:
  cnx.close()

If you forget to call it with a numeric parameter, it uses 2 as the default. You would call it as follows from a Linux prompt from the local directory:

./power.py

It returns:

 2^0 is:  1
 2^1 is:  2
 2^2 is:  4
 2^3 is:  8
 2^4 is: 16
 2^5 is: 32
 2^6 is: 64

If you call it with a numeric parameter, it uses the numeric value. You would call it as follows from a Linux prompt from the local directory:

./power.py 3

It returns:

 3^0 is:  1
 3^1 is:  3
 3^2 is:  9
 3^3 is: 27
 3^4 is: 81

As always, I hope the post helps folks sort out how and why things work.

Written by maclochlainn

November 17th, 2024 at 6:47 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Connect,MySQL Connect/Python,Python,Python 3.x

SQL Statement Management

without comments

It’s very difficult explaining to students new to relational databases how SQL works. There are many parts that seem intuitive and then there are others that confuse and confound.

For beginners, the idea that a SQL statement is simply a text string that you must dispatch to a SQL statement processing engine is new. That’s because they use an Integrated Development Environment (IDE) that hides, or abstracts the complexity, of how SQL executes.

I start my core SQL class by demonstrating how to run a text literal query without a FROM clause in MySQL Workbench, like this:

SELECT 'Hello World!' AS "Output";

After writing the query, I highlight everything except the semicolon and click the lightening bolt that dispatches the static string to the SQL statement engine. They see this result:

Then, I launch a mysql Monitor session and write the query with a semicolon to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output";

and, with a \g to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output"\g

Both queries return the same output, as shown below:

+--------------+
| output       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

Rewriting the query with a \G to dispatch the SQL static string to the SQL statement engine:

SELECT 'Hello World!' AS "Output"\G

Both queries return the following output:

*************************** 1. row ***************************
output: Hello World!
1 row in set (0.00 sec)

The next step requires removing the MySQL Workbench and MySQL Monitor from the demonstration. Without either of those tools, a Python program can demonstrate how to run a static SQL string.

The query is now a string literal into a query.sql file. The Python program reads the query.sql file, dispatches the embedded query, and displays the query results.

This is the query.sql file is:

SELECT 'Hello World!' AS "output";

This is the query.py file is:

#!/usr/bin/python
 
# Import libraries.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# ============================================================
#  Use a try-catch block to read and parse a query from a
#  a file found in the same local directory as the Python
#  program.
# ============================================================
try:
  file = open('query.sql','r')
  query = file.read().replace('\n',' ').replace(';','')
  file.close()
 
except IOError:
  print("Could not read file:", fileName)
 
# ============================================================
#  Attempt connection in a try-catch block.
# ============================================================
# --------------------------------------------------------
#  Open connection, bind variable in query and format
#  query output before closing the cursor.
# --------------------------------------------------------
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
 
  # Create cursor.
  cursor = cnx.cursor()
 
  # Execute cursor, and coerce string to tuple.
  cursor.execute(query)
 
  # Display the rows returned by the query.
  for row in cursor:
    print(row[0])
 
  # Close cursor.
  cursor.close()
 
# --------------------------------------------------------
#  Handle MySQL exception 
# --------------------------------------------------------
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 connection after try-catch completes.
# --------------------------------------------------------
# Close the connection when the try block completes.
else:
  cnx.close()

In Linux or Unix from the relative directory where both the query.sql and query.py files are located:

./query.py

It returns:

Hello World!

These examples demonstrate that a query without variable substitution is only a static string. In all the cases, the static SQL strings are dispatched to the SQL engine by a terminator like a semicolon or through an ODBC library call that executes the static SQL string.

Written by maclochlainn

October 20th, 2024 at 1:38 pm

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

Tagged with

Learning SQL Exercise

without comments

I’ve been using Alan Beaulieu’s Learning SQL to teach my SQL Development class with MySQL 8. It’s a great book overall but Chapter 12 lacks a complete exercise. Here’s all that the author provides to the reader. This is inadequate for most readers to work with to solve the concept of a transaction.

Exercise 12-1

Generate a unit of work to transfer $50 from account 123 to account 789. You will need to insert two rows into the transaction table and update two rows in the account table. Use the following table definitions/data:

                      Account:
account_id     avail_balance    last_activity_date
-----------    --------------   ------------------
       123               450    2019-07-10 20:53:27
       789               125    2019-06-22 15:18:35
 
                      Transaction:
txn_id    txn_date      account_id    txn_type_cd    amount
------    ----------    -------+--    -----------    ------
  1001    2019-05-15           123    C                 500
  1002    2019-06-01           789    C                  75

Use txn_type_cd = ‘C” to indicate a credit (addition), and use txn_type_cd = ‘D’ to indicate a debit (substraction).

New Exercise 12-1

The problem with the exercise description is that the sakila database, which is used for most of the book, doesn’t have transaction or account tables. Nor, are there any instructions about general accounting practices or principles. These missing components make it hard for students to understand how to build the transaction.

The first thing the exercise’s problem defintion should qualify is how to create the account and transaction tables, like:

  1. Create the account table, like this with an initial auto incrementing value of 1001: 

    -- +--------------------+--------------+------+-----+---------+----------------+
-- | Field              | Type         | Null | Key | Default | Extra          |
-- +--------------------+--------------+------+-----+---------+----------------+
-- | account_id         | int unsigned | NO   | PRI | NULL    | auto_increment |
-- | avail_balance      | double       | NO   |     | NULL    |                |
-- | last_activity_date | datetime     | NO   |     | NULL    |                |
-- +--------------------+--------------+------+-----+---------+----------------+

  2. Create the transaction table, like this with an initial auto incrementing value of 1001: 

    -- +----------------+--------------+------+-----+---------+----------------+
-- | Field          | Type         | Null | Key | Default | Extra          |
-- +----------------+--------------+------+-----+---------+----------------+
-- | txn_id         | int unsigned | NO   | PRI | NULL    | auto_increment |
-- | txn_date       | datetime     | YES  |     | NULL    |                |
-- | account_id     | int unsigned | YES  |     | NULL    |                |
-- | txn_type_cd    | varchar(1)   | NO   |     | NULL    |                |
-- | amount         | double       | YES  |     | NULL    |                |
-- +----------------+--------------+------+-----+---------+----------------+

Checking accounts are liabilities to banks, which means you credit a liability account to increase its value and debit a liability to decrease its value. You should insert the initial rows into the account table with a zero avail_balance. Then, make these iniitial deposits:

  1. Credit transaction table with an account_id column value of 123 with $500 and a txn_type_cd column value of ‘C’.
  2. Credit transaction table with an account_id column value of 789 with $75 and a txn_type_cd column value of ‘C’.

Write an update statement to set the avail_balance column values equal to the aggregate sum of the transaction table’s rows, which treats credit transacctions (those with a ‘C’ in the txn_type_cd column as a positive number and thos with a ‘D’ in the txn_type_cd column as a negative number).

Generate a unit of work to transfer $50 from account 123 to account 789. You will need to insert two rows into the transaction table and update two rows in the account table. Use the following table definitions/data:

  1. Debit transaction table with an account_id column value of 123 with $50 and a txn_type_cd column value of ‘D’.
  2. Credit transaction table with an account_id column value of 789 with $50 and a txn_type_cd column value of ‘C’.

Apply the prior update statement to set the avail_balance column values equal to the aggregate sum of the transaction table’s rows, which treats credit transacctions (those with a ‘C’ in the txn_type_cd column as a positive number and thos with a ‘D’ in the txn_type_cd column as a negative number).

Here’s the solution to the problem:

-- +--------------------+--------------+------+-----+---------+----------------+
-- | Field              | Type         | Null | Key | Default | Extra          |
-- +--------------------+--------------+------+-----+---------+----------------+
-- | account_id         | int unsigned | NO   | PRI | NULL    | auto_increment |
-- | avail_balance      | double       | NO   |     | NULL    |                |
-- | last_activity_date | datetime     | NO   |     | NULL    |                |
-- +--------------------+--------------+------+-----+---------+----------------+
 
DROP TABLE IF EXISTS account, transaction;
 
CREATE TABLE account
( account_id          int unsigned PRIMARY KEY AUTO_INCREMENT
, avail_balance       double       NOT NULL
, last_activity_date  datetime     NOT NULL )
 ENGINE=InnoDB 
 AUTO_INCREMENT=1001 
 DEFAULT CHARSET=utf8mb4 
 COLLATE=utf8mb4_0900_ai_ci;
 
-- +----------------+--------------+------+-----+---------+----------------+
-- | Field          | Type         | Null | Key | Default | Extra          |
-- +----------------+--------------+------+-----+---------+----------------+
-- | txn_id         | int unsigned | NO   | PRI | NULL    | auto_increment |
-- | txn_date       | datetime     | YES  |     | NULL    |                |
-- | account_id     | int unsigned | YES  |     | NULL    |                |
-- | txn_type_cd    | varchar(1)   | NO   |     | NULL    |                |
-- | amount         | double       | YES  |     | NULL    |                |
-- +----------------+--------------+------+-----+---------+----------------+
 
CREATE TABLE transaction
( txn_id         int unsigned  PRIMARY KEY AUTO_INCREMENT
, txn_date       datetime      NOT NULL
, account_id     int unsigned  NOT NULL
, txn_type_cd    varchar(1)
, amount         double
, CONSTRAINT transaction_fk1 FOREIGN KEY (account_id)
 REFERENCES account(account_id))
 ENGINE=InnoDB
 AUTO_INCREMENT=1001
 DEFAULT CHARSET=utf8mb4
 COLLATE=utf8mb4_0900_ai_ci;
 
-- Insert initial accounts.
INSERT INTO account
( account_id
, avail_balance
, last_activity_date )
VALUES
( 123
, 0
,'2019-07-10 20:53:27');
 
INSERT INTO account
( account_id
, avail_balance
, last_activity_date )
VALUES
( 789
, 0
,'2019-06-22 15:18:35');
 
-- Insert initial deposits.
INSERT INTO transaction
( txn_date
, account_id
, txn_type_cd
, amount )
VALUES
( CAST(NOW() AS DATE)
, 123
,'C'
, 500 );
 
INSERT INTO transaction
( txn_date
, account_id
, txn_type_cd
, amount )
VALUES
( CAST(NOW() AS DATE)
, 789
,'C'
, 75 );
 
UPDATE account a
SET    a.avail_balance = 
 (SELECT  SUM(
            CASE
              WHEN t.txn_type_cd = 'C' THEN amount
              WHEN t.txn_type_cd = 'D' THEN amount * -1
            END) AS amount
 FROM     transaction t
 WHERE    t.account_id = a.account_id
 AND      t.account_id IN (123,789)
 GROUP BY t.account_id);
 
SELECT * FROM account;
SELECT * FROM transaction;
 
-- Insert initial deposits.
INSERT INTO transaction
( txn_date
, account_id
, txn_type_cd
, amount )
VALUES
( CAST(NOW() AS DATE)
, 123
,'D'
, 50 );
 
INSERT INTO transaction
( txn_date
, account_id
, txn_type_cd
, amount )
VALUES
( CAST(NOW() AS DATE)
, 789
,'C'
, 50 );
 
UPDATE account a
SET    a.avail_balance = 
 (SELECT  SUM(
            CASE
              WHEN t.txn_type_cd = 'C' THEN amount
              WHEN t.txn_type_cd = 'D' THEN amount * -1
            END) AS amount
 FROM     transaction t
 WHERE    t.account_id = a.account_id
 AND      t.account_id IN (123,789)
 GROUP BY t.account_id);
 
SELECT * FROM account;
SELECT * FROM transaction;

The results are:

+------------+---------------+---------------------+
| account_id | avail_balance | last_activity_date  |
+------------+---------------+---------------------+
|        123 |           450 | 2019-07-10 20:53:27 |
|        789 |           125 | 2019-06-22 15:18:35 |
+------------+---------------+---------------------+
2 rows in set (0.00 sec)
 
+--------+---------------------+------------+-------------+--------+
| txn_id | txn_date            | account_id | txn_type_cd | amount |
+--------+---------------------+------------+-------------+--------+
|   1001 | 2024-04-01 00:00:00 |        123 | C           |    500 |
|   1002 | 2024-04-01 00:00:00 |        789 | C           |     75 |
|   1003 | 2024-04-01 00:00:00 |        123 | D           |     50 |
|   1004 | 2024-04-01 00:00:00 |        789 | C           |     50 |
+--------+---------------------+------------+-------------+--------+
4 rows in set (0.00 sec)

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

April 1st, 2024 at 12:32 am

Posted in MySQL,MySQL 8

Tagged with ,

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 ,

Ruby+MySQL on Ubuntu

without comments

This post goes through installing and configuring Ruby and Ruby on Rails for MySQL. The first step requires updating the Ubuntu OS:

sudo apt-get update

Interestingly, I found that the man-db service had inadvertently stopped. It raised the following error:

E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem.

You run this command to find the problem with the dpkg utility:

sudo dpkg --configure -a

It returned:

Setting up man-db (2.10.2-1) ...
Updating database of manual pages ...
man-db.service is a disabled or a static unit not running, not starting it.

The following command started the man-db service:

sudo systemctl start man-db.service

Next, you install the prerequisite packages with this command:

sudo apt-get install -y git-core zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev software-properties-common libffi-dev

Display detailed console log →

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Note, selecting 'git' instead of 'git-core'
build-essential is already the newest version (12.9ubuntu3).
build-essential set to manually installed.
libreadline-dev is already the newest version (8.1.2-1).
libreadline-dev set to manually installed.
git is already the newest version (1:2.34.1-1ubuntu1.10).
git set to manually installed.
software-properties-common is already the newest version (0.99.22.9).
zlib1g-dev is already the newest version (1:1.2.11.dfsg-2ubuntu9.2).
zlib1g-dev set to manually installed.
The following additional packages will be installed:
  libssl3
Suggested packages:
  libcurl4-doc libidn11-dev libkrb5-dev libldap2-dev librtmp-dev libssh2-1-dev
  sqlite3-doc libssl-doc libyaml-doc
The following NEW packages will be installed:
  libcurl4-openssl-dev libffi-dev libsqlite3-dev libxml2-dev libxslt1-dev
  libyaml-dev sqlite3
The following packages will be upgraded:
  libssl-dev libssl3
2 upgraded, 7 newly installed, 0 to remove and 18 not upgraded.
Need to get 7,426 kB of archives.
After this operation, 12.8 MB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libssl-dev amd64 3.0.2-0ubuntu1.13 [2,374 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libssl3 amd64 3.0.2-0ubuntu1.13 [1,902 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libcurl4-openssl-dev amd64 7.81.0-1ubuntu1.15 [386 kB]
Get:4 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libsqlite3-dev amd64 3.37.2-2ubuntu0.3 [846 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libxml2-dev amd64 2.9.13+dfsg-1ubuntu0.3 [804 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libxslt1-dev amd64 1.1.34-4ubuntu0.22.04.1 [219 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 sqlite3 amd64 3.37.2-2ubuntu0.3 [768 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libffi-dev amd64 3.4.2-4 [63.7 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libyaml-dev amd64 0.2.2-1build2 [62.8 kB]
Fetched 7,426 kB in 1s (5,467 kB/s)
Preconfiguring packages ...
(Reading database ... 246735 files and directories currently installed.)
Preparing to unpack .../libssl-dev_3.0.2-0ubuntu1.13_amd64.deb ...
Unpacking libssl-dev:amd64 (3.0.2-0ubuntu1.13) over (3.0.2-0ubuntu1.12) ...
Preparing to unpack .../libssl3_3.0.2-0ubuntu1.13_amd64.deb ...
Unpacking libssl3:amd64 (3.0.2-0ubuntu1.13) over (3.0.2-0ubuntu1.12) ...
Setting up libssl3:amd64 (3.0.2-0ubuntu1.13) ...
Selecting previously unselected package libcurl4-openssl-dev:amd64.
(Reading database ... 246735 files and directories currently installed.)
Preparing to unpack .../0-libcurl4-openssl-dev_7.81.0-1ubuntu1.15_amd64.deb ...
Unpacking libcurl4-openssl-dev:amd64 (7.81.0-1ubuntu1.15) ...
Selecting previously unselected package libsqlite3-dev:amd64.
Preparing to unpack .../1-libsqlite3-dev_3.37.2-2ubuntu0.3_amd64.deb ...
Unpacking libsqlite3-dev:amd64 (3.37.2-2ubuntu0.3) ...
Selecting previously unselected package libxml2-dev:amd64.
Preparing to unpack .../2-libxml2-dev_2.9.13+dfsg-1ubuntu0.3_amd64.deb ...
Unpacking libxml2-dev:amd64 (2.9.13+dfsg-1ubuntu0.3) ...
Selecting previously unselected package libxslt1-dev:amd64.
Preparing to unpack .../3-libxslt1-dev_1.1.34-4ubuntu0.22.04.1_amd64.deb ...
Unpacking libxslt1-dev:amd64 (1.1.34-4ubuntu0.22.04.1) ...
Selecting previously unselected package sqlite3.
Preparing to unpack .../4-sqlite3_3.37.2-2ubuntu0.3_amd64.deb ...
Unpacking sqlite3 (3.37.2-2ubuntu0.3) ...
Selecting previously unselected package libffi-dev:amd64.
Preparing to unpack .../5-libffi-dev_3.4.2-4_amd64.deb ...
Unpacking libffi-dev:amd64 (3.4.2-4) ...
Selecting previously unselected package libyaml-dev:amd64.
Preparing to unpack .../6-libyaml-dev_0.2.2-1build2_amd64.deb ...
Unpacking libyaml-dev:amd64 (0.2.2-1build2) ...
Setting up libyaml-dev:amd64 (0.2.2-1build2) ...
Setting up libffi-dev:amd64 (3.4.2-4) ...
Setting up libxml2-dev:amd64 (2.9.13+dfsg-1ubuntu0.3) ...
Setting up libsqlite3-dev:amd64 (3.37.2-2ubuntu0.3) ...
Setting up libcurl4-openssl-dev:amd64 (7.81.0-1ubuntu1.15) ...
Setting up libssl-dev:amd64 (3.0.2-0ubuntu1.13) ...
Setting up sqlite3 (3.37.2-2ubuntu0.3) ...
Setting up libxslt1-dev:amd64 (1.1.34-4ubuntu0.22.04.1) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for install-info (6.8-4build1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.6) ...

Use the cd command to change to the student home directory. Clone the asdf as the multiple runtime version manager with this command:

git clone https://github.com/excid3/asdf.git ~/.asdf

The following is the output of the git clone command:

Cloning into '/home/student/.asdf'...
remote: Enumerating objects: 8756, done.
remote: Counting objects: 100% (829/829), done.
remote: Compressing objects: 100% (476/476), done.
remote: Total 8756 (delta 428), reused 657 (delta 334), pack-reused 7927
Receiving objects: 100% (8756/8756), 3.10 MiB | 4.29 MiB/s, done.
Resolving deltas: 100% (5148/5148), done.

Next, you fix your .bashrc file by adding the following components:

echo '. "$HOME/.asdf/asdf.sh"' >> ~/.bashrc
echo '. "$HOME/.asdf/completions/asdf.bash"' >> ~/.bashrc
echo 'legacy_version_file = yes' >> ~/.asdfrc
echo 'export EDITOR="code --wait"' >> ~/.bashrc

Source the modifies shell, which you can do like this:

exec $SHELL

or, like:

. ${HOME}/.bashrc

Add the following asdf plug-ins:

asdf plugin add ruby
asdf plugin add nodejs

Install Ruby with the following command:

asdf install ruby 3.3.0

Display detailed console log →

Downloading ruby-build...
==> Downloading ruby-3.3.0.tar.gz...
-> curl -q -fL -o ruby-3.3.0.tar.gz https://cache.ruby-lang.org/pub/ruby/3.3/ruby-3.3.0.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 21.0M  100 21.0M    0     0  10.1M      0  0:00:02  0:00:02 --:--:-- 10.1M
==> Installing ruby-3.3.0...
-> ./configure "--prefix=$HOME/.asdf/installs/ruby/3.3.0" --enable-shared --with-ext=openssl,psych,+
-> make -j 2
-> make install
==> Installed ruby-3.3.0 to /home/student/.asdf/installs/ruby/3.3.0
asdf: Warn: You have configured asdf to preserve downloaded files (with always_keep_download=yes or --keep-download). But
asdf: Warn: the current plugin (ruby) does not support that. Downloaded files will not be preserved.

Install Ruby Global with this syntax:

asdf global ruby 3.3.0

Update the Ruby Gems with this command:

gem update --system

Display detailed console log →

Updating rubygems-update
Fetching rubygems-update-3.5.5.gem
Successfully installed rubygems-update-3.5.5
Parsing documentation for rubygems-update-3.5.5
Installing ri documentation for rubygems-update-3.5.5
Done installing documentation for rubygems-update after 1 seconds
Parsing documentation for rubygems-update-3.5.5
Done installing documentation for rubygems-update after 0 seconds
Installing RubyGems 3.5.5
  Successfully built RubyGem
  Name: bundler
  Version: 2.5.5
  File: bundler-2.5.5.gem
Bundler 2.5.5 installed
RubyGems 3.5.5 installed
Regenerating binstubs
Regenerating plugins
Parsing documentation for rubygems-3.5.5
Installing ri documentation for rubygems-3.5.5
 
# 3.5.5 / 2024-01-18
 
## Enhancements:
 
* Installs bundler 2.5.5 as a default gem.
 
## Bug fixes:
 
* Fix `require` activation conflicts when requiring default gems under
  some situations. Pull request
  [#7379](https://github.com/rubygems/rubygems/pull/7379) by
  deivid-rodriguez
* Use cache_home instead of data_home in default_spec_cache_dir. Pull
  request [#7331](https://github.com/rubygems/rubygems/pull/7331) by mrkn
 
## Documentation:
 
* Use squiggly heredocs in `Gem::Specification#description` documentation,
  so it doesn't add leading whitespace. Pull request
  [#7373](https://github.com/rubygems/rubygems/pull/7373) by bravehager
 
# 3.5.4 / 2024-01-04
 
## Enhancements:
 
* Always avoid "Updating rubygems-update" message. Pull request
  [#7335](https://github.com/rubygems/rubygems/pull/7335) by
  deivid-rodriguez
* Installs bundler 2.5.4 as a default gem.
 
## Bug fixes:
 
* Make `gem update --system` respect ruby version constraints. Pull
  request [#7334](https://github.com/rubygems/rubygems/pull/7334) by
  deivid-rodriguez
 
 
------------------------------------------------------------------------------
 
RubyGems installed the following executables:
	/home/student/.asdf/installs/ruby/3.3.0/bin/gem
	/home/student/.asdf/installs/ruby/3.3.0/bin/bundle
	/home/student/.asdf/installs/ruby/3.3.0/bin/bundler
 
Ruby Interactive (ri) documentation was installed. ri is kind of like man 
pages for Ruby libraries. You may access it like this:
  ri Classname
  ri Classname.class_method
  ri Classname#instance_method
If you do not wish to install this documentation in the future, use the
--no-document flag, or set it as the default in your ~/.gemrc file. See
'gem help env' for details.
 
RubyGems system software updated

You can confirm your Ruby install with two commands. First, use the which utility to check the Ruby install:

which -a ruby

It should return:

/home/student/.asdf/shims/ruby

Then, check the Ruby version:

ruby -v

It should return:

ruby 3.3.0 (2023-12-25 revision 5124f9ac75) [x86_64-linux]

Assuming you’ve installed and configured MySQL 8 on Ubuntu, you need this additional library to support the necessary Ruby Gem:

sudo apt-get install -y libmysqlclient-dev

Now, you can install the current MySQL Ruby Gem:

gem install mysql2

You can now write a mysql_connection.rb program to verify a connection to the MySQL 8 database, like:

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
begin
  # Create new database connection.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.query('SELECT version() AS version')
 
  # Read through the result set hash.
  stmt.each do | row |
    puts "#{row['version']}"
  end
  # Release the result set resources.
  stmt.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to the MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

Call the program with this syntax:

ruby mysql_connection.rb

It should return:

Connected to the MySQL database server.

You can verify the version with this mysql_version.rb program:

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
begin
  # Create new database connection.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  rs = db.query('SELECT version() AS version')
 
  # Read through the result set hash.
  rs.each do | row |
    puts "#{row['version']}"
  end
 
  # Release the result set resources.
  rs.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to the MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

On Ubuntu, it should return:

8.0.35-0ubuntu0.22.04.1

If you don’t know anything about the mysql2 Ruby Gem, you should read the documentation. It’s very concise and requires a basic understanding of Ruby programming. The two specific pages who may want to check for the next examples are:

The mysql_version.rb version uses the known string literal for columns or column aliases returned by the SQL statement, which becomes the stmt (or statement) in the program. The next program eliminates the need to enumerate with the text-based columns from the query by using the Statement#fields array values by use of a numeric index. The numeric index returns the field names from the Statement#fields class to use in as the name for values in the Result#fields value found in the row variable of the for loop.

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
# Begin block.
begin
  # Create a new connection resource.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.query("SELECT   DISTINCT i.item_title, ra.rating " +       \
                  "FROM     item i INNER JOIN rating_agency ra " +     \
                  "ON       i.item_rating_id = ra.rating_agency_id " + \
                  "WHERE    ra.rating_agency = 'MPAA'" +               \
                  "ORDER BY 1")
 
  # Read through the result set hash.
  stmt.each do | row |
    out = ""
    i = 0
    while i < stmt.fields.count()
      # Check when not last column and use the:
      #   - Hash returned by the result set for the value, and
      #   - String array value returned by the statement object
      #     as the name value of the hash by leveraging its 
      #     numeric index.
      if i < stmt.fields.count() - 1
        out += "#{row[stmt.fields[i]]}"
        out += ", "
      else
        out += "#{row[stmt.fields[i]]}"
      end
      i += 1
    end
    puts "#{out}"
  end
 
  # Release the result set resources.
  stmt.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

It returns the select two columns from the query:

A Man for All Seasons, G
Around the World in 80 Days, G
Beau Geste, PG
Brave Heart, R
Camelot, G
Casino Royale, PG-13
...
Tomorrow Never Dies, PG-13
Tora! Tora! Tora!, G
Tron, PG

The following mysql_query_params.rb Ruby example accepts a single argument to leverage a wild card query in MySQL:

require 'rubygems'
require 'mysql2'
 
# Input external arguments.
arguments = ARGV
 
# Check for one input parameter and substitute an empty string
# when one isn't found.
if arguments.length == 1
  argument = arguments[0]
else
  argument = ""
end
 
# Begin block.
begin
  # Create a new connection resource.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.prepare("SELECT   DISTINCT i.item_title, ra.rating " +       \
                    "FROM     item i INNER JOIN rating_agency ra " +     \
                    "ON       i.item_rating_id = ra.rating_agency_id " + \
                    "WHERE    ra.rating_agency = 'MPAA'" +               \
                    "AND      i.item_title LIKE CONCAT(?,'%')" +         \
                    "ORDER BY 1")
 
  # Bind the variable into the query.
  rs = stmt.execute(argument)
 
  # Read through the result set hash.
  rs.each do | row |
    out = ""
    i = 0
    while i < rs.fields.count()
      # Check when not last column and use the:
      #   - Hash returned by the result set for the value, and
      #   - String array value returned by the statement object
      #     as the name value of the hash by leveraging its 
      #     numeric index.
      if i < rs.fields.count() - 1
        out += "#{row[rs.fields[i]]}"
        out += ", "
      else
        out += "#{row[rs.fields[i]]}"
      end
      i += 1
    end
    puts "#{out}"
  end
 
  # Release the result set resources.
  rs.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

If you call the mysql_query_params.rb program with this syntax:

ruby mysql_aquery_params.rb Harry

It’ll return the following from the studentdb database:

Harry Potter and the Chamber of Secrets, PG
Harry Potter and the Deathly Hallows, Part 1, PG-13
Harry Potter and the Deathly Hallows, Part 2, PG-13
Harry Potter and the Goblet of Fire, PG-13
Harry Potter and the Half Blood Prince, PG
Harry Potter and the Order of the Phoenix, PG-13
Harry Potter and the Prisoner of Azkaban, PG
Harry Potter and the Sorcerer's Stone, PG

After that, you should install Rails (check for current version beyond 1/2024). Install Ruby Global with this syntax:

gem install rails -v 7.1.3

Check the version installed:

rails -v

It should return:

Rails 7.1.3

Run this command to enable Rails for MySQL 8:

rails new myapp -d mysql

If you want to configure a username and password for MySQL, edit the config/database.yml file.

As always, I hope this helps somebody looking for step-by-step guide.

Written by maclochlainn

February 3rd, 2024 at 4:57 pm

Posted in Linux,MySQL,MySQL 8,Ruby,Ubuntu

Tagged with , , ,

Ubuntu, Perl & MySQL

without comments

Configuring Perl to work with MySQL is straight forward. While Perl is installed generally, you may need to install the libdbd-mysql-perl library.

You install it as a sudoer user with this syntax:

sudo apt install -y libdbd-mysql-perl

Display detailed console log →

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libmysqlclient21
The following NEW packages will be installed:
  libdbd-mysql-perl libmysqlclient21
0 upgraded, 2 newly installed, 0 to remove and 12 not upgraded.
Need to get 1,389 kB of archives.
After this operation, 7,143 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libmysqlclient21 amd64 8.0.35-0ubuntu0.22.04.1 [1,301 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libdbd-mysql-perl amd64 4.050-5ubuntu0.22.04.1 [87.6 kB]
Fetched 1,389 kB in 1s (1,213 kB/s)     
Selecting previously unselected package libmysqlclient21:amd64.
(Reading database ... 235085 files and directories currently installed.)
Preparing to unpack .../libmysqlclient21_8.0.35-0ubuntu0.22.04.1_amd64.deb ...
Unpacking libmysqlclient21:amd64 (8.0.35-0ubuntu0.22.04.1) ...
Selecting previously unselected package libdbd-mysql-perl:amd64.
Preparing to unpack .../libdbd-mysql-perl_4.050-5ubuntu0.22.04.1_amd64.deb ...
Unpacking libdbd-mysql-perl:amd64 (4.050-5ubuntu0.22.04.1) ...
Setting up libmysqlclient21:amd64 (8.0.35-0ubuntu0.22.04.1) ...
Setting up libdbd-mysql-perl:amd64 (4.050-5ubuntu0.22.04.1) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.5) ...

You can find the Perl version with the following version.pl program:

1
2
3
4

#!/usr/bin/perl -w
 
# Print the version.
print "Perl ".$]."\n";

The first line lets you call the program without prefacing the program name with perl. The first line invokes a subshell of perl by default. You just need to ensure the file has read and execute privileges to run by using the

chmod 755 version.pl

You call it with this:

./version.pl

It prints:

Perl 5.034000

The following static_query.pl Perl program uses the Perl DBI library to query and return a data set based on a static query.

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

#!/usr/bin/perl -w
 
# Use the DBI library.
use DBI;
use strict;
use warnings;
 
# Create a connection.
my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306"
                      ,"student","student",{'RaiseError' => 1});
 
# Create SQL statement.
my $sql = "SELECT i.item_title
           ,      ra.rating
           ,      cl.common_lookup_meaning
           FROM   item i INNER JOIN common_lookup cl
           ON     i.item_type = cl.common_lookup_id INNER JOIN rating_agency ra
           ON     i.item_rating_id = ra.rating_agency_id
           WHERE  i.item_title LIKE 'Harry%'
           AND    cl.common_lookup_type = 'BLU-RAY'";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
 
# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  my $rating = $row[1];
  my $lookup_meaning = $row[2];
  print "$item_title, $rating, $lookup_meaning\n";
}
 
# Close resources.
$sth->finish();

It returns the following rows from the sample database:

Harry Potter and the Sorcerer's Stone, PG, Blu-ray
Harry Potter and the Chamber of Secrets, PG, Blu-ray
Harry Potter and the Prisoner of Azkaban, PG, Blu-ray
Harry Potter and the Goblet of Fire, PG-13, Blu-ray

The following dynamic_query.pl Perl program uses the Perl DBI library to prepare a query, bind a local variable into the query, and return a data set based on a dynamic query.

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

#!/usr/bin/perl -w
 
# Use the DBI library.
use DBI;
use strict;
use warnings;
 
# Mimic a function parameter by using a local variable.
my $item_title_in = 'Star';
 
# Create a connection.
my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306"
                      ,"student","student",{'RaiseError' => 1});
 
# Create SQL statement.
my $sql = "SELECT i.item_title
           ,      ra.rating
           ,      cl.common_lookup_meaning
           FROM   item i INNER JOIN common_lookup cl
           ON     i.item_type = cl.common_lookup_id INNER JOIN rating_agency ra
           ON     i.item_rating_id = ra.rating_agency_id
           WHERE  i.item_title LIKE CONCAT(?,'%')
           AND    cl.common_lookup_type = 'BLU-RAY'";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Bind a variable to first parameter in the query string.
$sth->bind_param(1, $item_title_in);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
 
# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  my $rating = $row[1];
  my $lookup_meaning = $row[2];
  print "$item_title, $rating, $lookup_meaning\n";
}
 
# Close resources.
$sth->finish();

It returns the following rows from the sample database:

Star Wars II, PG, Blu-ray

You can replace lines 34 through 40 with the following to read any number of columns into a comma-delimited row return:

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

# Read through returned rows, assign elements explicitly to match SELECT-list.
while (my @row = $sth->fetchrow_array()) {
 
  # Read through a dynamic column list for column separated display.
  my $result = '';
  foreach(@row) {
    if (length($result) == 0) {
      $result = $_; }
    else {
      $result .= ", " . $_; }
  } 
 
  # Print comma-separted values by row.
  print $result . "\n"
}

It returns the following rows from the sample database:

Star Wars II, PG, Blu-ray

As always, I hope this helps the reader solve a problem.

Written by maclochlainn

January 8th, 2024 at 10:37 pm

Posted in Linux,MySQL,MySQL 8,Ubuntu,Unix

Tagged with , ,

SQL 1 v.s. SQL 3

without comments

In Alan Beaulieu’s wonderful book Learning SQL: Generate, Manipulate, and Retrieve Data, he uses a SQL 1 to demonstrates a CROSS JOIN in MySQL counting from 1 to 100. My students always find it difficult to read because the subqueries take so much space it makes it difficult to see the process, for example he gives this SQL 1 solution:

SELECT ones.x + tens.x + 1 AS counter
FROM
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x ) ones CROSS JOIN
 (SELECT 0 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 20 AS x UNION ALL
  SELECT 30 AS x UNION ALL
  SELECT 40 AS x UNION ALL
  SELECT 50 AS x UNION ALL
  SELECT 60 AS x UNION ALL
  SELECT 70 AS x UNION ALL
  SELECT 80 AS x UNION ALL
  SELECT 90 AS x ) tens
ORDER BY counter;

While anybody with a command of SQL should be able to see how it works, for those new to SQL it’s difficult. It’s more effective to use a Common Table Expression with the WITH clause because the derived tables become variables in the scope of the WITH clause and the final query works more like a CROSS JOIN between two tables:

WITH ones AS
 (SELECT 0 AS x UNION ALL
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 4 AS x UNION ALL
  SELECT 5 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 7 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 9 AS x )
, tens AS
 (SELECT 0 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 20 AS x UNION ALL
  SELECT 30 AS x UNION ALL
  SELECT 40 AS x UNION ALL
  SELECT 50 AS x UNION ALL
  SELECT 60 AS x UNION ALL
  SELECT 70 AS x UNION ALL
  SELECT 80 AS x UNION ALL
  SELECT 90 AS x )
SELECT ones.x + tens.x + 1 AS counter
FROM ones CROSS JOIN tens
ORDER BY counter;

As always, I hope this helps somebody trying to sort out the syntax and workflow.

Written by maclochlainn

November 7th, 2023 at 10:10 pm

Posted in MySQL,MySQL 8,sql

Tagged with

MySQL on Ubuntu

without comments

Working with my students to create an Ubuntu virtual environment for Python development with the MySQL database. After completing the general provisioning covered in this older post, I’d recommend you create a python symbolic link before installing the MySQL-Python driver.

sudo ln -s /usr/bin/python3 /usr/bin/python

You install the Python development driver with the following:

sudo apt-get -y install python3-mysql.connector

Create a python_connect.py file to test your Python deployment’s ability to connect to the MySQL database:

#!/usr/bin/python
 
# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='localhost',
                                database='sakila')
 
  # Print the value.
  print("Database connection resolved.")
 
# 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(e)
 
# Close the connection when the try block completes.
else:
  cnx.close()

You should change the file permissions of the python_connect.py to read-write-execute as owner, and read-execute as group and other with the following command:

chmod 755 python_connect.py

Then, you can test your python_connect.py program from the local directory with the following command:

./python_connect.py

You see the following output if it works:

Database connection resolved.

As alway, I hope those looking for a code complete solutionk

Written by maclochlainn

July 14th, 2023 at 2:29 pm

Posted in Linux,MySQL,MySQL 8,MySQL Connect,Python 3.x,Ubuntu

Tagged with ,

MySQL Workbench Fits

without comments

My students wanted an illustration of where MySQL Workbench fits within the MySQL database. So, I overlaid it in this drawing from my old book on comparative SQL syntax for Oracle and MySQL. Anybody else have a cool alternative illustration?

The idea is the lightening bolt transmits an interactive call and reading a script file submits a call paradigm.

More or less MySQL processes a static query in the panel, like the following Python program process the dynamic query (on lines 71-78) with parameters sent when calling the Python script.

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

#!/usr/bin/python
# ------------------------------------------------------------
#  Name: mysql-query2.py
#  Date: 20 Aug 2019
# ------------------------------------------------------------
#  Purpose:
#  -------
#    The program shows you how to provide agruments, convert
#    from a list to individual variables of the date type.
#
#    You can call the program:
#
#    ./mysql-query3.py 2001-01-01 2003-12-31
#
# ------------------------------------------------------------
 
# Import the library.
import sys
import mysql.connector
from datetime import datetime
from datetime import date
from mysql.connector import errorcode
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
start_date = ""
end_date = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
#  Check and process argument list.
# ============================================================
#  If there are less than two arguments provide default values.
#  Else enumerate and convert strings to dates.
# ============================================================
if (len(argumentList) < 2):
  # Set a default start date.
  if (isinstance(start_date,str)):
    start_date = date(1980, 1, 1)
 
  # Set the default end date.
  if (isinstance(end_date,str)):
    end_date = datetime.date(datetime.today())
else:
  # Enumerate through the argument list where beginDate precedes endDate as strings.
  try:
    for i, s in enumerate(argumentList):
      if (i == 0):
        start_date = datetime.date(datetime.fromisoformat(s))
      elif (i == 1):
        end_date = datetime.date(datetime.fromisoformat(s))
  except ValueError:
    print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
#  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 = ("SELECT CASE "
           "         WHEN item_subtitle IS NULL THEN CONCAT('''',item_title,'''') "
           "         ELSE CONCAT('''',item_title,': ',item_subtitle,'''') "
           "         END AS title, "
           "release_date "
           "FROM item "
           "WHERE release_date BETWEEN %s AND %s "
           "ORDER BY item_title")
 
  # Execute cursor.
  cursor.execute(query, (start_date, end_date))
 
  # Display the rows returned by the query.
  for (title, release_date) in cursor:
    print("{}, {:%d-%b-%Y}".format(title, release_date))
 
  # 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()

You could call this type of script from the Linux CLI (Command-Line Interface), like this:

./mysql-query3.py '2003-01-01' '2003-12-31' 2>/dev/null

As always, I hope this helps those looking to understand things.

Written by maclochlainn

June 17th, 2023 at 3:02 pm

Posted in MySQL,MySQL 8,MySQL Workbench

Tagged with ,

MySQL Posts Summary

without comments

Here’s a quick catalog for my students of PowerShell, JavaScript, and Python examples connecting to MySQL:

As always, I hope this helps those looking for a code sample.

Written by maclochlainn

June 16th, 2023 at 12:38 am

Posted in JavaScript,Linux,MySQL,MySQL 8,PowerShell,Python 3.x

Tagged with ,

« Older Entries