MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL 8’ Category

Python MySQL Query

without comments

Somebody asked me how to expand a prior example with the static variables so that it took arguments at the command line for the variables. This example uses Python 3 new features in the datetime package.

There’s a small trick converting the string arguments to date data types. Here’s a quick example that shows you how to convert the argument list into individual date data type variables:

#!/usr/bin/python3
 
# include standard modules
import sys
from datetime import datetime
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
beginDate = ""
endDate = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
# Enumerate through the argument list where beginDate precedes endDate as strings.
try:
  for i, s in enumerate(argumentList):
    if (i == 0):
      beginDate = datetime.date(datetime.fromisoformat(s))
    elif (i == 1):
      endDate = datetime.date(datetime.fromisoformat(s))
except ValueError:
  print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
# Print the processed values and types.
print("Begin Date: [",beginDate,"][",type(beginDate),"]")
print("End Date:   [",endDate,"][",type(endDate),"]")

Assume you call this arguments.py. Then, you call it with valid conforming date format value like the following command-line example:

./arguments.py 2001-01-01 2003-12-31

It returns the arguments after they have been converted to date data types. The results should look like this:

Begin Date:  1991-01-01 [ <class 'datetime.date'> ]
End Date:    2004-12-31 [ <class 'datetime.date'> ]

The next Python example accepts dynamic arguments at the command line to query the MySQL database:

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

#!/usr/bin/python3
 
# 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 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 (item_name, release_date) in cursor:
    print("{}, {:%d-%b-%Y}".format(item_name, release_date))
 
#  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.
finally:
  cnx.close()

You can call the python-mysql-query.py program with the following syntax:

./python-mysql-query.py 2001-01-01 2003-12-31

It returns the films between 1 Jan 2001 and 31 Dec 2003, like this:

Clear and Present Danger: Special Collector's Edition, 06-May-2003
Die Another Day: 2-Disc Ultimate Version, 03-Jun-2003
Die Another Day, 03-Jun-2003
Die Another Day, 03-Jun-2003
Golden Eye, 03-Jun-2003
Golden Eye: Special Edition, 03-Jun-2003
Harry Potter and the Chamber of Secrets, 28-May-2002
Harry Potter and the Chamber of Secrets: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone, 28-May-2002
Harry Potter and the Sorcerer's Stone: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone: Full Screen Edition, 28-May-2002
MarioKart: Double Dash, 17-Nov-2003
Pirates of the Caribbean, 30-Jun-2003
RoboCop, 24-Jul-2003
Splinter Cell: Chaos Theory, 08-Apr-2003
Star Wars II: Attack of the Clones, 16-May-2002
Star Wars II: Attack of the Clones, 16-May-2002
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 30-Jun-2003
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 16-May-2002

As always, I hope this helps somebody who wants to learn how to use Python with the MySQL database.

Written by maclochlainn

September 6th, 2019 at 10:31 pm

Posted in Fedora,Linux,MySQL,MySQL 8,Python,Python 3.x

Tagged with ,

MySQL Python Connector

with one comment

While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post.

You use the following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list:

yum install -y mysql-connector-python*

Display detailed console log →

Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT.
Dependencies resolved.
=====================================================================================================================================
 Package                                  Architecture       Version                    Repository                              Size
=====================================================================================================================================
Installing:
 mysql-connector-python                   x86_64             8.0.17-1.fc30              mysql-connectors-community             435 k
 mysql-connector-python-cext              x86_64             8.0.17-1.fc30              mysql-connectors-community             7.7 M
 mysql-connector-python3                  x86_64             8.0.17-1.fc30              mysql-connectors-community             429 k
 mysql-connector-python3-cext             x86_64             8.0.17-1.fc30              mysql-connectors-community             7.7 M
Installing dependencies:
 python2-protobuf                         noarch             3.6.1-3.fc30               fedora                                 563 k
 python3-protobuf                         noarch             3.6.1-3.fc30               fedora                                 568 k
 
Transaction Summary
=====================================================================================================================================
Install  6 Packages
 
Total download size: 17 M
Installed size: 89 M
Downloading Packages:
(1/6): python3-protobuf-3.6.1-3.fc30.noarch.rpm                                                      1.0 MB/s | 568 kB     00:00    
(2/6): python2-protobuf-3.6.1-3.fc30.noarch.rpm                                                      994 kB/s | 563 kB     00:00    
(3/6): mysql-connector-python-8.0.17-1.fc30.x86_64.rpm                                               481 kB/s | 435 kB     00:00    
(4/6): mysql-connector-python3-8.0.17-1.fc30.x86_64.rpm                                              612 kB/s | 429 kB     00:00    
(5/6): mysql-connector-python-cext-8.0.17-1.fc30.x86_64.rpm                                          3.8 MB/s | 7.7 MB     00:02    
(6/6): mysql-connector-python3-cext-8.0.17-1.fc30.x86_64.rpm                                         4.2 MB/s | 7.7 MB     00:01    
-------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                5.4 MB/s |  17 MB     00:03     
warning: /var/cache/dnf/mysql-connectors-community-8bcc2bd350b53f70/packages/mysql-connector-python-8.0.17-1.fc30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
MySQL Connectors Community                                                                           7.0 MB/s |  27 kB     00:00    
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                             1/1 
  Installing       : python3-protobuf-3.6.1-3.fc30.noarch                                                                        1/6 
  Installing       : python2-protobuf-3.6.1-3.fc30.noarch                                                                        2/6 
  Installing       : mysql-connector-python-8.0.17-1.fc30.x86_64                                                                 3/6 
  Installing       : mysql-connector-python3-8.0.17-1.fc30.x86_64                                                                4/6 
  Installing       : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                                                           5/6 
  Installing       : mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            6/6 
  Running scriptlet: mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            6/6 
  Verifying        : python2-protobuf-3.6.1-3.fc30.noarch                                                                        1/6 
  Verifying        : python3-protobuf-3.6.1-3.fc30.noarch                                                                        2/6 
  Verifying        : mysql-connector-python-8.0.17-1.fc30.x86_64                                                                 3/6 
  Verifying        : mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            4/6 
  Verifying        : mysql-connector-python3-8.0.17-1.fc30.x86_64                                                                5/6 
  Verifying        : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                                                           6/6 
 
Installed:
  mysql-connector-python-8.0.17-1.fc30.x86_64                    mysql-connector-python-cext-8.0.17-1.fc30.x86_64                   
  mysql-connector-python3-8.0.17-1.fc30.x86_64                   mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                  
  python2-protobuf-3.6.1-3.fc30.noarch                           python3-protobuf-3.6.1-3.fc30.noarch                               
 
Complete!

Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.1 Connecting to MySQL Using Connector/Python here’s a test of the connection to MySQL 8.

# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
 
  # 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()

Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.4 Querying Data Using Connector/Python here’s a test of the connection to MySQL 8.

# Import the library.
import datetime
import mysql.connector
from mysql.connector import errorcode
 
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 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")
 
  # Set the start and end date.
  start_date = datetime.date(1991, 1, 1)
  end_date = datetime.date(2004, 12, 31)
 
  # Execute cursor.
  cursor.execute(query, (start_date, end_date))
 
  # Display the rows returned by the query.
  for (item_name, release_date) in cursor:
    print("{}, {:%d %b %Y}".format(item_name, 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()

If you run the above in Python 2.7 it works fine. It fails to parse successfully in Python 3.x because the print() function requires the parentheses all the time. You would need to re-write the except block, like this with the parentheses:

# 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

While it works without the parentheses in Python 2.7, it also works with the parentheses. That means the best practice is to write cross compatible code by always using the parentheses with the print() function.

As always, I hope this helps somebody.j

Written by maclochlainn

August 21st, 2019 at 1:44 am

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

Tagged with ,

MySQL on Fedora 30

with one comment

While updating my class image to Fedora 30, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.

So, I removed all those packages with the following syntax:

dnf remove `rpm -qa | grep akonadi`

Display detailed console log →

 

After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this syntax:

yum install -y community-mysql*

Display detailed console log →

 

Having installed MySQL Community Edition, I wanted to start the mysql service with this command:

sudo service mysqld start

Unfortunately, the service utility wasn’t installed. That surprised me. While I could have run this command:

systemctl start mysqld.service

A better solution was to install any missing code components. I determined that the service utility is part of the initscripts package; and I installed it with the following command:

sudo yum install -y initscripts

Display detailed console log →

Fedora Modular 30 - x86_64                    30 kB/s |  18 kB     00:00    
Fedora Modular 30 - x86_64 - Updates          40 kB/s |  17 kB     00:00    
Fedora 30 - x86_64 - Updates                  43 kB/s |  17 kB     00:00    
Fedora 30 - x86_64                            58 kB/s |  19 kB     00:00    
google-chrome-unstable                        12 kB/s | 1.3 kB     00:00    
google-chrome                                 16 kB/s | 1.3 kB     00:00    
Dependencies resolved.
=============================================================================
 Package            Architecture  Version               Repository      Size
=============================================================================
Installing:
 initscripts        x86_64        10.02-1.fc30          updates        202 k
 
Transaction Summary
=============================================================================
Install  1 Package
 
Total download size: 202 k
Installed size: 1.1 M
Downloading Packages:
initscripts-10.02-1.fc30.x86_64.rpm          296 kB/s | 202 kB     00:00    
-----------------------------------------------------------------------------
Total                                        162 kB/s | 202 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : initscripts-10.02-1.fc30.x86_64                     1/1 
  Running scriptlet: initscripts-10.02-1.fc30.x86_64                     1/1 
  Verifying        : initscripts-10.02-1.fc30.x86_64                     1/1 
 
Installed:
  initscripts-10.02-1.fc30.x86_64                                            
 
Complete!

Then, I ran the mysql_secure_installation script to secure the installation:

mysql_secure_installation

The script set the root user’s password, remove the anonymous user, disallow remote root login, and remove the test databases. Then, I verified connecting to the MySQL database with the following syntax:

mysql -uroot -ppassword

I enabled the MySQL Service to start with each reboot of the Fedora instance. I used the following command:

systemctl enable mysqld.service

It creates the following link:

ln -s '/etc/systemd/system/multi-user.target.wants/mysqld.service' '/usr/lib/systemd/system/mysqld.service'

The next step requires setting up a sample studentdb database. The syntax has changed from prior releases. Here are the three steps:

  1. Create the studentdb database with the following command as the MySQL root user: 

    mysql> CREATE DATABASE studentdb;
  2. 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';
  3. Create the user with a clear English password and grant the user student full privileges on the studentdb database: 

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

If you fail to specify mysql_native_password when creating the user and use the older syntax like the following example:

mysql> CREATE USER 'student'@'localhost' IDENTIFIED BY 'student';
mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost';

The GRANT command will raise the following error:

ERROR 1410 (42000): You are not allowed to create a user with GRANT

Written by maclochlainn

August 16th, 2019 at 1:02 am

Posted in MySQL,MySQL 8

Tagged with ,

Newer Entries »