MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

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 ,

MySQL on Ubuntu

without comments

Fresh install of Ubuntu on my MacBook Pro i7 because Apple said the OS X was no longer upgradable. Time to install and configure MySQL Server. These are the steps to install MySQL on the Ubuntu Desktop.

Installation

  1. Update the Ubuntu OS by checking for, inspecting, and upgrading any available updates with the following commands: 

    sudo apt update
sudo apt list
sudo apt upgrade
  2. Check for available MySQL Server packages with this command: 

    apt-cache search binaries | grep -i mysql

    It should return:

    mysql-server - MySQL database server binaries and system database setup
mysql-server-8.0 - MySQL database server binaries and system database setup
mysql-server-core-8.0 - MySQL database server binaries
default-mysql-server - MySQL database server binaries and system database setup (metapackage)
default-mysql-server-core - MySQL database server binaries (metapackage)
mariadb-server-10.6 - MariaDB database core server binaries
mariadb-server-core-10.6 - MariaDB database core server files
  3. Check for more details on the MySQL packages with this command: 

    apt info -a mysql-server-8.0
  4. Install MySQL Server packages with this command: 

    sudo apt install mysql-server-8.0
  5. Start the MySQL Server service with this command: 

    sudo systemctl start mysql.service
  6. Before you can run the mysql_secure_installation script, you must set the root password. If you skip this step the mysql_secure_installation script will enter an infinite loop and lock your terminal session. Log in to the mysql monitor with the following command: 

    sudo mysql

    Enter a password with the following command (password is an insecure example):

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'C4nGet1n!';

    Quit the mysql monitor session:

    quit;
  7. Run the mysql_secure_installation script with this command: 

    sudo mysql_secure_installation

    Here’s the typical output from running the mysql_secure_installation script:

    Securing the MySQL server deployment.
 
Enter password for user root: 
 
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
 
Press y|Y for Yes, any other key for No: Y
 
There are three levels of password validation policy:
 
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
 
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.
 
Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : N
 
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
 
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.
 
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
 
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.
 
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
 
 
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.
 
 - Removing privileges on test database...
Success.
 
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
 
All done!

Configuration

The next step is configuration. It requires setting up the sample sakila and studentdb database. The syntax has changed from prior releases. Here are the new three steps:

  1. Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user: 

    mysql> GRANT ALL ON *.* TO 'root'@'localhost';
  2. Download the sakila database, which you can download from this site. Click on the sakila database’s TGZ download.

    When you download the sakila zip file it creates a sakila-db folder in the /home/student/Downloads directory. Copy the sakila-db folder into the /home/student/Data/sakila directory. Then, change to the /home/student/Data/sakila/sakila-db directory, connect to mysql as the root user, and run the following command:

    mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql
mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql
  3. Create the studentdb database with the following command as the MySQL root user: 

    mysql> CREATE DATABASE studentdb;
  4. Create the user with a clear English password and grant the user student full privileges on the sakila and studentdb databases: 

    mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Stud3nt!';
mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost';
mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';

You can now connect to a sandboxed sakila database with the student user’s credentials, like:

mysql -ustudent -p -Dsakila

or, you can now connect to a sandboxed studentdb database with the student user’s credentials, like:

mysql -ustudent -p -Dstudentdb

MySQL Workbench Installation

sudo snap install mysql-workbench-community

You have now configure the MySQL Server 8.0.

Written by maclochlainn

May 23rd, 2023 at 4:18 pm

Posted in LAMP,Linux,MySQL,MySQL 8,MySQL Shell,Unix

Tagged with ,

« Older Entries