techspot.zzzeek.org

A few people have been asking about this one as of late, it's quite easy to do with a ConnectionProxy. Here's a quick recipe (yes, this is 0.5):

from sqlalchemy.interfaces import ConnectionProxy
import time
import logging
 
logging.basicConfig()
logger = logging.getLogger("myapp.sqltime")
logger.setLevel(logging.DEBUG)
 
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
            logger.debug("Query: %s" % statement)
            logger.debug("Total Time: %f" % total)
 
if __name__ == '__main__':
    from sqlalchemy import *
 
    engine= create_engine('sqlite://', proxy=TimerProxy())
 
    m1 = MetaData(engine)
    t1 = Table("sometable", m1, 
            Column("id", Integer, primary_key=True),
            Column("data", String(255), nullable=False),
        )
 
    conn = engine.connect()
    m1.create_all(conn)
 
    conn.execute(
        t1.insert(), 
        [{"data":"entry %d" % x} for x in xrange(100000)]
    )
 
    conn.execute(
        t1.select().where(t1.c.data.between("entry 25", "entry 7800")).order_by(desc(t1.c.data))
    )

Output:

DEBUG:myapp.sqltime:Query: PRAGMA table_info("sometable")
DEBUG:myapp.sqltime:Total Time: 0.000233
DEBUG:myapp.sqltime:Query: 
CREATE TABLE sometable (
    id INTEGER NOT NULL, 
    data VARCHAR(255) NOT NULL, 
    PRIMARY KEY (id)
)
 
 
DEBUG:myapp.sqltime:Total Time: 0.000397
DEBUG:myapp.sqltime:Query: INSERT INTO sometable (data) VALUES (?)
DEBUG:myapp.sqltime:Total Time: 1.147780
DEBUG:myapp.sqltime:Query: SELECT sometable.id, sometable.data 
FROM sometable 
WHERE sometable.data BETWEEN ? AND ? ORDER BY sometable.data DESC
DEBUG:myapp.sqltime:Total Time: 1.064579

ConnectionProxy features two methods that can be overridden, execute() and cursor_execute(). The difference is that the former hooks onto the overall execute() method of Connection and is given the application level arguments, such as the ClauseElement and parameter dictionary, whereas the latter is called at the lower level where we have a string SQL statement and bind parameters compiled and formatted against the specific DBAPI in use.

Ticket 798, allow conjunctions to act as column elements, is complete after a long wait, finally attended to for the great reason that I suddenly needed this feature myself ;). A few tweaks and we can now talk about asking yes/no questions of our database.

As we head into the 0.5 era of SQLAlchemy, one theme is that if we're using the ORM, we're going to need select() constructs a lot less, if at all. Most things can now be done using Query objects. The other day, I needed to ask my database a question regarding if some particular data were available, and that's it. The query needed to be as fast as possible so I wanted to use EXISTS, so that the database needs to only access the first row of the selected rows in order to give an answer. Additionally, I needed to ask this question of a few different sets of criterion, which could be most efficiently achieved by combining them together into a single statement using OR.

Using the current trunk of SQLAlchemy 0.5, we can use Query() against boolean values:

from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))
 
true, false = literal(True), literal(False)
 
(ret, ), = Session.query(true)
print ret

which returns:

True

The question we just asked our SQLite database is SELECT 1, where "1" is SQLite's way of representing true (in Postgres it's true, in MySQL it can be 1 or true. Using literal(True) means we don't have to worry about this). SQLite tells us, "yes, True is True". Note the way I'm getting the row out of Query. At the moment that seems to be a fun way to go, but you could also say:

ret, = Session.query(true).one()

and of course:

ret = Session.query(true).one()[0]

We can also ask our database about boolean values combined with OR and AND:

>>> (ret, ), = Session.query(or_(true, false))
>>> ret
True
 
>>> (ret, ), = Session.query(and_(true, false))
>>> ret
False

