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:

SQL Schema »

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;">&nbsp;</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;">&nbsp;</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