true false maybe

tom longson’s blog on software, design, and user experience

Profiling SQL in Pylons with Dozer

So you want to find out why your Pylons app is running slowly? Well most likely it has to do with your SQL queries, and the best way to see what’s going on and how long each request is taking is to install Dozer (by benbangert of Pylons), and load it up with a TimerProxy (by zzzeek of SQLAlchemy).

Sound like fun? Well, here’s how to do it.

Install Dozer:

sudo easy_install -U http://www.bitbucket.org/bbangert/dozer/get/b748d3e1cc87.gz

Add this to your middleware:

# Add this to your middleware.py, right before return app
    if asbool(config['debug']):
        from dozer import Logview
        app = Logview(app, config)

Add this to your development.ini

# Add to development.ini
logview.sqlalchemy = #faa
logview.pylons.templating = #bfb

(you can customize the colors here)

Next, modify your configuration ini as well as you like to configure what shows up in the log. Note that I have root set to INFO which will squelch a lot of messages. Change this to DEBUG to see more of what’s going on in each request.

# Logging configuration
[loggers]
keys = root, YOURPROJ

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = INFO
handlers = console

[logger_YOURPROJ]
level = DEBUG
handlers =
qualname = YOURPROJ.lib

[logger_sqlalchemy]
level = INFO
handlers =
qualname = sqlalchemy.engine

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(asctime)s,%(msecs)03d %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

Add this file to /lib/

querytimer.py

from sqlalchemy.interfaces import ConnectionProxy
import time

import logging
log = logging.getLogger(__name__)

class TimerProxy(ConnectionProxy):
    def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
        now = time.time()
        try:
            return execute(cursor, statement, parameters, context)
        finally:
            total = time.time() - now
            log.debug("Query: %s" % statement)
            log.debug("Total Time: %f" % total)

Okay, one last thing, modify your SQLAlchemy engine in environment.py to this:

engine = engine_from_config(config, 'sqlalchemy.', proxy=TimerProxy())

and add an import at the top:

from YOURPROJ.lib.querytimer import TimerProxy

So that’s it! Restart paster, and load up a request in your web browser. There will now be a bar at the top that you can click on and see all the requests.