To demonstrate EXISTS, let's build a table. We're using declarative, which is pretty much the only way I roll these days (hey, better late than never....):

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Keyword(Base):
    __tablename__ = "keyword"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
 
Base.metadata.create_all(engine)

One way we could find out if our Keyword table has any of a certain set of keywords, is to do something familiar and ask for a count:

keywords = ["beans", "lentils", "legumes"]
(ret, ), = Session.query(func.count(Keyword.id)).filter(Keyword.name.in_(keywords))

But to eliminate the need for the database to actually count the full set of rows, we can ask it just if any rows at all exist, using the exists() construct:

(ret, ), = Session.query(exists().where(Keyword.name.in_(keywords)))

Which issues the SQL:

SELECT EXISTS (SELECT * FROM keyword WHERE keyword.name IN (?, ?, ?)) AS anon_1

The return value is boolean, indicating True for rows were found, False for no rows were found. In my case, I was querying among a bunch of tables. Let's suppose we have a database which represents cookbooks and individual recipes, both of which have lists of keywords associated. Let's declare that up:

recipe_keywords = Table("recipe_keyword", Base.metadata, 
                    Column("recipe_id", Integer, ForeignKey("recipe.id")),
                    Column("keyword_id", Integer, ForeignKey("keyword.id"))
                    )
 
cookbook_keywords = Table("cookbook_keyword", Base.metadata, 
                    Column("cookbook_id", Integer, ForeignKey("cookbook.id")),
                    Column("keyword_id", Integer, ForeignKey("keyword.id"))
                    )
 
class Recipe(Base):
    __tablename__ = "recipe"
    id = Column(Integer, primary_key=True)
    description = Column(Text)
    keywords = relation(Keyword, secondary=recipe_keywords)
 
class Cookbook(Base):
    __tablename__ = "cookbook"
    id = Column(Integer, primary_key=True)
    description = Column(Text)
    keywords = relation(Keyword, secondary=cookbook_keywords)
 
Base.metadata.create_all(engine)

The above schema defines a recipe and a cookbook table, each of which relate to keyword via the recipe_keyword or cookbook_keyword association tables, respectively.

The question of "Do any recipes feature any of our above three keywords?" can be answered by:

(ret, ), = Session.query(
    exists().where(Recipe.id==recipe_keywords.c.recipe_id).
            where(recipe_keywords.c.keyword_id==Keyword.id).
            where(Keyword.name.in_(keywords))
)

Above we ask "do any rows exist, where the ID of the recipe table matches the recipe ID of the recipe_keywords table, and the keyword ID of the recipe_keywords table matches the ID of a keyword table row, and the name of the keyword is in the list ["beans", "lentils", "legumes"]". SQL is:

SELECT EXISTS (SELECT * 
FROM recipe, recipe_keyword, keyword 
WHERE (recipe.id = recipe_keyword.recipe_id AND recipe_keyword.keyword_id = keyword.id) 
AND keyword.name IN (?, ?, ?)) AS anon_1

Spelling out the full join from Recipe to Keyword above is a little bit verbose. We have the option to let SQLAlchemy create this for us using the Recipe.keywords relation already set up, using the ORM level join() function, which provides direct access to the ORM's join algorithm:

from sqlalchemy.orm import join
(ret, ), = Session.query(
        exists().select_from(join(Recipe, Keyword, Recipe.keywords)).
                where(Keyword.name.in_(keywords))
)

This generates:

SELECT EXISTS (SELECT * 
FROM recipe JOIN recipe_keyword AS recipe_keyword_1 ON 
recipe.id = recipe_keyword_1.recipe_id JOIN keyword ON 
keyword.id = recipe_keyword_1.keyword_id 
WHERE keyword.name IN (?, ?, ?)) AS anon_1

We now know how to ask the database if it has any recipe rows which relate to a given set of keyword names. To ask the database if it has any cookbook rows or recipe rows matching our keywords at the same time, we can double up on exists() clauses using or_():

