There is a well-known saying that there are two types of programs: those that are toys and those that access databases. Well, the project in this chapter is not a toy, so that should narrow it down for you. This project will enable you to manage your DVD inventory. It takes advantage of MySQL, a powerful open-source relational database management system (RDMS). Along the way, you'll learn how to do the following:
Work with a database
Connect to a database
Query the database
Add records
Modify records
Delete records
Output information in the database to a CSV file
The program is available for download at www.wrox.com
. To run it, simply go to a command prompt, and from the directory on your system where the Chapter 2 program files are located, type python dvd.py.
This will bring up a menu like the one shown here:
================================ DVD DATABASE ================================ 1 - Add a DVD to the database 2 - Search inventory 3 - Modify DVD record 4 - Delete DVD record 5 - Export listing to CSV 6 - Exit ================================ Enter a choice and press enter:
From here, as you can see, you can add a DVD to the database, search the inventory for DVDs based on search criteria, modify fields in a DVD record, delete a DVD record, export the list of DVDs to a CSV file, or exit the program. The following sections walk through each of these program features.
To install and run the application in this chapter, you'll need to install MySQL and the Python MySQLdb package. You can find instructions for downloading and installing MySQL and MySQLdb in Appendix B. After you do that, run the SQL script included in the program directory to create the table needed for this example.
Once it is installed, create a database and name it DVDCOLLECTION
. Then run the following SQL command to create the table we'll use in this exercise:
CREATE TABLE 'DVD' ( 'DVD_TITLE' varchar(50) default NULL, 'DVD_STAR_NAME' varchar(50) default NULL, 'DVD_COSTAR_NAME' varchar(50) default NULL, 'DVD_YEAR' varchar(50) default NULL 'DVD_GENRE' varchar(50) default NULL );
If you choose 1 and press Enter to add a DVD to the database, you'll be asked for the following information:
DVD title
DVD star name
DVD costar name
DVD year released
DVD genre (drama, horror, comedy, or romance)
The screen will look like the following:
=============================== ADD A DVD TO THE DATABASE: =============================== Enter the DVD title: American Gangster Enter the name of the movie's star: Denzel Washington Enter the name of the movie's costar: Russell Crowe Enter the year the movie was released: 2007 Enter the genre: - 1 for Drama, 2 for horror, 3 for comedy, 4 for romance:
In the preceding example, I entered DVD information for American Gangster (not on DVD yet, but I'm building my Christmas list). After I type a number corresponding to the genre for the movie (in this case, 1
for drama) and press Enter, I get the following prompt:
Record added - press enter to continue:
After you press Enter you are returned to the main menu.
If you encounter a problem while adding the record (for instance, if you don't provide all the information), you'll get an error message. For example, if you merely press Enter when prompted for the genre, you'll get the following message:
=============================== ADD A DVD TO THE DATABASE: =============================== Enter the DVD title: Princess Bride Enter the name of the movie's star: Cary Elwes Enter the name of the movie's costar: Robin Wright Enter the year the movie was released: 1987 Enter the genre: - 1 for Drama, 2 for horror, 3 for comedy, 4 for romance: ERROR ADDING RECORD! Press Enter to return to the menu:
If you type 2 and press Enter to search the DVD inventory, you'll be presented with the following prompt:
=============================== DVD LOOKUP: =============================== Enter the criteria to look up by: 1 - Movie title 2 - Star 3 - Costar 4 - Year released 5 - Genre Type a number and press enter:
As you can see, you can look up records based on any of the fields that make up a record. Each lookup is covered in the following sections.
If you type 1 and press Enter to search by movie title, you'll get the following prompt:
Enter the DVD title to search for:
Type the name of the movie exactly as it appears in the database and press Enter. You'll then get the result set output to the screen:
=============================== DVD SEARCH RESULTS: =============================== Title: American Gangster Star: Denzel Washington Costar: Russell Crowe Year released: 1995 Genre: : Drama =============================== Press enter to continue:
Press Enter to be returned to the menu.
If you type 2 and press Enter to search by the star's name, you'll get the following prompt:
Enter the DVD star name to search for:
Type the star's name exactly as it appears in the database and press Enter. You'll then get the result set output to the screen (the following example output shows how it looks if multiple DVDs are returned as a result of the search):
=============================== DVD SEARCH RESULTS: =============================== Title: Tommy Boy Star: Chris Farley Costar: David Spade Year: 1995 Genre: Comedy =============================== Title: Black Sheep Star: Chris Farley Costar: David Spade Year: 1996 Genre: Comedy =============================== Press enter to continue:
Press Enter to be returned to the menu.
If you type 3 and press Enter to search by the costar's name, you'll get the following prompt:
Enter the DVD costar name to search for:
Type the costar's name exactly as it appears in the database and press Enter. You'll then get the result set output to the screen, as shown in the following example:
=============================== DVD SEARCH RESULTS: =============================== Title: Runaway Bride Star: Julia Roberts Costar: Richard Gere
Year: 1999 Genre: Comedy =============================== Title: Pretty Woman Star: Julia Roberts Costar: Richard Gere Year: 1990 Genre: Comedy =============================== Press enter to continue:
Press Enter to be returned to the menu.
If you type 4 and press Enter to search by the year in which the movie was released, you'll get the following prompt:
Enter the DVD release year to search for:
Type the appropriate year and press Enter. You'll then get the result set output to the screen. In the following example, note that both movies shown are released in 1990, the year I entered as search criteria:
=============================== DVD SEARCH RESULTS: =============================== Title: Pretty Woman Star: Julia Roberts Costar: Richard Gere Year: 1990 Genre: Comedy =============================== Title: Ghost Star: Patrick Swayze Costar: Demi Moore Year: 1990 Genre: Drama =============================== Press enter to continue:
Press Enter to be returned to the menu.
If you type 4 and press Enter to search by genre, you'll be presented with the following screen:
Enter the genre to search for: 1 - Drama 2 - Horror 3 - Comedy 4 - Romance
When you type the number corresponding to the genre you want to search for, you'll get a result set consisting of all DVDs in the database associated with that genre:
=============================== DVD SEARCH RESULTS: =============================== Title: American Gangster Star: Denzel Washington Costar: Russell Crowe Year: 2007 Genre: Drama =============================== Title: Ghost Star: Patrick Swayze Costar: Demi Moore Year: 1990 Genre: Drama =============================== Press enter to continue:
Press Enter to be returned to the menu.
If no records are found, you'll get a screen that indicates that fact:
=============================== DVD SEARCH RESULTS: =============================== NO RECORDS FOUND =============================== Press enter to continue:
If you enter something other than 1–5 when prompted for search criteria, you'll get an appropriate error message:
=============================== DVD LOOKUP: =============================== Enter the criteria to look up by: 1 - Movie title 2 - Star 3 - Costar 4 - Year released 5 - Genre Type a number and press enter: 6 ERROR IN CHOICE! Press Enter to return to the menu:
In both error cases, pressing Enter takes you back to the main menu.
If you type 3 and press Enter to modify a record, you'll get the following initial prompt:
=============================== MODIFY A DVD RECORD: =============================== Enter the title of the DVD to modify:
Type the name of the movie exactly as it appears in the database. When you do, you'll get the following screen:
=============================== MODIFY A DVD RECORD: =============================== Enter the title of the DVD to modify: American Gangster =============================== DVD TO MODIFY: =============================== 1 - Title: American Gangster 2 - Star: Denzel Washington 3 - Costar: Russell Crowe 4 - Year: 2007 5 - Genre: Drama =============================== Type the number for the field you want to modify and press Enter:
As you can see, the program displays the information relating to the movie, whose title you entered, and asks you to indicate which field you'd like to modify. The following sections cover each field.
If you type 1 and press Enter to modify the DVD title, you'll get the following prompt:
Enter the new DVD title name:
Simply type in the new title that you'd like to use and press Enter. The program will then show you the modified record. In the following example, I had initially misspelled my movie "Amrician Gangster." Therefore, I chose option 3 to modify the name, typed in the name of the movie as it currently appeared in the database, and then changed the title text:
=============================== Enter the title of the DVD to modify: Amrician Gangster =============================== DVD TO MODIFY: =============================== 1 - Title: Amrician Gangster 2 - Star: Denzel Washington 3 - Costar: Russell Crowe 4 - Year: 2007 5 - Genre: Drama =============================== Type the number for the field you want to modify and press Enter: 1 Enter the new DVD title name: American Gangster =============================== MODIFIED RECORD: =============================== 1 - Title: American Gangster 2 - Star: Denzel Washington 3 - Costar: Russell Crowe 4 - Year: 2007 5 - Genre Drama =============================== Press enter to continue:
Press Enter to be returned to the menu.
If you type 2 from the main menu and press Enter to modify the name of the DVD's star, you'll get the following prompt:
Enter the new DVD star name:
Simply type in what you'd like to modify the star's name to, and then press Enter. The program will then show you the modified record, as in the "modify title" example shown in the preceding section.
Then press Enter and you will be returned to the menu.
If you type 3 and press Enter to modify the DVD costar name, you'll get the following prompt:
Enter the new DVD costar name:
Simply type in what you'd like to modify the costar's name to, and then press Enter. The program will then show you the modified record.
Press Enter to return to the menu.
If you type 4 and press Enter to modify the DVD release year, you'll get the following prompt:
Enter the new DVD year of release:
Simply type in what you'd like to modify the DVD release year to, and then press Enter. The program will then show you the modified record.
Press Enter to return to the menu.
If you type 5 and press Enter to modify the DVD genre, you'll get the following prompt:
Enter the genre to apply to this DVD: 1 - Drama 2 - Horror 3 - Comedy 4 - Romance Type the number for the genre you want to apply and press Enter:
Type the number corresponding to the genre you'd like to apply to your DVD, and then press Enter. The program will then show you the modified record, as shown in previous examples.
Pressing Enter will return you to the menu.
If you enter search criteria that does not match a title in the database, then you'll get an appropriate error:
=============================== MODIFY A DVD RECORD: =============================== Enter the title of the DVD to modify: American THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE! Press Enter to continue:
Similarly, if you enter invalid data when modifying a record, you'll get an error message, as shown in the following example, where I pressed Enter without entering a value for the genre to apply to a record:
=============================== Enter the genre to apply to this DVD: 1 - Drama 2 - Horror 3 - Comedy 4 - Romance Type the number for the genre you want to apply and press Enter: THERE WAS A PROBLEM MODIFYING THE RECORD Press Enter to continue:
In both error cases, pressing Enter takes you back to the main menu.
If you type 4 and press Enter to delete a DVD record, you'll be presented with the following prompt:
=============================== DELETE A DVD RECORD: =============================== Enter the title of the DVD to delete:
Simply enter the title of the DVD you want to delete, exactly as it appears in the database. When you do so, you'll get a screen like the following:
=============================== DVD TO DELETE: =============================== Title: American Gangster Star: Denzel Washington
Costar: Russell Crowe Year released: 2007 Genre: : Romance =============================== Are you sure you want to delete? Enter a choice and press enter (Y/y = yes, Anything else = No)
If you type Y or y and press Enter, the record will be deleted and you'll get the following confirmation prompt:
Item deleted, press enter to continue:
If you type anything else and press Enter, then the record will not be deleted, and you'll get the following prompt:
Item NOT deleted, press enter to continue:
In both cases, after you press Enter you'll be returned to the main menu.
Often, it can be useful to have a list of the items in your database in a comma-separated value (CSV) file. If you type 5 and press Enter to do that, you'll get the following prompt:
=============================== EXPORT DATABASE TO CSV: =============================== Enter base filename (will be given a .csv extension):
Type a base filename (the part before the extension) and press Enter. For example, if I decided to name my csv
file jimsdvds.csv
, I would type jimsdvds
and press Enter. I'd then get the following output:
=============================== EXPORT DATABASE TO CSV: =============================== Enter base filename (will be given a .csv extension): jimsdvds jimsdvds.csv successfully written, press Enter to continue:
At this point, if I go to the directory from which the program was executed, I will see a jimsdvds.csv
file. If I bring that file up in a spreadsheet that can read CSV files, I'll see what's shown in Figure 3-1.
Like the example in the previous chapter, the DVD Inventory program uses a text-based menu system (although it is somewhat more complex). The main program is dvd.py
, which displays the menu and manages program flow. It calls several other modules:
add_dvds
— Adds DVDs to the database
lookup_dvds
— Looks up DVDs in the database based on search criteria
modify_dvd
— Modifies field information for a particular DVD record
delete_dvd
— Deletes DVD records
csvreport_dvd
— Generates a CSV file of all the items in the database
This program differs from the last chapter in one notable way: Instead of a large "helper" module containing all the code branched from the main program, you have created a separate module for each menu option.
This application has a main module, dvd.py
, and multiple modules that are called from the main module. The following sections walk through them one at a time.
In addition to being the main program that users will run, the dvd.py
module also contains the code to display the menu and respond to user selections. Table 3-1 shows the function of the dvd.py
module.
The add_dvd
module adds a DVD record to the database. It takes user input for the DVD information and then executes a SQL INSERT
statement to insert the record in the database. Table 3-2 describes the functions of the add_dvd
module.
The lookup_dvds
module enables the user to look up DVDs in the database by title, star, costar, year, or genre. It also enables finding multiple records if the search returns multiple "hits." Table 3-3 describes the functions of the lookup_dvds
module.
Table 3.3. Table 3-3
Function | Return Type | Description |
---|---|---|
| none | Takes user input on title, star, costar, year, and genre and passes that data to |
| none | Takes title, star, costar, year, and genre information from |
The modify_dvd
module enables the user to modify the fields in a DVD record. It contains just a single function, although the function is quite large. Table 3-4 describes the function of the modify_dvd
module.
Table 3.4. Table 3-4
Function | Return Type | Description |
---|---|---|
| none |
|
The delete_dvd
module enables the user to delete a DVD record from the database. Note that deletions are permanent and cannot be undone. Table 3-5 shows the functions of the delete_dvd
module.
Table 3.5. Table 3-5
Function | Return Type | Description |
---|---|---|
| none | Takes user input for the DVD title to delete. Looks up and displays the record. If the user confirms, it calls |
| none | This function takes the title from |
The csvreport_dvd
module takes a filename as user input (the filename to give the report) and writes the database data to a CSV file. Table 3-6 describes the function of the csvreport_dvd
module.
As you work through the following code, you'll notice how some items are named similarly — this helps to outline or lay out the structure and makes it easier for those who come after you to read your code. Try to do this whenever you can. Of course, it's not always possible (for example, modify_dvd.py
has a single function), so make sure you let common sense prevail. Let's look at some code.
In the interests of page space, I've omitted the code headers, but you should make sure that you use them. Your coworkers will thank you.
Basically, the purpose of the main program is to present the user menu and provide branches to the other modules when an item is chosen. The following code example presents the entire program. As you did in the preceding chapter, look it over, and then we'll break it down piece by piece:
import os import add_dvd import lookup_dvds import modify_dvd import delete_dvd import csvreport_dvd #MAIN MENU def Menu(): os.system('cls') print """ ================================ DVD DATABASE ================================ 1 - Add a DVD to the database 2 - Search inventory 3 - Modify DVD record 4 - Delete DVD record 5 - Export listing to CSV 6 - Exit ================================ """ choice = raw_input("Enter a choice and press enter: ") return choice #TAKE CHOICE AND LAUNCH MODULE choice = "" while choice != "6": choice = Menu() if choice == "1": os.system('cls') add_dvd.AddDVD() elif choice == "2": os.system('cls') lookup_dvds.LookupDVD() elif choice == "3": os.system('cls') modify_dvd.ModifyDVD() elif choice == "4": delete_dvd.DeleteDVD() elif choice == "5": csvreport_dvd.WriteCSV()
The main()
function (again, main()
is implied, even though it's not listed in the program, and anything not in a function or class is part of main()
and automatically runs) operates very similarly to the File/Directory Snapshot program: It initializes a choice
variable and then uses a while
menu, which calls Menu()
and assigns the choice
variable to menu()
's return value. It then presents the menu and performs actions based on the user's selection:
#TAKE CHOICE AND LAUNCH MODULE choice = "" while choice != "6": choice = Menu() if choice == "1": os.system('cls') add_dvd.AddDVD() elif choice == "2": os.system('cls') lookup_dvds.LookupDVD() elif choice == "3": os.system('cls') modify_dvd.ModifyDVD() elif choice == "4": delete_dvd.DeleteDVD() elif choice == "5": csvreport_dvd.WriteCSV()
However, note one difference in this case: The while
structure has almost no actual code of its own — it simply calls other functions. Here, it calls the functions contained in the other modules pertaining to the menu selections of the user.
Again, the while
loop continues to call the menu()
function until the user types 6 and presses Enter, which breaks the loop; and because the while
loop is the last code in the program, it will end the program and return the user to a system prompt.
The Menu()
function displays the user menu and accepts the user's input, assigning it to the variable choice
:
#MAIN MENU def Menu(): os.system('cls') print """ ================================ DVD DATABASE ================================ 1 - Add a DVD to the database 2 - Search inventory 3 - Modify DVD record
4 - Delete DVD record 5 - Export listing to CSV 6 - Exit ================================ """ choice = raw_input("Enter a choice and press enter: ") return choice
The function then returns the value of choice
to the command that called it.
The add_dvd
module adds a DVD record to the database. It has two functions: AddDVD()
, which interacts with the user, and SQLAddDVD()
, which interacts with the database. Here's the entire module for you to look at — a breakdown of each function immediately follows:
import MySQLdb, random, os #RUN THE SQL STATEMENT TO INSERT RECORD INTO DATABASE def SQLAddDVD(Title, Star, Costar, Year, Genre): SQL = 'INSERT INTO DVD values ("%s", "%s", "%s", "%s", "%s")' % (Title, Star, Costar, Year, Genre) try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL) db.commit() c.close() db.close() raw_input("Record added - press enter to continue: ") except: print "THERE WAS A PROBLEM ADDING THE RECORD" raw_input("Press Enter to continue: ") #TAKE USER INPUT AND RUN FUNCTION TO INSERT INTO DATABASE def AddDVD(): print "===============================" print "ADD A DVD TO THE DATABASE:" print "===============================" Title = raw_input("Enter the DVD title: ") Star = raw_input("Enter the name of the movie's star: ") Costar = raw_input("Enter the name of the movie's costar: ") Year = raw_input("Enter the year the movie was released: ") Genre = raw_input("Enter the genre: - 1 for Drama, 2 for horror, 3 for comedy, 4 for romance: ") if Genre == "1": Genre = "Drama" elif Genre == "2": Genre = "Horror"
elif Genre == "3": Genre = "Comedy" elif Genre == "4": Genre = "Romance" else: print "ERROR GETTING INFORMATION!" raw_input("Press Enter to return to the menu: ") return SQLAddDVD(Title, Star, Costar, Year, Genre)
The AddDVD()
function uses a series of raw_input
statements to get all the information needed to add a DVD record to the database:
Title = raw_input("Enter the DVD title: ") Star = raw_input("Enter the name of the movie's star: ") Costar = raw_input("Enter the name of the movie's costar: ") Year = raw_input("Enter the year the movie was released: ")
For genre, because there is a discrete list of items to select from, the user is presented with a list of options and prompted to type a number corresponding to the desired genre. An if
construct then converts the selected number to the appropriate string value:
Genre = raw_input("Enter the genre: - 1 for Drama, 2 for horror, 3 for comedy, 4 for romance: ") if Genre == "1": Genre = "Drama" elif Genre == "2": Genre = "Horror" elif Genre == "3": Genre = "Comedy" elif Genre == "4": Genre = "Romance" else: print "ERROR GETTING INFORMATION!" raw_input("Press Enter to return to the menu: ") return
As you can see, if the user presses something other than 1–4, then he or she will get an error message.
The last line of the function is simply a call to the SQLAddDVD()
function, passing it all the values it needs to add the record to the database (Title, Star, Costar, Year, and Genre):
SQLAddDVD(Title, Star, Costar, Year, Genre)
As you can see from SQLAddDVD()
's parameter list, it takes the data from AddDVD()
that the user typed in. Then, the first thing it does is create a string variable to hold the SQL command that will be run to insert the record in the database, using the values provided:
SQL = 'INSERT INTO DVD values ("%s", "%s", "%s", "%s", "%s")' % (Title, Star, Costar, Year, Genre)
The rest of the function consists of a try
/except
block for inserting the record in the database:
try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL) db.commit() c.close() db.close() raw_input("Record added - press enter to continue: ") except: print "THERE WAS A PROBLEM ADDING THE RECORD" raw_input("Press Enter to continue: ")
It uses the MySQLdb
module's connect()
method to connect to the MySQL database. Then it assigns to variable c
the "cursor" (which is basically the reference point in the database). We then execute the SQL command and commit the changes.
Because connecting to a database and making changes always includes the potential for problems, this code is encapsulated inside a try
/except
block. When problems occur, an appropriate error message is generated. Pressing Enter will return the user to the main menu.
The lookup_dvds.py
module enables users to query the database with a search pattern and view a list of matching records. It allows searches to be executed based on title, star, costar, year of release, or genre. It is comprised of a LookupDVD()
function and a SQLLookupDVD()
function. Here is the code:
import MySQLdb, os #RUN THE SQL STATEMENT TO QUERY THE DATABASE def SQLLookupDVD(searchby, searchtext): SQL = "SELECT * FROM DVD WHERE %s = %s" % (searchby, searchtext) try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor()
c.execute(SQL) output = c.fetchall() c.close() db.close() except: print "THERE WAS A PROBLEM ACCESSING THE DATABASE" raw_input("Press Enter to continue: ") return os.system('cls') print "===============================" print "DVD SEARCH RESULTS:" print "===============================" if output == (): print "NO RECORDS FOUND" print "===============================" for entry in output: print "Title: ", entry[0] print "Star: ", entry[1] print "Costar: ", entry[2] print "Year: ", entry[3] print "Genre: ", entry[4] print "===============================" raw_input(" Press enter to continue: ") #TAKE USER INPUT AND RUN FUNCTION TO QUERY THE DATABASE def LookupDVD(): print """ =============================== DVD LOOKUP: =============================== Enter the criteria to look up by: 1 - Movie title 2 - Star 3 - Costar 4 - Year released 5 - Genre""" choice = raw_input(" Type a number and press enter: ") searchby = "" searchtext = "" if choice == "1": searchby = "DVD_TITLE" searchtext = raw_input("Enter the DVD title to search for: ") searchtext = ""%s"" % (searchtext)
elif choice == "2": searchby = "DVD_STAR_NAME" searchtext = raw_input("Enter the DVD star name to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "3": searchby = "DVD_COSTAR_NAME" searchtext = raw_input("Enter the DVD costar name to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "4": searchby = "DVD_YEAR" searchtext = raw_input("Enter the DVD release year to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "5": searchby = "DVD_GENRE" print """ Enter the genre to search for: 1 - Drama 2 - Horror 3 - Comedy 4 - Romance """ entrychoice = raw_input(" ") if entrychoice == "1": searchtext = ""Drama"" elif entrychoice == "2": searchtext = ""Horror"" elif entrychoice == "3": searchtext = ""Comedy"" elif entrychoice == "4": searchtext = ""Romance"" else: print "ERROR IN CHOICE!" raw_input("Press Enter to return to the menu: ") return SQLLookupDVD(searchby, searchtext)
The LookupDVD()
function starts out by simply asking the user which field to search by and assigning it to the variable choice
:
print """ =============================== DVD LOOKUP: =============================== Enter the criteria to look up by: 1 - Movie title 2 - Star 3 - Costar 4 - Year released 5 - Genre""" choice = raw_input(" Type a number and press enter: ")
Two variables are then initialized as empty strings:
searchby = "" searchtext = ""
The next section of the function is an if
construct that, based on which field the user decided to search on, does two things:
It assigns the variable searchby
to the field the user decided to search on.
It prompts the user for the search text and assigns it to the variable searchtext
:
if choice == "1": searchby = "DVD_TITLE" searchtext = raw_input("Enter the DVD title to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "2": searchby = "DVD_STAR_NAME" searchtext = raw_input("Enter the DVD star name to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "3":
searchby = "DVD_COSTAR_NAME" searchtext = raw_input("Enter the DVD costar name to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "4": searchby = "DVD_YEAR" searchtext = raw_input("Enter the DVD release year to search for: ") searchtext = ""%s"" % (searchtext) elif choice == "5": searchby = "DVD_GENRE" print """ Enter the genre to search for: 1 - Drama 2 - Horror 3 - Comedy 4 - Romance """ entrychoice = raw_input(" ") if entrychoice == "1": searchtext = ""Drama"" elif entrychoice == "2": searchtext = ""Horror"" elif entrychoice == "3": searchtext = ""Comedy"" elif entrychoice == "4": searchtext = ""Romance"" else: print "ERROR IN CHOICE!" raw_input("Press Enter to return to the menu: ") return
As shown in the preceding example, the if
construct enables users to type in a number corresponding to their selected genre if that is what they are searching on. In addition, there is an else
branch in case the user provides an invalid entry.
The last thing the function does is call SQLLookupDVD()
, passing it the field to the search on and the search text:
SQLLookupDVD(searchby, searchtext)
The SQLLookupDVD()
function performs the SQL query to look up any matching records and then outputs the results to the screen. It takes the field to search on and the search text, and creates a string variable with the SQL query:
SQL = "SELECT * FROM DVD WHERE %s = %s" % (searchby, searchtext)
The next code block is a try/except
block that connects to the database and executes the query, assigning the result set to the variable output
:
try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL) output = c.fetchall() c.close() db.close() except: print "THERE WAS A PROBLEM ACCESSING THE DATABASE" raw_input("Press Enter to continue: ") return
With the results assigned to the variable output
, the function then displays the results to the screen:
os.system('cls') print "===============================" print "DVD SEARCH RESULTS:" print "===============================" if not output: print "NO RECORDS FOUND" print "===============================" for entry in output: print "Title: ", entry[0] print "Star: ", entry[1] print "Costar: ", entry[2] print "Year: ", entry[3] print "Genre: ", entry[4] print "===============================" raw_input(" Press enter to continue: ")
Notice that if the result set is empty, then it prints out a message indicating there are "no records found."
The modify_dvd
module is the most complex in the program, which is not unexpected — it is much trickier to edit database records in place than to delete them or add them. Here's the whole module. As before, look it over and then we'll break the method down:
import MySQLdb def ModifyDVD(): print "===============================" print "MODIFY A DVD RECORD:" print "===============================" dvdTitle = raw_input(" Enter the title of the DVD to modify: ") SQL_LOOKUP = "SELECT * FROM DVD WHERE DVD_TITLE = "%s"" % dvdTitle try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_LOOKUP) searchResult = c.fetchall() if searchResult[0] == (): raise except: print "THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE!" raw_input("Press Enter to continue: ") return try: print "===============================" print "DVD TO MODIFY:" print "===============================" print "1 - Title: ", searchResult[0][0] print "2 - Star: ", searchResult[0][1] print "3 - Costar: ", searchResult[0][2] print "4 - Year: ", searchResult[0][3] print "5 - Genre: ", searchResult[0][4] print "===============================" choice = raw_input("Type the number for the field you want to modify and press Enter: ") titleChanged = False modify = "" newvalue = "" if choice == "1": modify = "DVD_TITLE" newvalueTitle = raw_input("Enter the new DVD title name: ") newvalue = ""%s"" % newvalueTitle titleChanged = True elif choice == "2": modify = "DVD_STAR_NAME" newvalue = raw_input("Enter the new DVD star name: ") newvalue = ""%s"" % newvalue elif choice == "3": modify = "DVD_COSTAR_NAME" newvalue = raw_input("Enter the new DVD costar name: ")
newvalue = ""%s"" % newvalue elif choice == "4": modify = "DVD_YEAR" newvalue = raw_input("Enter the new DVD year of release: ") newvalue = ""%s"" % newvalue elif choice == "5": modify = "DVD_GENRE" print "===============================" print "Enter the genre to apply to this DVD:" print "1 - Drama" print "2 - Horror" print "3 - Comedy" print "4 - Romance" entrychoice = raw_input("Type the number for the genre you want to apply and press Enter: ") if entrychoice == "1": newvalue = ""Drama"" elif entrychoice == "2": newvalue = ""Horror"" elif entrychoice == "3": newvalue = ""Comedy"" elif entrychoice == "4": newvalue = ""Romance"" SQL_UPDATE = "UPDATE DVD SET %s = %s WHERE DVD_TITLE = "%s"" % (modify, newvalue, dvdTitle) db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_UPDATE) db.commit() if titleChanged: SQL_LOOKUP = "SELECT * FROM DVD WHERE DVD_TITLE = "%s"" % newvalueTitle c = db.cursor() c.execute(SQL_LOOKUP) modifyResult = c.fetchall() c.close() db.close() except: print "THERE WAS A PROBLEM MODIFYING THE RECORD" raw_input("Press Enter to continue: ") return print "===============================" print "MODIFIED RECORD:" print "===============================" print "1 - Title: ", modifyResult[0][0] print "2 - Star: ", modifyResult[0][1]
print "3 - Costar: ", modifyResult[0][2] print "4 - Year: ", modifyResult[0][3] print "5 - Genre ", modifyResult[0][4] print "===============================" raw_input("Press enter to continue: ")
The function starts out simply enough — it prints the header for the "modify" screen:
print "===============================" print "MODIFY A DVD RECORD:" print "==============================="
Then the user is prompted for the title of the DVD to be modified, and the response is assigned to the variable dvdTitle
:
dvdTitle = raw_input(" Enter the title of the DVD to modify: ")
Users need to be able to see what they are modifying, so the first thing you need to do is look up the record:
SQL_LOOKUP = "SELECT * FROM DVD WHERE DVD_TITLE = "%s"" % dvdTitle try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_LOOKUP) searchResult = c.fetchall() if searchResult[0] == (): raise except: print "THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE!" raw_input("Press Enter to continue: ") return
As before, using a try
/except
block is well-advised when accessing a database, just in case problems occur. As you can see from the preceding code block, if no results are found from the search, then you raise an exception, which displays an error message and returns to the main menu.
Now you are at the bulk of the function — when you actually modify the record. This entire part is encased in a try
/except
block.
First, display a menu asking users which field they want to modify (and assign their answer to the variable choice
):
print "===============================" print "DVD TO MODIFY:" print "===============================" print "1 - Title: ", searchResult[0][0] print "2 - Star: ", searchResult[0][1] print "3 - Costar: ", searchResult[0][2] print "4 - Year: ", searchResult[0][3] print "5 - Genre: ", searchResult[0][4] print "===============================" choice = raw_input("Type the number for the field you want to modify and press Enter: ")
The next part is a branching section with a nested if
statement:
titleChanged = False modify = "" newvalue = "" if choice == "1": modify = "DVD_TITLE" newvalueTitle = raw_input("Enter the new DVD title name: ") newvalue = ""%s"" % newvalueTitle titleChanged = True elif choice == "2": modify = "DVD_STAR_NAME" newvalue = raw_input("Enter the new DVD star name: ") newvalue = ""%s"" % newvalue elif choice == "3": modify = "DVD_COSTAR_NAME" newvalue = raw_input("Enter the new DVD costar name: ") newvalue = ""%s"" % newvalue elif choice == "4": modify = "DVD_YEAR" newvalue = raw_input("Enter the new DVD year of release: ") newvalue = ""%s"" % newvalue elif choice == "5": modify = "DVD_GENRE" print "===============================" print "Enter the genre to apply to this DVD:" print "1 - Drama" print "2 - Horror" print "3 - Comedy" print "4 - Romance" entrychoice = raw_input("Type the number for the genre you want to apply and press Enter: ") if entrychoice == "1": newvalue = ""Drama"" elif entrychoice == "2":
newvalue = ""Horror"" elif entrychoice == "3": newvalue = ""Comedy"" elif entrychoice == "4": newvalue = ""Romance""
Did you notice that there are a few variables assigned at the top of this code snippet? The first one is very important. Because all the fields can be changed when a record is modified, you need to ensure that you can redisplay the record after you're done if the field you used to look it up has changed. To do so, you have to monitor whether the title is being modified, and keep track of the title as it appeared originally. That way, you can be sure to display the same record.
The following code does the actual update of the record:
SQL_UPDATE = "UPDATE DVD SET %s = %s WHERE DVD_TITLE = "%s"" % (modify, newvalue, dvdTitle) db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_UPDATE) db.commit()
At this point, our SQL_LOOKUP
string variable contains the value of the DVD title originally entered. If you didn't modify the title, that's fine. However, if you did modify the title, then you need to change the query. Therefore, you next put in a simple little if
statement:
if titleChanged: SQL_LOOKUP = "SELECT * FROM DVD WHERE DVD_TITLE = "%s"" % newvalueTitle
Next, run a query for the modified record and assign its result set to the variable modifyResult
:
c = db.cursor() c.execute(SQL_LOOKUP) modifyResult = c.fetchall() c.close() db.close()
Note that there is an exception statement, like the others you've looked at, displaying an error message if you have a problem with your database connection.
Finally, display the modified record:
print "===============================" print "MODIFIED RECORD:" print "===============================" print "1 - Title: ", modifyResult[0][0] print "2 - Star: ", modifyResult[0][1] print "3 - Costar: ", modifyResult[0][2] print "4 - Year: ", modifyResult[0][3] print "5 - Genre ", modifyResult[0][4] print "===============================" raw_input("Press enter to continue: ")
The delete_dvd.py
module enables users to delete DVD records from the database. Users will be prompted to ensure that they want to make the deletion, but deletions are permanent. This module includes two functions, DeleteDVD()
and SQLDeleteDVD()
, which are described following the code in their entirety:
import MySQLdb, os #RUN THE SQL STATEMENT TO DELETE THE SELECTED RECORD def SQLDeleteDVD(dvdToDelete): try: SQL_DELETE = "DELETE DVD FROM DVD WHERE DVD_TITLE = "%s"" % dvdToDelete db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_DELETE) db.commit() c.close() db.close() raw_input("Item deleted, press enter to continue: ") except: print "THERE WAS A PROBLEM DELETING THE RECORD" raw_input("Press Enter to continue: ") #TAKE USER INPUT AND RUN FUNCTION TO DELETE THE SELECTED RECORD def DeleteDVD(): os.system('cls') print "===============================" print "DELETE A DVD RECORD:" print "===============================" dvdToDelete = raw_input(" Enter the title of the DVD to delete: ") SQL_DELETE = "DELETE DVD FROM DVD WHERE DVD_TITLE = "%s"" % dvdToDelete
try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_LOOKUP) searchResult = c.fetchall() if searchResult[0] == (): raise except: print "THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE!" raw_input("Press Enter to continue: ") return print "===============================" print "DVD TO DELETE:" print "===============================" print "Title: ", searchResult[0][0] print "Star: ", searchResult[0][1] print "Costar: ", searchResult[0][2] print "Year released: ", searchResult[0][3] print "Genre: :", searchResult[0][4] print "===============================" print ''' Are you sure you want to delete? Enter a choice and press enter (Y/y = yes, Anything else = No) ''' choice = raw_input(" ") if (choice == "Y" or choice == "y"): SQLDeleteDVD(dvdToDelete) else: c.close() db.close() raw_input("Item NOT deleted, press enter to continue: ")
The DeleteDVD()
function prompts users for the title of the DVD they want to delete, shows them the record, makes sure that they want to delete it, and (assuming they confirm), calls the SQLDeleteDVD()
function to make the deletion.
It starts by simply displaying the banner for the menu:
os.system('cls') print "===============================" print "DELETE A DVD RECORD:" print "==============================="
Then, the user is prompted for the title of the DVD to be deleted, and this information is assigned to the variable dvdToDelete
:
dvdToDelete = raw_input(" Enter the title of the DVD to delete: ")
A SQL query is then built (so the record can be looked up) and assigned to a variable:
SQL_LOOKUP = "SELECT * FROM DVD WHERE DVD_TITLE = "%s"" % dvdToDelete
The record is then looked up and the result set assigned to a variable:
try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_LOOKUP) searchResult = c.fetchall() if searchResult[0] == (): raise except: print "THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE!" raw_input("Press Enter to continue: ") return
Again, use try/except
because you are accessing the database.
Now it's time to display the record to the user and confirm whether they really want to delete it:
print "===============================" print "DVD TO DELETE:" print "===============================" print "Title: ", searchResult[0][0] print "Star: ", searchResult[0][1] print "Costar: ", searchResult[0][2] print "Year released: ", searchResult[0][3] print "Genre: :", searchResult[0][4] print "===============================" print ''' Are you sure you want to delete? Enter a choice and press enter (Y/y = yes, Anything else = No) ''' choice = raw_input(" ")
At this point, the function does its work based on what the user input:
if (choice == "Y" or choice == "y"): SQLDeleteDVD(dvdToDelete) else: c.close() db.close() raw_input("Item NOT deleted, press enter to continue: ")
If the user selects Y or y and presses Enter, then the SQLDeleteDVD()
function is called, passing the title of the DVD to delete. If the user types anything else, then an error message is displayed and the user is returned to the main menu.
This function takes the title of the DVD to delete from DeleteDVD()
and executes the deletion. Because the function is fairly short and straightforward, it is shown in its entirety here:
def SQLDeleteDVD(dvdToDelete): try: SQL_DELETE = "DELETE DVD FROM DVD WHERE DVD_TITLE = "%s"" % dvdToDelete db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL_DELETE) db.commit() c.close() db.close() raw_input("Item deleted, press enter to continue: ") except: print "THERE WAS A PROBLEM DELETING THE RECORD" raw_input("Press Enter to continue: ")
Basically, it takes the title through a parameter, builds a SQL statement, connects to the database, runs the statement, and closes the connection. Because it's in a try/except
block, if problems are encountered when connecting to the database, then a friendly error message is displayed.
The csvreport_dvd.py
module enables users to export the complete list of DVDs to a CSV file, which can then be retrieved and used in a spreadsheet program. It consists of a single function, WriteCSV()
. Here is the code:
import MySQLdb, csv, os #FUNCTION TO WRITE ENTIRE DVD LIST TO CSV def WriteCSV(): SQL = "SELECT * FROM DVD" try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL) output = c.fetchall() c.close() db.close() except: print "THERE WAS A PROBLEM ACCESSING THE DATABASE!" raw_input("Press Enter to return to the menu: ") return
try: os.system('cls') print "===============================" print "EXPORT DATABASE TO CSV:" print "===============================" filename = raw_input("Enter base filename (will be given a .csv extension): ") filename = filename + ".csv" writer = csv.writer(open(filename, "wb")) writer.writerow(("TITLE", "STAR NAME", "COSTAR NAME", "YEAR", "GENRE")) writer.writerows(output) print filename, " successfully written, press Enter to continue: " raw_input("") return except: print "ERROR WRITING FILE!" raw_input("Press Enter to return to the menu: ")
The program begins by assigning to a string variable a SQL statement to query all records in the database:
SQL = "SELECT * FROM DVD"
Following this is a try/except
block to query for all the records in the database, assigning the result set to variable output:
try: db = MySQLdb.connect("localhost", "root", "zanzibar", "DVDCOLLECTION") c = db.cursor() c.execute(SQL) output = c.fetchall() c.close() db.close() except: print "THERE WAS A PROBLEM ACCESSING THE DATABASE!" raw_input("Press Enter to return to the menu: ") return
Finally, a try/except
block does the following:
Prompts the user for the filename to use
Uses Python's csv
module (which we imported) to take the tuple of values (assigned to identifier output) and export them to a csv
file with the filename the user input
Provides the user with success status when the file has been written
try: os.system('cls') print "===============================" print "EXPORT DATABASE TO CSV:" print "===============================" filename = raw_input("Enter base filename (will be given a .csv extension): ") filename = filename + ".csv" writer = csv.writer(open(filename, "wb")) writer.writerow(("TITLE", "STAR NAME", "COSTAR NAME", "YEAR", "GENRE")) writer.writerows(output) print filename, " successfully written, press Enter to continue: " raw_input("") return except: print "ERROR WRITING FILE!" raw_input("Press Enter to return to the menu: ")
Database programs can be tricky to test. As you test them, you'll want to think about implementing the following tactics:
Test each field individually with several different data sets — use valid data, invalid data, and valid data right at the "boundary" (e.g., if a field can hold integers up to 10,000, then enter 10,000 and 10,001).
Test with "user scenarios," — that is, consider what some typical records or data sets would look like and use those.
Learn how to create export files with your database of choice so you can quickly import them to seed your database. This will save a great deal of time.
There are several ways this project could be enhanced, including the following:
You could implement a web interface for the product — in chapter 8, you'll look at Plone, a Python-based web framework designed for just this kind of project.
You could expand on the reporting capabilities — for example, you could enable users to create specific queries for building CSV files.
You could include a field for "rating" and then export it to a CSV file and produce a graph in Excel showing comparisons of different groups of DVDs.
In this chapter, you learned how to write an application to access and integrate with a database. You learned how to search for information, add records, modify records, and delete records — all from Python. This knowledge serves as a foundation that can help you to build countless applications.
To build truly useful programs with Python, you'll also have to know how to combine the power of a database with the accessibility of computer networks, which is exactly what you'll learn next.
3.141.7.240