Category Archives: Python

Python Flask PeeWee SQL debugging


While trying to refactor a Flask PeeWee query written using raw SQL that needed to be done using standard PeeWee methods I was struggling to understand why they query structure I had used was returning zero rows compared to the SQL equivalent.

I struggled equally to  understand if there was any way to determine if there were any PeeWee methods that could be applied to a query to retrieve the underlying SQL when I came across the following:

# Print all queries to stderr.
import logging
logger = logging.getLogger('peewee')

Restart the application and all the queries are shown on stderr along with the placeholder values.


The suggestion above was founmd at

Python Flask pagination

Flask pagination

The [crossword hints]() Flask application I have been writing has been expanding and I have taken some time to add more clues and solutions such that there is now some need to break up the index list into more manageable chunks using pagination.

I’ve been considering a couple of options:

  • the flask-paginate pip which is modelled on Rails’ will_paginate (which I have used in the past.
  • a Flask snippet from Armin Ronacher, which on the face of it looks like it will be harder to implement;
  • a hand-rolled solution, but why re-invent the wheel?


This should be the preferred solution because it reduces the amount of coding required in the application, but I had a cserious problems when it came to rendering the pagination: it’s missing much of the necessary CSS to display the page listing.

At first, the page listing just appeared as a bulleted list. It turns out that Flask-paginate uses a CSS framework called bootstrap but the documentation
doesn’t include any real mention of it, let alone describing it as essential.

StackOverflow responses to Flask=paginate styling problems just said to install
the bootstrap CSS files, but when I tried this it just completely mangled much of
the other navigation styling I already had. I hate messing around with CSS at the best of times so there’s no way I’m wasting time fiddling around with minimised 3rd-party dependent CSS. Time to ‘git checkout — ‘ the files I’d been working on.

Snippet 44

I was wary of attempting to implementing the suggestion in the snippet, not
because I thought it wouldn’t be any good (total regard for Armin’s code), but because of my lack opf confidence working with what looked like a bit more advanced Python and Flask and whether my ropey application could support it being added without a complete rewrite.
I need not have worried.

Anyway, the task breaks down into the following stages:

  • a pagination class
  • a view helper,
  • routing and rendering: URLs and views,
  • CSS to support a reasonable page layout

Pagination class

First off we need a class to contain properties and methods needed to describe the pagination:

  • total number of records,
  • items per page
  • number of pages
  • to determine whether the current page needs next and/or previous links
  • an iterator over the pages; I don’t think I would have been able to write this
    so concisely, so definitely good value here,
class Pagination(object):
    def __init__(self, page, per_page, total_count): = page
        self.per_page = per_page
        self.total_count = total_count

    def pages(self):
        return int(ceil(self.total_count / float(self.per_page)))

    def has_prev(self):
        return > 1

    def has_next(self):
        return < self.pages

    def iter_pages(self, left_edge=2, left_current=2,
        right_current=5, right_edge=2):
        last = 0
        for num in range(1, self.pages + 1):
            if num <= left_edge or \
              (num > - left_current - 1 and \
            num < + right_current) or \
            num > self.pages - right_edge:
            if last + 1 != num:
                yield None
            yield num
            last = num

I had to make a tiny change to what appeared in the snippet, replacing xrange with just range.

View helper

There needs to be a view helper that can be used in a template macro to generate the link to the other pages.

def url_for_other_page(page):
    args = request.view_args.copy()
    args['page'] = page
    return url_for(request.endpoint, **args)
application.jinja_env.globals['url_for_other_page'] = url_for_other_page

Although this has been added to my main application file it is something that can be relocated somewhere more sensible when refactoring work starts; registering the helper with Jinja this way is certainly something I can try with future projects so this snippet is teaching more than just pagination. Win-win.

Routing and rendering

The trickiest part of the process is probably changing the index route to support displaying a particular page of content.

Default and paged indexes

The easiest way to drive the pagination routing is to associate a page number with the route

@application.route("/crossword-solutions/", defaults={'page': 1})
def crossword_solution_index(page):

The index routing needs to include:

  • a count of the number of items
  • a query that only selects the necessary records for display,
  • a check to capture out of range page requests,
  • create a pagination instance and pass it to the template to render

This gives an index router like:

    count =
    offset = ((int(page)-1) * PER_PAGE)
    solutions = crossword_solutions.raw("""
        SELECT as setter, cs2.solution AS solution, cs2.rowid AS csid, AS soltype
        FROM crossword_setters cs1
        INNER JOIN crossword_solutions cs2
        ON cs1.rowid = cs2.crossword_setter_id
        INNER JOIN solution_types st1
        ON cs2.solution_type_id = st1.rowid
        ORDER BY cs2.solution
        LIMIT %s, %s""" % (offset, PER_PAGE))
    # Display a 409 not found page for an out of bounds request
    if not solutions and page != 1:
        return(render_template('errors/409.html', errmsg="Requested page out of bounds"), 409 )
    pagination = Pagination(page, PER_PAGE, count)

Although I’m using PeeWee as the ORM, I’ve found that queries like this one work better as plain SQL particularly with the offset and limit. What we notice here is that the pagination has no connection at all with the database result set like, say, will_paginate, but still doesn’t lose any functionality.

Pagination macro

I’m still not very experienced with Jinja2 macros so really appreciated the sample provided by the snippet. I modified it slightly to include a previous page link and also display next and previous as grayed-out boxes and the last and first pages respectively.

{% macro render_pagination(pagination) %}

<br />
{% endmacro %}

This is added to an existing macros,html template file.

Template rendering

The macros file is imported into a high-level layout template as

{%- import "macros.html" as f -%}

and child templates apply the pagination with,

{{ f.render_pagination(pagination) }}

Pagination stylesheet

I dislike HTML layout in general and loathe CSS in particular so I will settle for whatever gives a reasonable look despite any ‘obvious’ inefficiencies and any prolonged CSS development will inevitably provoke much cussing and cursing. I have based the styling for my current project around ‘digg_pagination’ styles I came across for old Rails projects with as few tweaks as possible; I really don’t why some settings get included from some sections and not other.

The styling boils down to the following sections (I include it only as an example
and not a recommendation; no-one in their right mind would trust me to design CSS)

.pagination {
margin: 10px 0;
margin-top: 0;
margin-bottom: 0;
font-size: 0.7em;
.pagination ul li {
border-color: #105cb6;
border-width: 0 0 1px;
border-radius: 0;
float: left;
list-style-type: none;
.pagination ul li span.nolink {
padding: 2px 10px 2px 10px;
display: block;
/* float: left; */
margin-right: 1px;
font-size: 8pt;
font-weight: bold;
border: 1px solid #9aafe5;
color: #D5D5D5;
.pagination a, .pagination ul li span {
padding: 2px 10px 2px 10px;
display: block;
/* float: left; */
margin-right: 1px;
font-weight: bold;
color: #105cb6;
border: 1px solid #9aafe5;
.pagination ul li span.ellipsis {
/* font-size: 10pt; */
font-weight: normal;
padding: 2px;
margin: 1px;
border-color: #fff;

Which I think gives a reasonable look to the pagination with greyed out boxes for the prev and next links on the first and last pages and clearly indicates the active page.


I highly recommend as a great place to start if looking at pagination for a Flask application; in general, Armin’s posts here are top-notch and very informative.

Python Flask activity logging

This post describes a simple pproach to recording login, logout, insert, update and delete operations that are carried out by a Python Flask application.

This is not the same as a table auditing function similar to Ruby’s Paperclip module. is an example feature branch for a personal project implementing this.

The activity log will be stored in a database table with the following structure:
* rowid – auto-assigned unique id for the activity record
* actor – the name of the user performing the action
* action – one of login, insert, update, delete or logout,
* item_type -the table on which the operation has been performed.
* item_id – the numeric id of the item under operation
* act_action – details of the content that has been changed

This can be modeled in Flask using PeeWee

from peewee import *
from datetime import date, timedelta, datetime


class BaseModel(Model):
    with application.app_context():
    class Meta:
        database = database

    class activity_logs(BaseModel):
        rowid = AutoField()
        actor = CharField(max_length=32)
        action = CharField(max_length=32)
        item_type = CharField(max_length=32)
        item_id = IntegerField()
        act_action = TextField()
        created_at = CharField(max_length=32)
        updated_at = DateTimeField(

The application context is called application to work better when deployed to AWS.

Having used Flask-login to control the login process. we have a variable. current_user, that contains a numeric id of the user from their record in teh database.

The users model includes a method to return the name of the user and this can be used for the ‘actor’ value.

A simple function can create the activity log reord:

def add_log(actor, action, item_type, item_id, activity):
    log = activity_logs(actor=actor,

And activities to be logged can be logged with something like,

log = ("name: %s\ndescription: %s" % (name, description))
add_log(users.get_name(current_user), 'update', 'solution_types', id, log)


  • name and description are variables containing form-submitted data
  • activity_logs is the PeeWee model of the databse table
  • users is a PeeWee ORM model of an application user that can be authenticated.

When used the (sqlite3) database records will look like,

sqlite> select * from activity_logs;
crossy|login|user|1|Successful login for crossy|2019-02-15 18:44:38.257050|2019-02-15 18:44:38.257061
crossy|update|solution_types|12|name: Homophone
description: Sounds like a word that has a different spelling|2019-02-15 18:45:03.227188|2019-02-15 18:45:03.227193
crossy|logout|user|1|Successful logout user for crossy|2019-02-15 18:48:11.043243|2019-02-15 18:48:11.043249

With these records it should be possible to display an activity report and perhaps even recover previous versions of particular database records.


  • Storing the full record on each update is inefficient; only differences should be recorded.
  • The logging function should be able to determine what to include in the log message based on the

Configure Python Flask application for LDAP authentication

Using a crossword-hints Flask application to include authentication for adding, modifying and deleting database content.
We will use ad a users table to the database and hand off the authentication to the directory.

Install the python-ldp pip (3.1.0 as of February 2019)

$ pip install python-ldap --user

Install the Flask-login pip (0.4.1 s of February 2019)

$ pip install flask-login --user

Include the modules in the Flask application

from flask_login import LoginManager, UserMixin, login_required, login_user, logout_user
import ldap

Configure Flask-login in the application

# flask-login
login_manager = LoginManager()
login_manager.login_view = "crossword_login"

The login_view indicated the function to be called to handle the user login.

Flask-login also requires a user_loader function that returns an integer id value for the user,

def load_user(id):
    return users.get(users.rowid == int(id))

Add a function to get a connection to the directory (but no authentication at this point)

def get_ldap_connection():
    conn = ldap.initialize('ldap://localhost:389')
    return conn

Add a route to the login form. This accepts GET and POST requests to display the form and then
process the submitted form. Notice that after successful login there is a redirect to a page passed as the next parameter in the query string when an application route requires login: this could be a securiity concern if steps are not taken to prevent the application accepting a spoofed resource as input rather than a permitted resource in the intended application.

@application.route("/login", methods=['GET', 'POST'])
def crossword_login():
        if current_user.is_authenticated:
        flash('You are already logged in.')
        return redirect(request.path)

    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')

            users.try_login(username, password)
        except ldap.INVALID_CREDENTIALS:
                'Invalid username or password. Please try again.', 'danger')
            return(render_template('views/login/login.html', u=username, r=request))

        user = users.get(users.username == username)
        return redirect(request.args.get("next"))
        username = 'username'

    return(render_template('views/login/login.html', u=username, r=request))

A template for the login form could be

{%- import "macros.html" as f -%}
Use this form to login to the site to maintain restricted content.
<form name="crossword_login" action="{{ r.full_path }}" method="POST">
<fieldset id="crossword_login">
{{ f.label('username', 'Username') }}
{{ f.input_tag('username', value=(u|escape()|default('')), class="xwordhints") }}

{{ f.label('password', 'Password') }}
{{ f.input_tag('password', type="password", class="xwordhints") }}

<input type="submit" name="login" value="Login" />
<input type="reset" value="Reset" />
<br /><br />
<a href="/crossword-hints">Back to index</a>

macros.html (there’s a good GitHub gist with some macros but I can’t find a link to it) contains helpers to create the form input elements. The form action
uses the request’s full path which includes the query string containing the next parameter.

Provide a logout route. Note that this requires a valid login. Going direct to /logout will show a prompt to login!

def logout():
    flash("Logout successful. Please close browser for best security.")

A PeeWee user model

class users(BaseModel):
    rowid = AutoField()
    username = CharField(null=False, max_length=32, unique=True)
    created_at = DateTimeField(
    updated_at = DateTimeField(

    def try_login(username, password):
        conn = get_ldap_connection()
            'uid=%s,ou=People,dc=my-domain,dc=com' % username, password

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        conn = get_ldap_connection()

    def get_name(self):

The get_name method is used by HTML templates to display the logged in username. For authentication the try_login method just attempts to bind to the directory as the user.

{% if current_user.get_id() is not none %}Logged in {{ current_user.get_name() }}&nbsp; <a href="/logout">Logout</a>{% endif %}

Routes that need authentication (and authoristion) before access simply need to state ‘login_required):

@application.route("/crossword-setters/<int:id>/edit", methods=["GET", "POST"])
def crossword_setters_edit(id):

Accessing this route will redirect to the login form and successful login will redirect back to the edit page.

Unit tests

When requiring authentication to enable certain operations in the application, consideration needs to be given to the impact on running unit tests.

The protecting views section of the Flask-login site indicates that this is simply a matter of setting the following option:


which disables the login_required decoration applied to routes. Adding this to a settings file used when running the tests allows everything to complete as expected.

APP_SETTINGS='' python



  • Don’t use the SQL database to store the users
  • Control authorisation according to group membership.
  • Prevent authentication leakage via spoofed ‘next’ resources.
  • Don’t request a login when going direct to the logout route.


Restricted access to EB instance

Almost embarrassed to admit that I spent much of the day trying to figure why my attempts at applying a custom nginx configuration scheme to block access to the editable content on my test site at were failing because the default Python instance actually runs Apache httpd!

There was certainy enough evidence in the logs but as soon as I figured out how to SSH to the instance, it didn’t take long.

For reference, SSHing to the instance requires that the EC2 key pair be applied to the environment through the Security settings; it’s likely that this can also be done via the CLI.

Checking the EC2 control panel for the instances will give the hostname to use for SSH login; just change the path to the SSH key that has been uploaded to AWS.

$ ssh -i ~/.ssh/private-key

There are  couple of ways of applying the custom configuration needed to restrict access to the editable resources but the method I settled on was by adding the content to the .ebextensions/options.config with an entry in a section called files:

    SECRET_KEY: ChangeMe

    mode: 0644
    content: |
      <LocationMatch "/(crossword-solutions|crossword-setters|setter-types|solution-types)/[0-9]+/(edit|delete)">
        Require all denied
      <LocationMatch "/(crossword-solutions|crossword-setters|setter-types|solution-types)/new">
        Require all denied
      ErrorDocument 403 /static/403-xword-hints.html

It’s important to ensure that the indentation is correct for the file definition and content; the following deployment error will be thrown if not:

Service:AmazonCloudFormation, Message:[/Resources/AWSEBAutoScalingGroup/Metadata/AWS::CloudFormation::Init/prebuild_0_crossword_hints/files//etc/httpd/conf.d/xword-hints-deny.conf] 'null' values are not allowed in templates

The application needs to includes the 403 document, 403-xword-hints.html, because the web server will pass the request for the custom error page to it as a normal HTTP request.

With all this in place, the application is reasonably safe to leave running on the internet with any attempt to create, edit or delete content yielding a permissions error.

And the updates are still be applied by a Jenkins job pulling branch code from GitHub.

AWS ElasticBeanstalk custom environment variables

As a holiday project I’ve been looking into using Jenkins to deploy code updates from GitHub into an Amazon AWS ElasticBeanstalk instance[1] as an early attempt at some sort of continuous delivery.

One of the features of the Flask application is that it tries to get the SECRET_KEY from an environment variable (although the code for a failsafe value doesn’t work: FIXME). The intention is that the web server environment provides the key at runtime so that different values can be used in each environment.

Now, this AWS page describes the format of the options to apply custom environment settings to an application (the name of the actual file doesn’t matter so long as it is called .config and is found in the .ebextensions directory in the uploaded code):

    SECRET_KEY: ChangeMe

Setting the WSGIPath variable means that I can continue to use the original application source file rather than change to the default

This file can safely be kept in the GitHub repo and setup as a simple shell build step in Jenkins prior to the code upload, thus:

SECRET_KEY=`openssl rand -base64 12`; sed -ie "s/ChangeMe/${SECRET_KEY}/" .ebextensions/options.config

Jenkins has a great AWS EB deploy plugin that uses stored credentials to mange the source bundling, upload and deployment of the application; it’s kinda strange seeing the AWS console page spring into life in response to the Jenkins job running. To save having to include the build shell step, I’m thinking of creating my own version of the plugin that allows the inclusion of custom variables.

[1] – As a development instance the application will be mostly terminated (and offline) because AWS is a very expensive way of running a bit of demo code.


Ensure list only consists of strings

A nice little to remember for future use is when a list must consist of only strings:

def arrayOfStrings(l):
    nl = [s if isinstance(s, str) else str(s) for s in l]

It looks like it could be quite Pythonic and isn’t necessarily how I would have written it if left to my own devices; I might be braver and try that sort of structure in other situations.

Alas, I don’t have a reference for where I grabbed this from but it’s more than likely from StackOverflow.

Dynamic (in-memory) zip file creation

With a recent project I wanted to offer a download of a bunch of files as a ZIP download to make redistribution. I started by thinking that this would involve creating a zip file on disk and then serving it to the client.

It doesn’t.

This method creates an in-memory zip image of a temporary directory in which the required file are created. The directory is deleted before the zip file is returned to the client. Note the use of send_file to return the content and how it allows us to specify the name of the downloaded file.

If running the app under uwsgi there are some precautions to take:

  • import the send_file module from flask (the default wsgi version is broken)
  • Include ‘wsgi-disable-file-wrapper’ in the uwsgi settings
import os
import io
import tempfile
import zipfile
from peewee import *

# A simple function to write a file in a given directory
def makeFile(dir, filename, data):
    fname = str(dir) + '/' + str(filename)
    fh = open(fname, "w")
    return True
Function to create a zip file containing the application configuration files
To create the zip file, we need to create a memory file (BytesIO))
 row: Database row object containing columns for filename and
 content to be written in the form
 { {"file1": "Content for file 1}, {"file2": "Different content for file"} }
 data: BytesIO object containing the zipped files
def mkZipFile(row):
    zipdir = tempfile.mkdtemp(prefix='/tmp/')
    oldpath = os.getcwd()

    jdata = json.loads(row)
    for conf in jdata:
        for f in conf:
            makeFile(zipdir, f, conf[f])

    # Create the in-memory zip image
    data = io.BytesIO()
    with zipfile.ZipFile(data, mode='w') as z:
        for fname in os.listdir("."):

    return data

The route takes the form

Route to create a zip file containing all the required data files
 id: the numeric id of the record in the database
 ZIP: file in ZIP format containing the download files
@app.route('/download/<int:id>.zip', methods=['GET'])
def showZip(id):
    rs = == id).get()

    zfile = mkZipFile(rs)
    if not zfile:
        return Response(response="Invalid zip file", status="400")

    return send_file(


The new submission form

This post will detail the processing of a new submission form that contains a multiple select form element.

A widget is described as a set of versioned components where a component version could be used in many widgets:
id: INT
name: STRING
description: TEXT

id: INT
name: STRING
component_id: INT

id: INT
name: STRING
description: TEXT

component_id: IN
widget_id: INT

A simple route to handle a request to create  new item could be something like:

@app.route('/widgets/new', methods=['GET', 'POST'])
def newWidget():
    # Wasn't able to find any real documentation or examples of how to use the concat
    # function but sort of figured it out by looking at the PeeWee source code. This
    # uses a local function to create a list of list of lists of the items to be
    # displayed in the multiple select element in the form
    # [ [1, <component_name>-<component_version> ], [2, <component_name>-<component_version> ], ... ]
    # that is hopefully easier to process in the template 
    cvids = dict2lol(component_versions
             .join(components, JOIN.INNER,
             on=component_versions.component_id ==
             .dicts(), 'cmpntid', 'cmpntname')
    if request.method == 'POST':
        (status, msg) = validateWidget(request.form)
         # Use .getlist() to retrieve the multiple select items
         slctd = request.form.getlist('component_version_ids')
         if status:
             # Although auto-increment works with SQLite3 it didn't seem to work
             # when creating records with PeeWee
             nextId =
                                         name=request.form['name'], \
                                         description=request.form['description'], \
                                         updated_by="config.admin", \
             for cvid in slctd:
                  component_version_widget.create(widget_id=nextId, component_version_id=cvid)
                  logActivity('create', 'component_version_widgets', nextId, ("component_version_id: %s" % pvid))

             logActivity('create', 'widgets', nextId, request.form['name'])
              flash(("Saved new widget, update for %s." % request.form['name']))
              return redirect('/widgets')
             flash("Widget creation failed: %s." % msg)
              slctd = []
              for pv in request.form.getlist('component_version_ids'):
                 wdgt = {"description": request.form['description'],
                           "name": request.form['name'],
                           "component_version_ids": slctd}
        wdgt = {'name': 'Widget name', 'description': 'Brief description', "component_version_ids": []}
    return render_template('views/widgets/new.html', wdgt=wdgt, cvids=cvids, req=request)


  • cvids: a list of lists of select options in the form [id, name]. Possibly being
    lazy but this is a bit simpler than trying to process what might come out of a PeeWee
    result set
  • slctd: a list of selected options from the select form; either from the database or the
    submitted form data. When creating this list from the submitted form we need to cast the id values as int’s or they won’t be recognised by the template inline conditional.
  • wdgt: dict containing the values for the template form items
  • formatting python code in the edit window is quite tricky so no guarantees that it compiles cleanly

The template to display this might look a bit like,

<form name="widgets" method="POST" action="{{ req.path }}">

Widget Widget name Description {{ rel['description'] }}
Component versions:
{%- for opt in cvids-%} {{ opt[1] }} {%- endfor -%}
<input type="submit" name="submit" value="Save widget"> </form>

The only real point to note in here is the method by which the ‘selected’ attribute is added to the option in the select list: the inline if.