# The new objects imported here are redirect and g. redirect is used to # issue a redirect back to the web browser to have it request another # app endpoint. g is a per-request object used to store global state during # a request. (You've seen url_for already in your Jinja templates.) from flask import Flask, render_template, request, redirect, url_for, g # connect is used to connect to a PostgreSQL database. from psycopg2 import connect # DictCursor allows us to work with the results returned by fetch # operations on a cursor as if they were stored in a dictionary, rather # than a tuple. tuple access via an index is still possible. from psycopg2.extras import DictCursor # As necessary, add variable definitions to your cs417secrets.py file. # Remember, they should have the form # DBDEMO_DB = 'registration' # These definitions will be stored in the app.config object as dictionary # key and value pairs. See below. app = Flask(__name__) app.config.from_pyfile('config.py') # This decorator is used to register a function to be called once the # request is completed. Use it to clean-up at the end of a request. The # registered function takes a response object parameter and returns a response # object. @app.after_request def afterReq(response): closeDb() return response # Get the connection to the back-end db. If the connection doesn't yet # exist, create it. Note how to access your application "secrets" via # the app.config dictionary. def getDb(): if 'db' not in g: g.db = connect(dbname=app.config['TODO_DB'], user=app.config['TODO_USER'], password=app.config['TODO_PW'], cursor_factory=DictCursor) return g.db def closeDb(): db = g.pop('db', None) if db is not None: db.close() @app.route('/') def index(): # Open a cursor using a context manager. The cursor will be closed # when the context is exited. Cursors are used to interact with # the records returned by a query. See the documentation for the # Cursor class in psycopg2 for details. with getDb().cursor() as cur: # Get all todo records, sorted in ascending order by the id field. cur.execute('select * from todo order by id asc;') todos = cur.fetchall() # By default, any DB operation, even a read, opens a transaction. # It's best to not leave an unneeded transaction hanging around, # so let's commit it. getDb().commit() return render_template('todo.html', todos=todos) @app.route('/add', methods = ['POST']) def add(): # Insert a new todo into the todo table. todo = request.form['todo'] with getDb().cursor() as cur: # %(todo)s is a named parameter in the query string. vars is a # dictionary specifying the mapping from the named parameters to their # values. Constructing the query in this fashion allows psycopg2 # to properly escape input from users, preventing SQL injection # attacks. query = 'insert into todo (title) values (%(todo)s);' vars = { 'todo' : todo } cur.execute(query, vars) getDb().commit() # Issue a redirect to the web browser, causing it to request the index # end point. return redirect(url_for('index')) @app.route('/delete', methods = ['POST']) def delete(): # The checkbox elements all have the same name, id. Gather them # into a list, interpreting the values as ints. ids = request.form.getlist('id', type=int) with getDb().cursor() as cur: query = 'delete from todo where id=%(id)s;' # One by one, delete the todos. This uses one query string, # used with a different id parameter for each iteration. for id in ids: vars = { 'id' : id } cur.execute(query, vars) getDb().commit() return redirect(url_for('index')) if __name__ != '__main__': application = app