(ret, ), = Session.query(or_(
        exists().select_from(join(Recipe, Keyword, Recipe.keywords)).
                where(Keyword.name.in_(keywords)),
 
        exists().select_from(join(Cookbook, Keyword, Cookbook.keywords)).
            where(Keyword.name.in_(keywords))
))

At this point, our eyes begin to glaze over when viewing the SQL itself. But that's fine; just remember that whenever this happens, somewhere in the world another "ORM's aren't worth it" blog post fades away:

SELECT ((EXISTS (SELECT * 
FROM recipe JOIN recipe_keyword AS recipe_keyword_1 ON recipe.id = recipe_keyword_1.recipe_id  
JOIN keyword ON keyword.id = recipe_keyword_1.keyword_id 
WHERE keyword.name IN (?, ?, ?))) OR (EXISTS (SELECT * 
FROM cookbook JOIN cookbook_keyword AS cookbook_keyword_1 ON 
cookbook.id = cookbook_keyword_1.cookbook_id JOIN keyword ON 
keyword.id = cookbook_keyword_1.keyword_id 
WHERE keyword.name IN (?, ?, ?)))) AS anon_1

Those readers who are familiar with some of SQLA's advanced Query operators might recognize that the above EXISTS queries look a lot like an any() expression. This is because, they are! Though in this case, almost. As a review, an any() expression creates an EXISTS clause which is correlated with the enclosing query, such as:

rows = Session.query(Recipe).filter(Recipe.keywords.any(Keyword.name.in_(keywords))).all()

This query produces a regular SELECT from the recipe table, then embeds a correlated EXISTS inside the WHERE clause:

SELECT recipe.id AS recipe_id, recipe.description AS recipe_description 
FROM recipe 
WHERE EXISTS (SELECT 1 
FROM recipe_keyword, keyword 
WHERE (recipe.id = recipe_keyword.recipe_id AND keyword.id = recipe_keyword.keyword_id) AND
keyword.name IN (?, ?, ?))

When any() is used, it explicitly states that the exists() clause should correlate() to the recipe table, which allows it to work in scenarios where SQLAlchemy's usual "auto" correlation cannot make the right decision (we have tests which illustrate this). So to use any() in our "column-based" approach, we just need to turn off that correlation using correlate(None). Our "recipes/cookbooks which exist" query can be stated as:

(ret, ), = Session.query(or_(
    Recipe.keywords.any(Keyword.name.in_(keywords)).correlate(None),
    Cookbook.keywords.any(Keyword.name.in_(keywords)).correlate(None)
    ))

While the any() approach above is nice, the correlation part of it has me preferring the more explicit exists() version.

My previous post (updated!) demonstrated how Mako's "defs with embedded content" feature was used to build a library of form tags, keeping all HTML and layout within templates, as well as a succinct method of linking them to form validation and data state. The "def with embedded content", a feature derived from HTML::Mason, is one feature that makes Mako highly unique in the Python world. The form demo also illustrated another unique feature, which is the ability to "export" the functionality of a def (essentially a subcomponent of a page) to other templates, without any dependency on inheritance or other structural relationship. Defs with embeddable content and exportable defs are two features I would never want to do without, which is why I ported HTML::Mason to Myghty, and later created Mako for Python.

A lesser known capability of the def is that they can be called not just by other templates but by any arbitrary caller, such as a controller. As it turns out, this capability is ideal in conjunction with asynchronous requests as well, a use case that didn't even exist when HTML::Mason was first created. Here I'll demonstrate my favorite way to do Ajax with Pylons and the unbelievably excellent jQuery. We'll introduce a new render() function that IMO should be part of Pylons, the same way as render_mako().

An asynchronous HTTP request is often used to render part of the page while leaving the rest unchanged, typically by taking the output of the HTTP request and rendering it into a DOM element. Jquery code such as the following can achieve this:

$("#some_element").load("/datafeed");

The above statement will render the output of the URI /datafeed into the DOM element with the id some_element. In Pylons, a controller and associated template would provide the output for the /datafeed URI, which would be HTML content forming a portion of the larger webpage.

