Python MySQL Query
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.
Subscribe via RSS