MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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 ,

«
»