In our example, we'll build a "pager" display which displays multiple pages of a document, one at a time, using the load() method to load new pages. One way we might do this looks like this:

<div id="display"></div>
<a href="javascript:showpage(1)">1</a> 
<a href="javascript:showpage(2)">2</a> 
<a href="javascript:showpage(3)">3</a> 
 
<script>
    function showpage(num) {
        $("#display").load("/page/read/" + num);
    }
    showpage(1);
</script>

Above, we define display, which is a div where the pages render. Some javascript code defines the showpage() function, which given a page number calls the jQuery load() function to load the content from the page-appropriate URI into the div. Three links to three different pages each link to showpage(), given different page numbers.

In this version, the /page/read controller would probably define a separate template of some kind in order to format a the data, so the layout of what goes inside of display is elsewhere. Additionally, the initial display of the full layout requires two HTTP requests, one to deliver the enclosing layout and another to load the formatted content within display.

When using Mako, we often want to group together related components of display within a single file. The <%def> tag makes this possible - a compound layout of small, highly interrelated components need not be spread across many files with small amounts of HTML in each; they can all be defined together, which can cut down on clutter and speed up development.

Such as, if we built the above display entirely without any asynchronous functionality, we might say:

<div id="display">
    ${showpage(c.page)}
</div>
<a href="/page/read/1">1</a>
<a href="/page/read/2">2</a>
<a href="/page/read/3">3</a>
 
<%def name="showpage(page)">
<div class="page">
    <div class="pagenum">Page: ${page.number}</div>
    <h3>${page.title}</h3>
 
    <pre>${page.content}</pre>
</div>
</%def>

The above approach again defines showpage(), but it's now a server-side Mako def, which receives a single Page object as the thing to be rendered. The output is first displayed using the Page object placed at c.page by the controller, and subsequent controller requests re-render the full layout with the appropriate Page represented.

The missing link here is to use both of the above approaches at the same time - render the first Page object into the div without using an asynchronous request, allow subsequent Page requests to be rendered via Ajax, and finally to have the whole layout defined in a single file. For that, we need a new Pylons render function, which looks like this:

def render_def(template_name, name, **kwargs):
    globs = pylons_globals()
 
    if kwargs:
        globs = globs.copy()
        globs.update(kwargs)
 
    template = globs['app_globals'].mako_lookup.get_template(template_name).get_def(name)
    return template.render(**globs)

The above render_def() function is adapted from the standard Pylons boilerplate for building render functions. It's virtually the same as render_mako() except we're calling the extra get_def() method from the Mako Template object, and we're also passing some **kwargs straight to the def in addition to the standard Pylons template globals. A refined approach might involve building a render_mako() function that has the functionality to render both full Template objects as well as individual <%def> objects based on arguments; but we'll keep them separate for now.

With render_def(), the Ajax version of our page now looks like:

<div id="display">
     ${showpage(c.page)}
</div>
<a href="javascript:showpage(1)">1</a> 
<a href="javascript:showpage(2)">2</a> 
<a href="javascript:showpage(3)">3</a> 
 
<script>
    function showpage(num) {
        $("#display").load("/page/read/" + num);
    }
</script>
 
<%def name="showpage(page)">
<div class="page">
    <div class="pagenum">Page: ${page.number}</div>
    <h3>${page.title}</h3>
 
    <pre>${page.content}</pre>
</div>
</%def>

Note above that there are two showpage functions; one is a Mako def, callable during the server's rendering of the template, the other a Javascript function which uses jQuery to issue a new request to load new content. The /page/read controller calls the showpage() def directly as its returned template. The net effect is that the server-side version of showpage() dual purposes itself in two different contexts; as a server-side component which participates in the composition of an enclosing template render, and as a "standalone" template which delivers new versions of its layout into the same overall display within its own HTTP request.

The controller, which locates Page objects using a simple SQLAlchemy model, in its entirety:

class PageController(BaseController):
    def index(self):
        c.number_of_pages = Session.query(Page).count()
        c.page = Session.query(Page).filter(Page.number==1).one()
        return render("/page.mako")
 
    def read(self, id):
        pagenum = int(id)
 
        page = Session.query(Page).filter(Page.number==pagenum).one()
        return render_def("/page.mako", "showpage", page=page)

I've packaged the whole thing as a demo application (using Pylons 0.9.7 and SQLAlchemy 0.5), which pages through a document we all should be well familiar with.

Update - The formhelpers demo is updated and is production ready. See the end of the post for details.

If you're a web developer in New York City, unless you work here, here or maybe here, you're probably not using Python for primary development (if you are, please post your company here). Since I'm not a PHP monkey or a Microsoftie, everywhere I work they're using Struts, which is where the dust has settled around Java Server Pages (with honorable mentions to Spring MVC and JSF).

I'm in a position where I might be able to push Python technology for a project or two, which have until now been built on Struts 1, the most common version of Struts even though Struts 2 is considerably nicer. So I wanted to see where Pylons is at with form rendering and processing these days. Most importantly, I wanted to ensure that layout is accomplished plainly within a template with no module-embedded HTML and no "magic generation" of forms from classes or other datastructures, and that the cycle of data from form to controller back to form again is similarly simple and obvious. Most projects in New York are the kind that get handed off to totally different people when complete, or even just 80% complete since you've been put onto something else, for remaining development and future enhancements. Code handoffs are extremely common, so it can't be overstated how much more important it is to be obvious than to be DRY. This is a big reason that tedious, plodding approaches like Struts and PHP are so popular - through raw verbosity they discourage opaque ways of doing things. Overly clever, data-driven rendering solutions that nobody can understand or extend (and are usually broken, anyway) are basically what gets thrown away and rewritten by the next team.

In developing Mako, a primary goal was to make a super-nice version of a particular "component" pattern which I had used for years primarily with HTML::Mason which for me provides a "sweet spot" of obviousness, agility, and succinctness. The focus is around the ability to create "tag libraries" which interact easily with a server-parsed templating language, and which can be implemented within templates themselves. In JSP development, taglibs are now the standard way to indicate dynamic areas of templates, but while they look pretty clean, they are painful to implement (requiring HTML embedded in hand-crafted classes, a few dozen XML pushups for every tag you add, and the obligatory application restart whenever they change), and the EL and OGNL expressions which are standard within taglibs interact terribly with straight Java code.

Mako allows the creation of tags which can be arbitrarily nested and interactive with one another via the <%def> construct, in combination with the <%call> tag. It's been my observation that the <%call> tag as well as the usage of nesting-aware <%defs> hasn't caught on yet, as the examples in the docs are a little dense, so here I will seek to demystify it a bit.

Pylons currently recommends a decent approach to rendering forms, using Form Tags which are essentially little functions you can embed in your template to render standard form elements. The handling of the form at the controller level uses FormEncode and routes validation errors through htmlfill. My approach modifies this to use Mako tags to build a site-specific taglib around the webhelpers tags and adds an explicit interaction between those tags and the controller, in a manner similar to a Struts form handler, which replaces htmlfill and allows all layout, including the layout of validation error messages, using the same template system. It also adds a preprocessor that illustrates how to build custom tags in Mako which look as nice as the built-in ones.

A tar.gz of the approach, which at this point should be regarded strictly as a proof of concept, can be downloaded here (works against Pylons 0.9.7), which contains three templates each illustrating a different approach to laying out the form. The three approaches are raw webhelpers with htmlfill, the <%call> tag approach, and finally using the "custom tag" approach. The final result, present in the file templates/mako_helpers.html, looks like this:

<%namespace name="form" file="/form_tags.mako"/>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Mako Form Helpers</title>
  <link rel="stylesheet" href="/style.css" type="text/css" />
</head>
<body>
 
<h3>Using Mako Helpers</h3>
 
