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 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 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 the program is unable to find the record you want to delete, then you will get the following error:

THERE WAS A PROBLEM ACCESSING THE RECORD IN THE DATABASE!
Press Enter to continue:

Pressing Enter will take you back to the menu.

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.

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.

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.

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.

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 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 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:

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 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 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 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:

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: ")