Study Guide 02

Overview

The second Debriefing will cover the following topics:

Server-side Programming
- Tornado library
- Handlers
- Templates
Databases
- SQL Statements (CREATE, INSERT, SELECT)
- sqlite3 module:
  - with statement
  - cursors
  - execute

The Debriefing will consist of definitions, short answers, debugging, and programming questions related to the Project 02.

For the programming section, you will be able to use your computer (but only after you have completed all the other sections).

Below are examples of the type of questions that may be asked on the Debriefing. It is mean to be representative, rather than exhaustive (ie. there may be questions that show up on that are not show below).

Definitions

Briefly define the following terms:

URL
HTTP
Client
Server
Database
Table
Column
Row
SQL
Join

Short Answers

What are the components of a URL? Given the URL, http://127.0.0.1:9123/files?name=echosmith.mp3, identify each component.
Explain what happens when a user types http://rpiXX:9876/ into the web browser. That is, describe what information is passed between the local client and the remote server and what happens on each side of the HTTP transaction.
CRUD is a common pattern for many data-oriented applications. Explain what each letter of CRUD stands for and how each operation maps to a particular SQL statement.
In a Tornado web application how are values from a HTML form accessed by the Python code? Conversely, how are values passed from Python to the HTML template?

Debugging

Database

Suppose we added an insert_artist method to our Database class in Project 02 that inserts the given artist id, name, and image into the Artists table:

def insert_artist(self, id, name, image):
    sql = 'INSERT INTO Artists (ArtistId, Name, Image) VALUES ({}, {}, {})'.format(id, name, image)
    self.conn.execute(sql, id, name, image)

Identify any possible errors and fix the code.

Suppose we added an artists_names method to our Database class in Project 02 that returns a list of only the artist Names in sorted order:
```
def artists_names(self):
    sql = 'SELECT * FROM Artists'
    self.conn.execute(sql)
```
Identify any possible errors and fix the code.

Suppose we added an album_artist method to our Database class in Project 02 that returns the Name of the artist associated with the album_id.

def album_artist(self, album_id):
    sql = '''
    SELECT  Artists.Name
    FROM    Albums
    JOIN    Artists
    WHERE   AlbumId = ?
    '''
    self.conn.execute(artist_sql, album_id)

Identify any possible errors and fix the code.

Suppose we added an search_albums_by_artist method to our Database class in Project 02 that returns a list of album Names whose artist Names are similar to the artist query.

def search_albums_by_artist(self, artist:
    sql = '''
    SELECT  Name
    FROM    Albums
    JOIN    Artists
    WHERE   NAME LIKE %?%
    '''
    self.conn.execute(artist_sql, artist)

Identify any possible errors and fix the code.

Tornado

Suppose we wanted to create an HelloHandler that reads data from the following HTML form:
```
<form>
  <input type="text" name="name" />
  <input type="submit" />
</form>
```
The HelloHandler should read in the value from the form and then render the following hello.html template:
```
Hello, {{ name }}
```
Given the following implementation, identify any possible errors and fix the code.
```
class HelloHandler(object):
    def get(self, name):
        self.write('hello.html', name)
```

Suppose we wanted to create an ListHandler that reads data from the following HTML form:

<form>
  <input type="text" name="data" />
  <input type="submit" />
</form>

The ListHandler should read in the value from the form, split the data by commas into a list, and then render the following HTML:

<ol>
  <li>item 0</li>
  <li>item 1</li>
  <li>item 2</li>
  ...
</ol>

Given the following implementation list.html template and ListHandler class, identify any possible errors and fix the code.

<!--// list.html //-->
<ol>
{ for item in data: }
    <li>data[item]</li>
</ol>

# ListHandler
class ListHandler(object):
    def get(self):
        data = self.get('text').split()
        self.render('list.html', data)

Programming

Given the following list of Teenage Mutant Ninja Turtles in the file tmnt.csv:

Leonardo,blue,katana
Donatello,purple,bo
Raphael,red,sai
Michelangelo,orange,nunchucks

You are to create a database and a web application that let's you search the Teenage Mutant Ninja Turtles and present information about them.

Database

Here is a skeleton of the Database class you need to create:

class Database(object):
    DB_PATH  = 'tmnt.db'
    CSV_PATH = 'tmnt.csv'

    def __init__(self, db_path=dB_PATH, csv_path=CSV_PATH):
        # TODO: Initialize instance variables, create table, and load table

    def search(self, field, query):
        # TODO: Search the database to see if query is similar to values in the field column

Tornado

Here is a skeleton of the tmnt.html template you need to create:

<form>
  <input type="text">
  <select>
    <option>Name</option>
    <option>Color</option>
    <option>Weapon</option>
  </select>
  <input type="submit">
</form>

<!--// Search Results Go here //-->
<ol>
  <li>...</li>
</ol>

Here is a skeleton of the tmnt.py Python script you need to create:

# Handlers

class IndexHandler(tornado.web.RequestHandler):
    def get(self):
        # TODO: Return search results

# Application

class Application(tornado.web.Application):
    def __init__(self, port=PORT):
        tornado.web.Application.__init__(self, debug=True)
        self.logger   = logging.getLogger()
        self.ioloop   = tornado.ioloop.IOLoop.instance()
        # TODO: Define database and port instance variables

        # TODO: Add IndexHandler

    def run(self):
        try:
            self.listen(self.port)
        except socket.error as e:
            self.logger.fatal('Unable to listen on {} = {}'.format(self.port, e))
            sys.exit(1)

        self.ioloop.start()

# Main Execution

if __name__ == '__main__':
    tornado.options.parse_command_line()

application = Application()
application.run()