<%form:form controller='comment' action='mako_post'>
<table>
    <tr>
        <th colspan="2">Submit your Comment</th>
    </tr>
    <tr>
        <td>Your Name:</td>
        <td><%form:text name="name"/></td>
    </tr>
 
    <tr>
        <td>How did you hear about this site ?</td>
        <td>
            <%form:select name="heard">
                <%form:option value="" selected="True">None</%form:option>
                % for desc, value in c.heard_choices:
                    <%form:option value="${value}">${desc}</%form:option>
                % endfor
            </%form:select>
        </td>
    </tr>
 
    <tr>
        <td>Comment:</td>
        <td><%form:textarea name="comment"/></td>
    </tr>
 
    <tr>
        <td colspan="2"><%form:submit/></td>
    </tr>
</table>
</%form:form>
 
</body>
</html>

Where of note are the Mako-like <%form:foo> tags that aren't part of Mako ! A short preprocessor is applied to the source file which turns a tag like <%form:foo> into <%call expr="foo()"> at template compile time. I.e., a tag used above as:

<%form:textarea name="comment"/>

is preprocessed into raw Mako code:

<%call expr="form.textarea(name='comment')"/>

The %call tag invokes the textarea def inside the form namespace, which is defined in the file form_tags.mako. The def for textarea looks like:

<%def name="textarea(name, default=None, **attrs)">\
<%doc>
    Render an HTML <textarea></textarea> tag pair with embedded content.
</%doc>\
${form_errors(name)}\
${h.textarea(name, content=request.params.get(name, default), **attrs)}\
</%def>

Above, the $form_errors(name) is a def call used for reporting validation messages. The point of form_tags.mako is that all the form tags and their layout is plainly visible and easily customized. Multiple versions of the file can be used in one application, providing different form layouts for different areas. The fact that it uses the h helper to render the actual HTML for each form control is also arbitrary; you could just as well implement the <textarea> source directly within the def if some special treatment were needed.

The demo also contains a modified version of Pylons' @validate decorator. Usage is mostly the same, except the form parameter is replaced by the more direct input_controller parameter, which is the method used for input:

from formhelpers.lib.mako_forms import validate as mako_validate
 
class CommentController(BaseController):
    def index(self):
        return render('/mako_helpers.html')
 
    @mako_validate(schema=CommentForm, input_controller=index)
    def mako_post(self):
        c.name = self.form_result['name']
        return render('/thanks.html')

Where above, mako_post hits the validator, and on an invalid exception the index controller method is called instead. Validation errors are placed in self.form_errors as well as c.form_errors for template access. The validator as well as the preprocessor are defined in lib/mako_forms.py.

That's pretty much all there is to it at this point, a few folks on #pylons seem enthused about it, so perhaps this can be turned into something more formally available and/or recommended when implementing a Pylons/Mako application.

UPDATE

I've refined the formhelpers demo with a @validate function tailored to a single usage pattern (download). Each form now has a name, which identifies its place on c, such as:

<%form:form name="comment_form" controller='comment' action='post'>

The controller now places a comment_form dictionary on c in all cases, which the @validate function takes care of on the post side:

@validate("comment_form", CommentForm, input_controller=index)
def post(self):
    c.name = self.form_result['name']
    return render('/thanks.html')

The hacky methodology of relating the <%form:option> tag to its parent <%form:select> tag has also been replaced with something reasonable.

This version of formhelpers is production ready.

As far as comments regarding @rest.dispatch_on and forcing a GET to accomodate it, I think that's kind of ugly since I don't really believe in "virtual requests", which is one reason I rewrote the decorator in the first place (i.e., to remove the "re-get" aspect of it). If you are dual-purposing your index() method to display the form on GET as well as to re-display on POST, then index() needs to accommodate both methods. Otherwise you can break index() out into index() and _display_index() and have _display_index() be your input_controller.

Django's already got one, and Brian Rosner made one for us too: link. SQLA's pre-release development is apparent here which is why it floats around "zzzeek" for so long.