Author Archives: julianrawcliffe

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')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

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

References

The suggestion above was founmd at https://stackoverflow.com/questions/45456415/peewee-to-print-generated-queries

Device mounting through Windows Services for Linux

 

One of my concerns with moving to Windows from a 20-year stretch of only using Linux at home is whether I would continue to access removable media on the Ubuntu VM.

All that’s required is to create an appropriate mount point and use the drvfs device typeusing,

$ sudo mkdir /mnt/cdrom
$ sudo mount -t drvfs E: /mnt/cdrom

The contents of the cdrom are now available in the VM under /mnt/cdrom.

LDAP under WSL2

The Python Flask application I have been dabbling with for the past year or so uses LDAP to authenticate add and update actions. Moving my development host to Ubuntu on Windows Services for Linux on Windows 10 Home edition means I need a local LDAP server.

Slapd on Ubuntu

Now, since I have a Ubuntu ‘VM’ available that is running native and supports installation of standard Ubuntu packages, installing the slapd package would be a natural starting point. But

Setting up slapd (2.4.45+dfsg-1ubuntu1.4) ...
  Backing up /etc/ldap/slapd.d in /var/backups/slapd-... done.
  Moving old database directory to /var/backups:
  - directory unknown... done.
  Creating initial configuration... done.
  Creating LDAP directory... failed.
Loading the initial configuration from the ldif file () failed with
the following error while running slapadd:
    5d72add0 => mdb_next_id: get failed: MDB_BAD_TXN: Transaction must abort, has a child, or is invalid (-30782)
    5d72add0 => mdb_tool_entry_put: cursor_open failed: MDB_BAD_TXN: Transaction must abort, has a child, or is invalid (-30782)
    slapadd: could not add entry dn="dc=localdomain" (line=1): cursor_open failed: MDB_BAD_TXN: Transaction must abort, has a child, or is invalid (-30782)
    5d72add0 mdb_tool_entry_close: database dc=localdomain: txn_commit failed: MDB_BAD_TXN: Transaction must abort, has a child, or is invalid (-30782)
dpkg: error processing package slapd (--configure):
 installed slapd package post-installation script subprocess returned error exit status 1

This kind of service isn’t supported on WSL2.

Slapd on Docker

Slapd is a service well suited to running under a Docker container. But, Docker on Windows desktop is only supported for Windows 10 Pro. I’m on ome.

Slapd for Windows

A quick search for OpenLDAP on Windows turns up an installer from https://sourceforge.net/projects/openldapwindows/files/openldap-2.4.32/openldap-2.4.32-x86.zip/download. Note that there is a later 2.4.44 version available but this requires a registration key that is no longer available.

Anyhow, Ubuntu’s ldappasswd can be used to create a new rootpw to be added to OpenLDAP\etc\openldap\slapd.conf

$ slappasswd -h {SSHA}

The slapd service can then be started via the Start menu and an ldif in OpenLDAP\etc\ldif\base.ldif edited to include the necessary directory ous, users and groups needed for the Flask service, using the OpenLDAP CLI from the Start menu,

$ ldapadd.exe -v -x -D "cn=Manager,dc=my-domain,dc=com" -f ..\etc\ldif\base.ldif -W

With that the Flask application can make a connection to the LDAP server and authenticate users before allowing updates.

 

Windows Services for Linux – WSL2

With the ending of development of Antergos Linux announced I started looking around for a new distribution, although to be honest I had tired somewhat on Arch anyway so was eager to branch out a bit.

A friend suggested Manjaro as another Arch-based distro. It didn’t even manage to start the desktop environment for the installer.

I felt guilty at not stumping up any money for the download of elemental Linux but promised that I would donate some of my hard-earneds if it was still around after a week. It was so slow and the interface too much like Unity and really hard to use that it only lasted a day.

Reluctantly I decided to give xubuntu a spin despite my reservations about Ubuntu desktops. But before I could complete the install the PCs power supply failed.

With my son off back to University there’s a Windows 10 desktop idly sitting there only getting powered up to install the latest updates. After a work colleague’s enthusiasm for using Windows Services for Linux I thought I had the perfect opportunity to try it out; I was in the mood for getting back into some project work.

In a word: Loveit.

Sometimes there’s a feeling about something being right and WSL2 is pretty close to that mark. So much so that I doubt that I will make any serious efforts to get the old Linux desktop running again.

It’s not something I’d ever expected to be saying but desktop Linux does seem very tired and very far behind. If I can do my git and build operations from my ubuntu consoles I’ve got everything I need.

I even managed to get the 1903 Win10 update downloaded and installed from https://www.microsoft.com/en-us/software-download/windows10.

Simple Flask login:Flask app or uwsgi

For the purpose of auditing and general good practice I want to protect my applications against unauthorised adds, updates and deletes.

I don’t really want to be writing user account handlers and managers; in a production environment, LDAP will be the authentication source, and it’s too easy to make mistakes and allow login bypasses. I want to keep it simple and do external authentication and just capture the user id for activity logging.

Firstly I tried using HTTP basic auth in nginx (or Apache httpd) against specific resources but it’s a bit messy and I couldn’t get it to reliably work, probably because nginx didn’t recognise the password encoding in use in the htpasswd file. Either way, it also seemed that it could be problematic passing the username thru to uwsgi and the Flask application.

uwsgi basic auth

uwsgi is a complex piece of middleware and appears to support close on 1000 command options, so it’s fairly reasonable to suppose that it will support basic auth: it does.

We can then drop down a layer and get uwsgi to do the authentication

Basic auth with Flask

Finally, there might be a couple of ways of getting Flask itself to do the basic auth.

Flask-login

A simple pip install makes the module available but I run into a couple of problems:

  • The main documentation doesn’t describe how import it into the application; and,
  • an example site does describe it, but it still doesn’t work

It does seem to be mentioned in conjunction with Flask-security but there also seems to be a lot of uncertainty around

I think we’ll give this one a miss.

Flask-BasicAuth

This one seems very basic and hasn’t been updated in many years. It doesn’t look like it will work with htpasswd files.

References

 

Machine reading of crossword grid

Having spent some time ove the weekend entering clues, solutions and breakdowns of some sample crosswords for the hints database web application that I’m occasionally working on, and really struggling with it taking 30 – 45 minutes to add a complete grid I hit upon the idea of maybe trying to write a program that could scan a photo of the grid, determine the grid layout (i.e, identify the positions of the across and down clues) and then use OCR to detect the solution.

Just as a diversion, you understand. Probably won’t get very far. And I’ve still got a bunch of more relevant feature requests to be working on.

But it does mean that I can do a few blog posts with some cool sounding transforms and other mathematical wizardry without really knowing what I’m talking about (perhaps, I know people that have done this as a career, perhaps not).

OpenCV has python bindings and some good tutorials so it might be worth exploring for a while. And probably not as daft as trying to use image recognition in the garden for a smart scarecrowmagpie.

Disabling gnome-keyring

One of my pet peeves with cinnamon on the Antergos desktop is the incessant prompts from the gnome keyring when starting Chrome or Firefox with Gmail or other service loaded. I’ve never requested that Gnome store my passwords nor has it ever prompted for details of what it’s after and how to remove it.

In the past I have even deleted the gnome-keyring binary just to stop the really annoying behaviour, but there is a better way as described at https://www.linuxsecrets.com/archlinux-wiki/wiki.archlinux.org/index.php/GNOME/Keyring.html.

Just copy the gnome-keyring files from /etc/xdg/autostart into ~/.config/autostart and add settings to disable the daemon. According to http://ask.xmodulo.com/disable-gnome-keyring-linux-desktop.html, the implementation in gnome-keyring isn’t that great in any case so the service is probably not that useful anyway.

I’ll choose if I want the desktop to squirrel away my authentication data.

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?

Flask-paginate

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):
        self.page = page
        self.per_page = per_page
        self.total_count = total_count

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

    @property
    def has_prev(self):
        return self.page > 1

    @property
    def has_next(self):
        return self.page < 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 > self.page - left_current - 1 and \
            num < self.page + 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})
@application.route('/crossword-solutions/page/<int:page>')
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 = crossword_solutions.select(fn.COUNT(crossword_solutions.rowid)).scalar()
    PER_PAGE=25
    offset = ((int(page)-1) * PER_PAGE)
    solutions = crossword_solutions.raw("""
        SELECT cs1.name as setter, cs2.solution AS solution, cs2.rowid AS csid, st1.name 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)
    return(render_template('views/crossword-solutions/index.html',
                            r=request,
                            solns=solutions,
                            pagination=pagination))

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.

References

I highly recommend http://flask.pocoo.org/snippets/44/ 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.

https://github.com/slugbucket/crossword-hints/issues/9 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(default=datetime.now())

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,
          action=action,
          item_type=item_type,
          item_id=item_id,
          act_action=activity,
          created_at=datetime.now(),
          updated_at=datetime.now())
    log.save()

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)

Where,

  • 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.

Improvements

  • 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