If you want to run TimerProxy on it’s own (that is without Pylons and Dozer, see zzzeek’s post on “Timing All Queries“.


SQLAlchemy Migrations

Migrations are useful for emerging projects because they allow developers to evolve their models, and incrementally define schema change scripts so that old database records can be upgraded and downgraded easily.

When I started using Pylons, I set up a tinderbox to allow a 2nd copy to live for our team to play with the system and create data structures. In order to allow this data to live between major model changes, this meant I had to find a way to manage migrations. Using “sqlalchemy-migrate”, I was able to achieve just that. Here’s my quickstart on how to get up and running with sqlalchemy migrations under pylons. Now whenever I update my tinderbox (it’s not automatic quite yet), I just run “svn up; python dbmanage.py upgrade”.

Quickstart Guide:

1. Install sqlalchemy-migrate

sudo easy_install sqlalchemy-migrate

2. Go to your project directory

cd myapp

3. Create your sqlalchemy-migrate repository in your project

migrate create myapp/migrations/ "MyApp"

This will create the directory “migrations” in your myapp/myapp/ directory.

4. Add the migrate tables to your database:

migrate version_control mysql://user:pass@myhost:3306/database myapp

5. See what version the repository is at:

migrate version banyan/migrations

4. Create the dbmanage script. This is mostly a configuration script to make sa-migrate easier to use.

migrate manage dbmanage.py --repository=myapp/migrations/ --url=mysql://user:pass@myhost:3306/database

5. Create the first migration script

migrate script "Add initial tables"

Other documentation says to do ‘migrate script script.py’, but this didn’t work for me properly. What this will do is create

6. Edit your first migration script.

Open up the file in your favorite editor, and change it to meet your needs. In my case, it looks like this:

import time, datetime
from sqlalchemy import *
from migrate import *

meta = MetaData(migrate_engine)

users_table = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('display_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now)
                    )

def upgrade():
    # Upgrade operations
    users_table.create()

def downgrade():
    # Operations to reverse the above upgrade
    users_table.drop()

7. Test your new script.

python dbmanage.py test

8. “Commit” your changes.

python dbmanage.py upgrade

Note, the documentation I read said to use the command “commit” instead of “upgrade”. I have no idea why this is, the dbmanage script doesn’t know what to do when you ask it to commit. When you upgrade though, it will run the changes to your database, and change the migrations version from 0 to 1.

9. Rinse and repeat.
Do steps 5 through 8 to whenever you have new database changes you want to add to your database.

Here’s another example to show an alter. The module migrate.changeset allows you to alters that SQLAlchemy at this time does not.

1. Create another revision script.
Lets say we wanted to change the column display_name to first_name, and add a last_name column.

python dbmanage script "Change display_name column to real names"

2. Edit that file.

My one looks like this:

import time, datetime
from sqlalchemy import *
from migrate import *
import migrate.changeset

meta = MetaData(migrate_engine)

users_table_new = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('first_name', Unicode(255)),
                    Column('last_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now)
                    )
users_table_old = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('display_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now),
                    useexisting=True
                    )

def upgrade():
    # This assumes we're moving all existing accounts to have their first_name
    # be their previous display_name
    users_table_old.c.display_name.alter(name="first_name")
    # Note the above users_table_new has a new last_name column already defined in
    # it. This column has not been created yet in the database, so we will do that here:
    users_table_new.c.last_name.create()

def downgrade():
    # Again, for every upgrade, we have to have a downgrade
    users_table_new.c.first_name.alter(name="display_name")
    # Any downgrade will result in loss of last_name data with this script.
    users_table_new.c.last_name.drop()

3. Test the changes (again).

python dbmanage test

Everything good?

4. “Commit” the changes (yes, again):

python dbmanage upgrade

Awesome! If you have anything to add or catch any mistakes, please drop me a comment.


Decorators – Before and After

I had some trouble understanding decorators, especially on how to get decorators to execute in the order I wanted. That is, do_something() before the decorated function, or after the decorated function.

Jon Rosebaugh gave me this quick sample to explain how to get my decorator to execute in the desired order. In this case, he did a print statement instead of do_something(), but it’s still pretty straight forward.

from decorator import decorator

@decorator
def pre_call(f, *a, **kw):
    print "I'm using the internet!"
    return f(*a, **kw)

@decorator
def post_call(f, *a, **kw):
    result = f(*a, **kw)
    print "I'm using the internet!"
    return result

def f1():
    print "Hello World"

f2 = pre_call(f1)
f3 = post_call(f1)

print "calling f1"
f1()
print "calling f2"
f2()
print "calling f3"
f3()

If you just call f1(), of course you’ll see “Hello World”. No surprise there.

Calling f2() is a bit different though, it wraps the f1 function in pre_call – pre_call(f1)

@pre_call
def f1():
    print "Hello World"

The result you’ll get with this is the following:

I'm using the internet!
Hello World

Inversely, if you call f3(), which wraps the f1 function in post_call, the original function, f1 gets executed to begin with in the line result = f(*a, **kw), and is returned after the decorator’s print statement is fired. The result is of course this:

Hello World
I'm using the internet!

In Pylons, pre-call decorators are useful for intercepting controller requests and doing things like authentication. Post-call decorators on the other hand are more useful for transforming data, just as the pylons.decorators.jsonify does in taking the output of a controller, and turning it into a JSON string. Another post-call decorator might turn all your output text into pig-latin, or if you’re really anal, censor those seven deadly words.

  • Filed under: Pylons, Python
  • Published at: 12:50 pm on June 23, 2008
  • Comments: 1 comment
  • Written by: admin

Authorization in Pylons with Repoze.who (Part 1 – HTAccess)

I am in the process of getting Repoze.who working with Pylons, which I believe will provide me with a strong authentication framework for my application, and allow me to do users, groups, and roles. Luckily enough, I had the generous help from Author, Chris McDonough, who also helped write this tutorial with me.

This first part in my new series on working with Repoze.who will allow you to set up a very basic HTAccess based Authentication layer for your Pylons app. We’ll start off by installing Repoze.who, get moving by modifying the Middleware, and conclude by putting some logic in a controller to limit access to only authorized users. This is by no means a perfect solution to getting your Pylons app secure, but should be a good primer on how Repoze.who works, and will serve as a stepping stone to my next article on using SQLAlchemy with Repoze.who and Pylons to do groups and permissions.

Okay, now lets get on our way!

Install Repoze.who
Download and run the latest code from http://dist.repoze.org/who/latest/

wget http://dist.repoze.org/who/latest/repoze.who-1.0.1.tar.gz
tar xfz repoze.who-1.0.1.tar.gz
cd repoze.who-1.0.1
sudo python setup.py build
sudo python setup.py install

Configuring the Middleware

To get really started, you’ll need to modify middleware.py. To get an understanding of what middleware is, check out http://dev.pocoo.org/~mitsuhiko/werkzeug_en.pdf & http://dirtsimple.org/2007/02/wsgi-middleware-considered-harmful.html (thanks rcs_comp for the links).

To begin with, middleware.py probably looks a bit like this:

"""Pylons middleware initialization"""
from paste.cascade import Cascade
from paste.registry import RegistryManager
from paste.urlparser import StaticURLParser
from paste.deploy.converters import asbool

from pylons import config
from pylons.error import error_template
from pylons.middleware import error_mapper, ErrorDocuments, ErrorHandler, \
    StaticJavascripts
from pylons.wsgiapp import PylonsApp

from myapp.config.environment import load_environment

def make_app(global_conf, full_stack=True, **app_conf):
    """Create a Pylons WSGI application and return it

    ``global_conf``
        The inherited configuration for this application. Normally from
        the [DEFAULT] section of the Paste ini file.

    ``full_stack``
        Whether or not this application provides a full WSGI stack (by
        default, meaning it handles its own exceptions and errors).
        Disable full_stack when this application is "managed" by
        another WSGI middleware.

    ``app_conf``
        The application's local configuration. Normally specified in the
        [app:] section of the Paste ini file (where
        defaults to main).
    """
    # Configure the Pylons environment
    load_environment(global_conf, app_conf)

    # The Pylons WSGI app
    app = PylonsApp()

    # CUSTOM MIDDLEWARE HERE (filtered by error handling middlewares)

    if asbool(full_stack):
        # Handle Python exceptions
        app = ErrorHandler(app, global_conf, error_template=error_template,
                           **config['pylons.errorware'])

        # Display error documents for 401, 403, 404 status codes (and
        # 500 when debug is disabled)
        app = ErrorDocuments(app, global_conf, mapper=error_mapper, **app_conf)

    # Establish the Registry for this application
    app = RegistryManager(app)

    # Static files
    javascripts_app = StaticJavascripts()
    static_app = StaticURLParser(config['pylons.paths']['static_files'])
    app = Cascade([static_app, javascripts_app, app])
    return app

But you want it to look more like this:

"""Pylons middleware initialization"""
from paste.cascade import Cascade
from paste.registry import RegistryManager
from paste.urlparser import StaticURLParser

from paste.deploy.converters import asbool

from pylons import config
from pylons.error import error_template
from pylons.middleware import error_mapper, ErrorDocuments, ErrorHandler, \
    StaticJavascripts
from pylons.wsgiapp import PylonsApp

from myapp.config.environment import load_environment

"""Repoze.who stuff"""
from repoze.who.interfaces import IIdentifier
from repoze.who.interfaces import IChallenger
from repoze.who.plugins.auth_tkt import AuthTktCookiePlugin
from repoze.who.plugins.cookie import InsecureCookiePlugin
from repoze.who.plugins.form import FormPlugin
from repoze.who.plugins.htpasswd import HTPasswdPlugin
from repoze.who.middleware import PluggableAuthenticationMiddleware
import StringIO
from repoze.who.classifiers import default_request_classifier
from repoze.who.classifiers import default_challenge_decider
# for repoze.who debug
import sys
import logging

def cleartext_check(password, hashed):
    return password == hashed

def make_app(global_conf, full_stack=True, **app_conf):
	"""Create a Pylons WSGI application and return it
	``global_conf``
        The inherited configuration for this application. Normally from
        the [DEFAULT] section of the Paste ini file.

    ``full_stack``
        Whether or not this application provides a full WSGI stack (by
        default, meaning it handles its own exceptions and errors).
        Disable full_stack when this application is "managed" by
        another WSGI middleware.

    ``app_conf``
        The application's local configuration. Normally specified in the
        [app:] section of the Paste ini file (where
        defaults to main).
	"""
	# Configure the Pylons environment
	load_environment(global_conf, app_conf)

	# The Pylons WSGI app
	app = PylonsApp()

	# CUSTOM MIDDLEWARE HERE (filtered by error handling middlewares)

 	if asbool(full_stack):
		# Handle Python exceptions
 		app = ErrorHandler(app, global_conf, error_template=error_template,
			**config['pylons.errorware'])

        # Display error documents for 401, 403, 404 status codes (and
        # 500 when debug is disabled)
        app = ErrorDocuments(app, global_conf, mapper=error_mapper, **app_conf)

 	# Establish the Registry for this application
	app = RegistryManager(app)

    # Static files
	javascripts_app = StaticJavascripts()
	static_app = StaticURLParser(config['pylons.paths']['static_files'])
	app = Cascade([static_app, javascripts_app, app])

	# copy and pasted from repoze.who readme
	io = StringIO.StringIO()
	salt = 'aa'
	for name, password in [ ('admin', 'nimd'), ('nym', 'myn') ]:
	    io.write('%s:%s\n' % (name, password))
	io.seek(0)

	htpasswd = HTPasswdPlugin(io, cleartext_check)
	auth_tkt = AuthTktCookiePlugin('secret', 'auth_tkt')
	form = FormPlugin('__do_login', rememberer_name='auth_tkt')

	identifiers = [('form', form),('auth_tkt',auth_tkt)]
	authenticators = [('htpasswd', htpasswd)]
	challengers = [('form',form)]
	mdproviders = []

	log_stream = None
	import os
	if os.environ.get('WHO_LOG'):
	    log_stream = sys.stdout

	middleware = PluggableAuthenticationMiddleware(
		app,
		identifiers,
		authenticators,
	    challengers,
	    mdproviders,
	    default_request_classifier,
	    default_challenge_decider,
	    log_stream = log_stream,
	    log_level = logging.DEBUG
	)

	return middleware
    #return app

So, now you have middleware working! The only thing is, nothing's different, at least nothing seems different. No pages are locked down, nobody can log in yet, but you have set up the basis for the most simple form of authentication with Repoze.who - that is HTPasswdPlugin.

Lets take a deeper look at what's going on:

	io = StringIO.StringIO()
	salt = 'aa'
	for name, password in [ ('admin', 'nimda'), ('nym', 'myn') ]:
	    io.write('%s:%s\n' % (name, password))
	io.seek(0)

	htpasswd = HTPasswdPlugin(io, cleartext_check)

This sets up the HTPasswdPlugin with what looks appears to python as a regular htpasswd file. If you wanted to replace the StringIO with a open("htaccess","r"), you could. In later tutorials I'll show how to replace this completely with a user model to avoid having to use this incredibly underpowered setup. The reason I start off with an HTPasswdPlugin is because it's the simplest thing to understand, and easiest way to get actual authentication working with Repoze.who.

After htpasswd is defined as an HTPasswdPlugin, we do a few things:

Define the AuthTktCookiePlugin and the FormPlugin:

	auth_tkt = AuthTktCookiePlugin('secret', 'auth_tkt')
	form = FormPlugin('__do_login', rememberer_name='auth_tkt')

AuthTktCookiePlugin is an IIdentifier plugin that allows for identities to be stored as client-side cookies. This is great because as it turns out, your users probably won't want to be asked to input their credentials every time they visit a page on your website. The first parameter is the secret, which is used to encrypt the cookie on the client side, and decrypt it on the server side. The second parameter is the cookie name. I personally suggest you use the cookie name "mystical_monkey_powers". Additionally, if you don't add a third parameter, the cookie can be sent over HTTP connections, as well as HTTPS connections. If the third param is True, then the REMOTE_ADDR of the WSGI environment will be put inside the cookie. I just started off with the default for now.

FormPlugin is both an IIdentifier and IChallenger plugin, which intercepts POST requests. It's also what will display a login form later when a challenge is required. The Repoze.who README says that the first parameter is "a query string name used to denote that a form POST is destined for the form plugin". For this example, we use '__do_login', which is unique. The name '__do_login' doesn't mean anything, it's just unique enough not to cause any conflicts. The second argument, remember_name='auth_tkt' is the "configuration name" of our other IIdentifier plugin that deals with the "remember and forget" responsibilities. In our case, that plugin is "auth_tkt", which is our AuthTktCookiePlugin. See how it all fits together?

After that, there are three statements that define identifiers, authenticators, and challenges.

	identifiers = [('form', form),('auth_tkt',auth_tkt)]
	authenticators = [('htpasswd', htpasswd)]
	challengers = [('form',form)]

These are passed later to the middleware constructor, and are respectively, the implementations we are going to use for each of the IIdentifier, IAuthenticator, and IChallenger types in the middleware setup.

After that, the rest just configures the middleware to do things like logging and the classifier and decider implementations. The Repoze.who README says that these are stock, so I just left them alone.

Good job so far! We're almost done with our first authentication setup...

Creating the Password Protected Controller

Next, you want to create a controller to test out your authentication setup. Run the following from your top project directory:

paster controller seekret

Of course, this creates a controller called "seekret". Very black-ops sounding, I know.

Inside of that controller, right before it returns anything, put the following conditional:

		if request.environ.get('repoze.who.identity') == None:
			abort(401)

Give it a go! See the form that wasn't there before? If you use one of the logins you previously defined in middleware.py, you'll see "Hello World", if not, you'll get the form again. Pretty cool, huh?

So how does it work?

Now when you go to your controller, if the user hasn't been previously identified, it will abort with a 401 (Unauthorized) response.

After that, the seekret controller will evaluate request.environ.get('repoze.who.identity'). Since this will be the first time we hit that page, it will spit back None. The if statement will trigger, creating an Unauthorized response. That will translate within the framework to a "request classification: browser" to repoze.who. The identifier plugins, FormPlugin and AuthTktCookiePlugin, will then go to work, and find nothing (no form response, no cookie response). Everybody says "We don't know this guy", don't let him into the secret club. Nobody knows who you are and repoze.who will say "no identities found, not authenticating". Luckily it doesn't end there.

Once everyone is certain they don't know you, they'll give you a challenge. We registered the FormPlugin as the challenger, and repoze.who will present the FormPlugin as the challenger, presented to you the user as a Username and Password form.

If you answer correctly, you'll be let in. If not, you'll continue to get the FormPlugin. Actually what happens when you answer correctly is that the FormPlugin will return an identity, the authenticator, HTPasswdPlugin will make a match to what it has on file, and instead it's more correct to say that repoze.who will redirect to the page and this time allow access, which means you'll get to see those two lovely words- "Hello World".

Next time you hit the page, no challenge will be presented to you, the user, because it happens behind the scenes. The FormPlugin set the auth_tkt (sorry, mysticalmonkeypowers) cookie, and the app will let you in since you are already got in once. It's like having a stamp on your hand, and being let back into the club. Wash that stamp off though, and the bouncer will ask who you are again. The same goes for your app, if you delete the cookie that was created, you'll be back to square one.

Want to see for yourself? Shut down your app, and type the following into your terminal:

export WHO_LOG=1

Then restart your app in the foreground. Now repoze.who will print out all it's logging statements to your terminal, and you can see for yourself what it's doing behind the scenes.

Stay tuned for Part 2, hooking up a SQLAlchemy based Model to Repoze.who!

Example Source: http://standundering.com/examples/authexample1.tgz