Week 9

Last Week

  • Last week we looked back to Scratch to remind us of basic programming constructs, to help us transition from C to Python.

  • We saw a new way to start a simple program:

    def main():
        print("hello, world")
    
    if __name__ == "__main__":
        main()
    • and write more complex programs from there.

  • We also looked at the design pattern of MVC, used for creating the architecture of web applications.

  • Today we’ll look further at the M of MVC, Model, to save and retrieve data.

  • We’ll continue to use Flask, a popular web application framework for Python, as a starting place for our examples as we did last week. But web applications can be written with any number of other frameworks, or even built-in Python functions.

  • An application using Flask can be as simple as:

    from flask import Flask, render_template
    
    app = Flask(__name__)
    
    @app.route("/")
    def index():
        return render_template("index.html")
    • This program just returns the template index.html when the user visits /, or the default webpage, of the server.

Frosh IMs

  • Let’s look at froshims0, which has a templates directory with various files ending in .html, as well as application.py:

    from flask import Flask, render_template, request
    
    app = Flask(__name__)
    
    @app.route("/")
    def index():
        return render_template("index.html")
    
    @app.route("/register", methods=["POST"])
    def register():
        if request.form["name"] == "" or request.form["dorm"] == "":
            return render_template("failure.html")
        return render_template("success.html")
    • We added a register function that will respond differently based on the user’s input.

    • In this case, we will check whether the HTTP request, submitted with a POST method, has a form with a name and dorm field, and if either are blank, return the template failure.html, otherwise success.html.

  • failure.html just had this:

    {% extends "layout.html" %}
    
    {% block title %}
    failure
    {% endblock %}
    
    {% block body %}
    You must provide your name and dorm!
    {% endblock %}
  • And success.html is similar:

    {% extends "layout.html" %}
    
    {% block title %}
    success
    {% endblock %}
    
    {% block body %}
    You are registered! (Well, not really.)
    {% endblock %}
  • index.html, meanwhile, had the actual form:

    {% extends "layout.html" %}
    
    {% block title %}
    Frosh IMs
    {% endblock %}
    
    {% block body %}
    <h1>Register for Frosh IMs</h1>
    <form action="{{ url_for('register') }}" method="post">
        Name: <input name="name" type="text"/>
        <br/>
        Dorm:
        <select name="dorm">
            <option value=""></option>
            <option value="Apley Court">Apley Court</option>
            <option value="Canaday">Canaday</option>
            <option value="Grays">Grays</option>
            <option value="Greenough">Greenough</option>
            <option value="Hollis">Hollis</option>
            <option value="Holworthy">Holworthy</option>
            <option value="Hurlbut">Hurlbut</option>
            <option value="Lionel">Lionel</option>
            <option value="Matthews">Matthews</option>
            <option value="Mower">Mower</option>
            <option value="Pennypacker">Pennypacker</option>
            <option value="Stoughton">Stoughton</option>
            <option value="Straus">Straus</option>
            <option value="Thayer">Thayer</option>
            <option value="Weld">Weld</option>
            <option value="Wigglesworth">Wigglesworth</option>
        </select>
        <br/>
        <input type="submit" value="Register"/>
    </form>
    {% endblock %}
    • But notice that this page, too, uses the shared layout.html but with its own title block and body block.

  • To get the final web page, those blocks are then substituted into layout.html:

    <!DOCTYPE html>
    
    <html>
        <head>
            <meta content="initial-scale=1, width=device-width" name="viewport"/>
            <title>{% block title %}{% endblock %}</title>
        </head>
        <body>
            {% block body %}{% endblock %}
        </body>
    </html>
    • Since every page in our application share common pieces in their HTML, we can place those common pieces here but customize certain areas for each page.

  • To actually store our data, let’s look at application.py in froshims1:

    from flask import Flask, render_template, request
    import csv
    
    app = Flask(__name__)
    
    @app.route("/")
    def index():
        return render_template("index.html")
    
    @app.route("/register", methods=["POST"])
    def register():
        if request.form["name"] == "" or request.form["dorm"] == "":
            return render_template("failure.html")
        file = open("registrants.csv", "a")
        writer = csv.writer(file)
        writer.writerow((request.form["name"], request.form["dorm"]))
        file.close()
        return render_template("success.html")
    • Notice that in the final lines of register(), we write the information we get from the request into a file called registrants.csv, in a comma-separated values file format.

    • To do this, we open the file, and the second argument, a, appends to the file, or adds to the end. If we used w for writing, we’d overwrite the previous file with a new one.

    • Then we use a csv module that comes with Python to write to the file, calling a method writerow that actually does the writing of the name and dorm.

  • We can open csv files in Excel or Google Sheets or similar programs, but reading data from them or changing them programmatically require a lot of manual work, and becomes less and less efficient with more data.

SQL

  • To solve this problem of managing data, there exists SQL, Structured Query Language.

  • It’s a programming language that allows us to do basic operations on data, and there are many programs that support using this language, such as MySQL and PostgreSQL.

  • Usually those programs act as a server and listens for requests and responds to them, but a simpler implementation is SQLite, which allows us to use SQL.

  • Spreadsheet programs like Excel or Google Sheets allow us to store data in rows and columns. We commonly use the top row for headers like "name" and "dorm", and each row after will be an entry:

    students
  • In this example, we have structured data, with keys, or metadata that describes each column, and values.

  • We can think of this as a list of rows, each of which is a dictionary. Each row has a cell for each column, so we can make key-value pairs for each cell, where the key is the header for that column and the value is what’s in that cell for that row.

  • But we can better design a data storage system that is efficient and easy to integrate with other programs.

  • We’ll need basic operations:

    • CREATE …​

    • INSERT …​

    • SELECT …​

    • UPDATE …​

    • DELETE …​

    • …​

  • SQL is used for relational databases, or databases with data that has relationships with each other in different tables.

  • Within the CS50 IDE, we’ll run a program called phpliteadmin to create an SQLite database, and then we’ll be able to manage it with a web-based interface: