MovieDB
Like the webmail client, this movie database front-end was for csci4131 – Internet Programming, and is written in python/cgi. The most notable difference between this and the webmail client, though, is that this program utilizes a database (as the name implies). Specifically, a MySQL database with a few different tables and various keys are used to store various aspects of the information about the movie. The schema was actually provided for us already, as defined here:
Anyway, here’s the code I wrote. Enjoy.
#!/soft/python-2.4-bin/python import cgitb cgitb.enable() import cgi, md5, MySQLdb, time ######## support functions def printHeaders(title, scriptname): # print "Content-Type: application/xhtml+xml" print "Content-Type: text/html" print """ <?xml version="1.0" encoding="iso-8859-1"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>%s</title> <meta http-equiv="Content-Type" content="application/xhtml+xml; charset=iso-8859-1" /> <style type="text/css"> body { font-family: Arial, Helvetica, Sans-Serif; font-size: 13px; } h1 { text-align: center; } h2 { color: #990000; } </style> <script type=\\"text/javascript\\"> <!--""" % (title) if scriptname == "loginScript": print loginScript elif scriptname == "validateSearch": print validateSearch elif scriptname == "validateAdd": print validateAdd elif scriptname == "updateForm": # print "var table = [" # for record in table: # print "['%s %s. %s', '%s', '%s', '%s, %s', '%s']," % (record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8]) # print "]" print updateForm print """ --> </script> </head> <body> <h1>Csci4131 Lab06 - David R. Hedges, #2836226</h1>""" def printFooters(): print """ </body> </html>""" def printBadSession(): printHeaders("Invalid Session", "") print "<h2>Invalid Session</h2>\\n<div>Either your session has expired (5 min) or your login creditials are incorrect. You may click <a href=\\"./moviedb.cgi\\" title=\\"Login\\">here</a> to log in again.</div>" ################3 ## connectdb() def connect(): dbconn = MySQLdb.connect(host='db.itlabs.umn.edu', db='S1G20', user='S1G20', passwd='30612') # dbconn = MySQLdb.connect(host='localhost', db='test', user='S1G20', passwd='30612') return dbconn ############# ## validateSession(checksum) def validateSession(checksumstring): checksum = checksumstring[0:32] inittime = checksumstring[32:len(checksumstring)] sessionvalid = 0 for key in pwd: if checksum == str(md5.new(key + pwd[key]).hexdigest()): sessionvalid = 1 break if sessionvalid: if time.time() - eval(inittime) > 300: sessionvalid = 0 if sessionvalid: return checksum + str(time.time()) else: return 0 ## page 1 ## this function prints the content of the first page (login). the one variable ## 'error' is used to set the display: property of the error message for the ## login page. acceptable values are "inline" (if the error message should be ## displayed), or "none" (if the error message should not be diaplayed). ################ def printPg1(error): print"""<form action="moviedb.cgi" method="post" onsubmit="if (!validateInput()) {return false;}"> <h2>Login <span style="display: %s;">(Error: Invalid username/password)</span></h2> <div style="width: 200px; text-align: right; line-height: 24px;"> Username: <input type="text" name="username" size="12" /><br /> Password: <input type="password" name="password" size="12" /><br /> <input type="hidden" name="postPageNumber" value="1" /> <input type="submit" name="submit" value="Login" /> <input type="reset" name="Reset" value="Clear" /> </div> </form>""" % (error) ## page 2 ################ def printPg2(checksum): print"""<form action="moviedb.cgi" method="post"> <h2>Movie Database: Select an Action</h2> <div> <input type="radio" name="action" value="search" checked="checked" /> Search for Movie<br /> <input type="radio" name="action" value="add" /> Add Movie<br /> <input type="hidden" name="postPageNumber" value="2" /> <input type="hidden" name="checksum" value="%s" /> <input type="submit" name="submit" value="Go!" /> <input type="submit" name="logout" value="Logout" /> </div> </form>""" % (checksum) ############ ## search page def printSearchPg(checksum): print """<form action="moviedb.cgi" method="post" onsubmit="if(validateSearch()) { return true; } else { return false; }"> <h2>Movie Database: Search for Movies</h2> <div> <div style="float: left; text-align: right; width: 180px; line-height: 26px;"> Search by Title: <input type="radio" name="searchType" value="title" checked="checked" /><br /> Search by Director: <input type="radio" name="searchType" value="director" /><br /> Search by Actor: <input type="radio" name="searchType" value="actor" /> </div> <div style="float: left; text-align: right; width: 200px; line-height: 26px;"> Title: <input type="text" name="title" size="16" /><br /> Director: <input type="text" name="director" size="16" /><br /> Actor: <input type="text" name="actor" size="16" /> </div> <input type="hidden" name="postPageNumber" value="3" /> <input type="hidden" name="checksum" value="%s" /> <div style="margin-left: 180px; clear: both;"> <input type="submit" name="submit" value="Submit" /> <input type="reset" value="reset" name="Reset" /> </div> </div> </form>""" % (checksum) ############ ## add page def printAddPg(checksum): print """<form action="moviedb.cgi" method="post" onsubmit="if(validateAdd()) { return true; } else { return false; }"> <h2>Add a Movie</h2> <div> <div style="width: 70px; float: left; text-align: right; line-height: 26px;"> Title:<br /> Rating/5*:<br /> Director:<br /> Actors:<br /> </div> <div style="width: 200px; float: left; text-align: left; line-height: 26px; margin-left: 4px;"> <input type="text" name="title" size="26" /><br /> <input type="text" name="rating" size="1" maxlength="1" /><br /> <input type="text" name="director" size="18" /><br /> <textarea name="actors" rows="7" cols="35" title="Format: Firsname Lastname, Firstname2 Lastname2, Firstname3 Lastname3" onfocus="this.value='';">Firsname Lastname, Firstname2 Lastname2, Firstname3 Lastname3</textarea><br /> <input type="hidden" name="postPageNumber" value="4" /> <input type="hidden" name="checksum" value="%s" /> <input type="submit" name="submit" value="Submit" /> <input type="reset" value="reset" name="Reset" /> </div> <div style="clear: both;"> </div> </div> </form>""" % (checksum) ######## end support functions ##################### ################## ## scripts global loginScript loginScript = " function validateInput() {\\n if (document.forms[0].username.value == '') {\\n alert(\\"Please enter a username.\\");\\n return false;\\n }\\n if (document.forms[0].password.value == '') {\\n alert(\\"Please enter a password.\\");\\n return false;\\n }\\n return true;\\n}" global updateForm updateForm = """ function updateForm() { i = document.forms[0].Selection.selectedIndex-1; if (i<0) { document.forms[0].movieID.value = "-1"; document.forms[0].title.value = ""; document.forms[0].rating.value = ""; document.forms[0].director.value = ""; document.forms[0].actors.value = ""; } else { document.forms[0].movieID.value = table[i][0]; document.forms[0].title.value = table[i][1]; document.forms[0].rating.value = table[i][2]; document.forms[0].director.value = table[i][3]; document.forms[0].actors.value = table[i][4]; } }""" global validateSearch validateSearch = """ function validateSearch() { if (document.forms[0].searchType[0].checked) { if (document.forms[0].title.value == '') { alert('Please enter a title'); return false; } } else if (document.forms[0].searchType[1].checked) { if (document.forms[0].director.value == '') { alert('Please enter a director name'); return false; } else if (document.forms[0].director.value.split(/\\s+/).length != 2) { alert('Please enter the director\\\\'s name in the format: firstname lastname'); return false; } } else if (document.forms[0].searchType[2].checked) { if (document.forms[0].actor.value == '') { alert('Please enter an actor name'); return false; } else if (document.forms[0].actor.value.split(/\\s+/).length != 2) { alert('Please enter the actor\\\\'s name in the format: firstname lastname'); return false; } } return true; }""" global validateAdd validateAdd = """ function validateAdd() { if (document.forms[0].title.value == '') { alert('Please enter a title'); return false; } else if (document.forms[0].rating.value == '') { alert('Please enter a rating'); return false; } else if (document.forms[0].rating.value < '0' || document.forms[0].rating.value > '5') { alert('Please enter a rating between 0 and 5'); return false; } else if (document.forms[0].director.value == '') { alert('Please enter a director'); return false; } else if (document.forms[0].director.value.split(/\\s+/).length != 2) { alert('Please enter the director\\\\'s name in the format: firstname lastname'); return false; } else if (document.forms[0].actors.value == '') { alert('Please enter [an] actor[s]'); return false; } else if (document.forms[0].actors.value == 'Firsname Lastname, Firstname2 Lastname2, Firstname3 Lastname3') { alert('Please enter at least one actor. Do not use the formatting example.'); return false; } return true; }""" ##################### ## end scripts # user/password table pwd = { 'user': 'password', 'carl': 'blivit', 'ta': 'ta', 'frog': 'h49xah3YT', } form = cgi.FieldStorage() postPageNumber = form.getvalue("postPageNumber") if postPageNumber == None: postPageNumber = "0" if postPageNumber == "0": printHeaders("Login", "loginScript") printPg1("none") printFooters() elif postPageNumber == "1": try: if pwd[form.getvalue("username")] == form.getvalue("password"): checksum = str(md5.new(form.getvalue("username") + form.getvalue("password")).hexdigest()) + str(time.time()) printHeaders("Movie Database: Select an Action", "") printPg2(checksum) else: printHeaders("Login Error", "loginScript") printPg1("inline") except KeyError: printHeaders("Login Error", "loginScript") printPg1("inline") printFooters() elif postPageNumber == "2": checksum = validateSession(form.getvalue("checksum")) if checksum: #check if user clicked logout if form.getvalue("logout") == "Logout": printHeaders("Logout successful", "") print "Logout Successful<br />" printPg1("none") #check if we're searching for movie or adding a movie elif form.getvalue("action") == "search": printHeaders("Search", "validateSearch") printSearchPg(checksum) elif form.getvalue("action") == "add": printHeaders("Add", "validateAdd") printAddPg(checksum) else: printHeaders("Unknown request") print "The combination of values submitted from the search/add choice page was not understood. The values submitted were:<br />" for val in form: print "%s = %s<br />" % (val, form.getvalue(val)) else: printBadSession() printFooters() elif postPageNumber == "3": checksum = validateSession(form.getvalue("checksum")) if checksum: try: dbconn = connect() connected = 1 except MySQLdb.Error, e: printHeaders("Database Connection Error", "") print "Cannot connect to database: %d: %s" % (e.args[0], e.args[1]) connected = 0 if connected: cursor = dbconn.cursor() if form.getvalue("searchType") == "title": query = "SELECT MovieID, Title, Rating, DirectorID FROM Movie WHERE Title LIKE '%s' OR instr(Title, '%s') ORDER BY Title;" % (form.getvalue("title"), form.getvalue("title")) cursor.execute(query) movies = cursor.fetchall() elif form.getvalue("searchType") == "director": dirname = form.getvalue("director").split() query = "SELECT DirectorID, FName, LName FROM Director WHERE (FName LIKE '%s' AND LName LIKE '%s') OR (instr(FName, '%s') AND instr(LName, '%s')) ORDER BY LName;" % (dirname[0], dirname[1], dirname[0], dirname[1]) cursor.execute(query) directors = cursor.fetchall() if len(directors) > 0: movies = [] for result in directors: query = "SELECT * FROM Movie WHERE DirectorID = %s ORDER BY Title" % result[0] cursor.execute(query) temp = cursor.fetchall() for blah in temp: movies.append(blah) elif form.getvalue("searchType") == "actor": actname = form.getvalue("actor").split() query = "SELECT * FROM Actor WHERE (FName LIKE '%s' AND LName LIKE '%s') OR (instr(FName, '%s') AND instr(LName, '%s')) ORDER BY LName;" % (actname[0], actname[1], actname[0], actname[1]) cursor.execute(query) actors = cursor.fetchall() if len(actors) > 0: movies = [] for actor in actors: query = "SELECT MovieID FROM ActorSet WHERE ActorID = %s" % actor[0] cursor.execute(query) temp = cursor.fetchall() for actorset in temp: query = "SELECT * FROM Movie WHERE MovieID = %s ORDER BY Title" % actorset[0] cursor.execute(query) temp2 = cursor.fetchall() for blah in temp2: movies.append(blah) directorD = {} actorD = {} actorS = {} #populate dictionaries to be used in easy table population later for entry in movies: if not directorD.has_key(entry[3]): query = "SELECT FName, LName from Director where DirectorID = %s;" % entry[3] cursor.execute(query) directorD[entry[3]] = cursor.fetchone() query = "SELECT ActorID FROM ActorSet WHERE MovieID = %d" % entry[0] cursor.execute(query) actorS[entry[0]] = cursor.fetchall() for actor in actorS[entry[0]]: if not actorD.has_key(actor): query = "SELECT FName,LName FROM Actor WHERE ActorID = %d" % actor cursor.execute(query) actorD[actor] = cursor.fetchone() printHeaders("Query Results", "updateForm") # printHeaders("Query Results", "") print """<form action="moviedb.cgi" method="post"> <h2>Query Results: Examine/Edit a Record by Making a Selection</h2>""" print '<script type="text/javascript">' print '<!--' print 'var table = [' for entry in movies: print "['%d', '%s', '%d', '%s %s', [" % (entry[0], entry[1], entry[2], directorD[entry[3]][0], directorD[entry[3]][1]) for actor in actorS[entry[0]]: print "' %s %s', " % (actorD[actor][0], actorD[actor][1]) print "]]," print ']' print '-->' print '</script>' print """<div style="width: 700px; border: 1px dashed black;"> <div style="float: left;"> <select name="Selection" size="9" onchange="updateForm()"> <option value="-1" selected="selected">NONE</option>""" value = 0 for entry in movies: print '<option value="%d">%s</option>' % (value, entry[1]) value += 1 print """ </select> </div> <div style="width: 70px; float: left; text-align: right; line-height: 26px;"> Title:<br /> Rating/5*:<br /> Director:<br /> Actors: </div> <div style="width: 320px; float: left; text-align: left; line-height: 26px; margin-left: 4px;"> <input type="text" name="title" size="26" /><br /> <input type="text" name="rating" size="1" maxlength="1" /><br /> <input type="text" name="director" size="18" /><br /> <textarea name="actors" rows="7" cols="35" title="Format: Firsname Lastname, Firstname2 Lastname2, Firstname3 Lastname3"></textarea><br /> <input type="hidden" name="movieID" value="-1" /> <input type="hidden" name="postPageNumber" value="5" /> <input type="hidden" name="checksum" value="%s" /> <input type="submit" name="submit" value="Submit Edits" /> <input type="reset" name="reset" value="Reset" /> <input type="submit" name="none" value="No Changes" /> </div> <div style="clear: both;"> </div> </div> </form>""" % checksum cursor.close() dbconn.close() else: #not connected print "" else: printBadSession() printFooters() elif postPageNumber == "4": checksum = validateSession(form.getvalue("checksum")) if checksum: try: dbconn = connect() connected = 1 except MySQLdb.Error, e: printHeaders("Database Connection Error", "") print "Cannot connect to database: %d: %s" % (e.args[0], e.args[1]) connected = 0 if connected: cursor = dbconn.cursor() #check if title already exists: query = "SELECT * FROM Movie WHERE Title LIKE '%s';" % (form.getvalue("title")) cursor.execute(query) matchingMovies = cursor.fetchall() if len(matchingMovies) > 0: printHeaders("Movie Already Exists", "") print "<h2>Movie Title Exists in Database</h2>\\n<div>The movie title '%s' you attempted to add already has a matching result in the database: '%s'</div>" % (form.getvalue("title"), matchingMovies[0][1]) else: #movie doesn't exist in the database yet, so we can continue on adding it #next, check if the director already exists: dirname = form.getvalue("director").split() query = "SELECT * FROM Director WHERE FName LIKE '%s' AND LName LIKE '%s';" % (dirname[0], dirname[1]) cursor.execute(query) matchingDirector = cursor.fetchall() if len(matchingDirector) > 0: #director exists. save his directorID directorID = matchingDirector[0][0] else: #director doesn't exist. add him/her, then save the directorID query = "INSERT INTO Director (FName, LName) VALUES ('%s', '%s');" % (dirname[0], dirname[1]) cursor.execute(query) cursor.execute("SELECT LAST_INSERT_ID();") directorID = cursor.fetchone()[0] #director found or added, now insert movie, and get its movieID query = "INSERT INTO Movie (Title, Rating, DirectorID) VALUES ('%s', %s, %d);" % (form.getvalue("title"), form.getvalue("rating"), directorID) cursor.execute(query) cursor.execute("SELECT LAST_INSERT_ID();") movieID = cursor.fetchone()[0] #movie added. now we have to handle the crappy actors and actorset lameness #split on comma space actorList = form.getvalue("actors").split(", ") for actor in actorList: name = actor.split() if len(name) < 2: name.append(name[0]) # turns Madonna into Madonna Madonna ... :shrug:' query = "SELECT ActorID FROM Actor where FName LIKE '%s' AND LName LIKE '%s';" % (name[0], name[1]) cursor.execute(query) actorID = cursor.fetchone() if actorID != None: actorID = actorID[0] else: #actor not found, so insert him/her query = "INSERT INTO Actor (FName, LName) VALUES ('%s', '%s')" % (name[0], name[1]) cursor.execute(query) cursor.execute("SELECT LAST_INSERT_ID();") actorID = cursor.fetchone()[0] #we know the actor's ID, so insert him/her into actorSet query = "INSERT INTO ActorSet (MovieID, ActorID) VALUES (%s, %s);" % (movieID, actorID) cursor.execute(query) printHeaders("Movie added successfully", "") print "<h2>Movie added successfully</h2>" printPg2(checksum) cursor.close() dbconn.close() else: #not connected print "" else: printBadSession() printFooters() elif postPageNumber == "5": checksum = validateSession(form.getvalue("checksum")) if checksum: #check if user is clicking 'no changes' if form.getvalue("none") == "No Changes": printHeaders("Movie Database: Select an Action", "") printPg2(checksum) else: movieID = form.getvalue("movieID") if movieID > 0: try: dbconn = connect() connected = 1 except MySQLdb.Error, e: printHeaders("Database Connection Error", "") print "Cannot connect to database: %d: %s" % (e.args[0], e.args[1]) connected = 0 if connected: cursor = dbconn.cursor() query = "UPDATE Movie SET Title = '%s' where MovieID = %s;" % (form.getvalue("title"), movieID) cursor.execute(query) query = "UPDATE Movie SET Rating = %s where MovieID = %s;" % (form.getvalue("rating"), movieID) cursor.execute(query) dirname = form.getvalue("director").split() query = "SELECT * FROM Director WHERE FName LIKE '%s' AND LName LIKE '%s';" % (dirname[0], dirname[1]) cursor.execute(query) matchingDirector = cursor.fetchall() if len(matchingDirector) > 0: #director exists. save his directorID directorID = matchingDirector[0][0] else: #director doesn't exist. add him/her, then save the directorID query = "INSERT INTO Director (FName, LName) VALUES ('%s', '%s');" % (dirname[0], dirname[1]) cursor.execute(query) cursor.execute("SELECT LAST_INSERT_ID();") directorID = cursor.fetchone()[0] query = "UPDATE Movie SET DirectorID = %s where MovieID = %s;" % (directorID, movieID) cursor.execute(query) query = "DELETE from ActorSet where MovieID = %s;" % movieID cursor.execute(query) actorList = form.getvalue("actors").split(", ") for actor in actorList: name = actor.split() if len(name) < 2: name.append(name[0]) # turns Madonna into Madonna Madonna ... :shrug:' query = "SELECT ActorID FROM Actor where FName LIKE '%s' AND LName LIKE '%s';" % (name[0], name[1]) cursor.execute(query) actorID = cursor.fetchone() if actorID != None: actorID = actorID[0] else: #actor not found, so insert him/her query = "INSERT INTO Actor (FName, LName) VALUES ('%s', '%s')" % (name[0], name[1]) cursor.execute(query) cursor.execute("SELECT LAST_INSERT_ID();") actorID = cursor.fetchone()[0] #we know the actor's ID, so insert him/her into actorSet query = "INSERT INTO ActorSet (MovieID, ActorID) VALUES (%s, %s);" % (movieID, actorID) cursor.execute(query) cursor.close() dbconn.close() printHeaders("Changes Applied Successfully", "") print "<h2>Changes Applied Successfully</h2>" printPg2(checksum) else: #not connected print "" else: #movie id=-1 printHeaders("No Movie Selected", "") print "<h2>No Movie Selected</h2><div>Please select a movie before attempting to apply changes.</div>" else: printBadSession() printFooters() else: print "Content-Type: text/plain\\n" print "Unknown Page Number: %s" % (postPageNumber)
Download this code: moviedb.cgi