MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

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

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

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

Install Ruby Global with this syntax:

asdf global ruby 3.3.0

Update the Ruby Gems with this command:

gem update --system

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

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

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

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

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

MySQL Posts Summary

without comments

Written by maclochlainn

June 16th, 2023 at 12:38 am

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

    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

MySQL @SQL_MODE

without comments

Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.

So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

You need to change it to the following in an editor with Administrative privileges:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

Then, you need to connect to the services by launching services.msc from the command prompt. In the list of services find MYSQL80 service and restart it. You can verify it by connecting to the MySQL 8.0.* server and running the following SQL query:

SELECT @@SQL_MODE:

That’s how you convert Windows to use only traditional group by behaviors in SQL. As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 13th, 2023 at 12:55 pm

AlmaLinux Libraries

without comments

I discovered a dependency for MySQL Workbench on AlmaLinux 8 installation. I neglected to fully cover it when I documented the installation in a VM of AlmaLinux 9. I go back later and update that entry but for now you need the following dependencies:

proj-6.3.2-4.el8.x86_64.rpm
proj-datumgrid-1.8-6.3.2.4.el8.noarch.rpm
proj-devel-6.3.2-4.el8.x86_64.rpm

Install like this:

sudo dnf install -y *.rpm

Log file:

Last metadata expiration check: 3:01:53 ago on Fri 10 Feb 2023 03:37:49 AM UTC.
Dependencies resolved.
==========================================================================================
 Package                Architecture   Version                 Repository            Size
==========================================================================================
Installing:
 proj                   x86_64         6.3.2-4.el8             @commandline         2.0 M
 proj-datumgrid         noarch         1.8-6.3.2.4.el8         @commandline         5.4 M
 proj-devel             x86_64         6.3.2-4.el8             @commandline          89 k
 
Transaction Summary
==========================================================================================
Install  3 Packages
 
Total size: 7.5 M
Installed size: 17 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                  1/1 
  Installing       : proj-datumgrid-1.8-6.3.2.4.el8.noarch                            1/3 
  Installing       : proj-6.3.2-4.el8.x86_64                                          2/3 
  Installing       : proj-devel-6.3.2-4.el8.x86_64                                    3/3 
  Running scriptlet: proj-devel-6.3.2-4.el8.x86_64                                    3/3 
  Verifying        : proj-6.3.2-4.el8.x86_64                                          1/3 
  Verifying        : proj-datumgrid-1.8-6.3.2.4.el8.noarch                            2/3 
  Verifying        : proj-devel-6.3.2-4.el8.x86_64                                    3/3 
 
Installed:
  proj-6.3.2-4.el8.x86_64                 proj-datumgrid-1.8-6.3.2.4.el8.noarch          
  proj-devel-6.3.2-4.el8.x86_64          
 
Complete!

A quick update while installing AlmaLinux for AWS.

Written by maclochlainn

February 9th, 2023 at 11:47 pm