Groovie

One of the advantages to using SQLObject is that the code you write in it can easily be constructed in a way that ensures it’ll work without a problem in all the databases SQLObject supports. This is a tremendous advantage that is most useful when writing web applications intended for wide-spread deployment on a variety of systems.

The most common SQL expressions you’ll likely want to use are Update, Select, and Delete. You can directly issue all of these in SQLObject using the connections sqlrepr method. The easiest way to see this is to play with it on the interactive prompt.

Before we begin, let’s setup a simple database layout using sqlite to try out the examples with:

from sqlobject import *
from sqlobject.sqlbuilder import *
from datetime import datetime
import sys, os

db_filename = os.path.abspath('data.db')
if os.path.exists(db_filename):
    os.unlink(db_filename)
connection = connectionForURI('sqlite:' + db_filename)
sqlhub.processConnection = connection

class Comments(SQLObject):
    name = StringCol(length=50)
    date = DateTimeCol(default=datetime.now)
    comment = StringCol()

Comments.createTable()

# Create some boring comments
Comments(name='fred', comment='Hello everyone')
Comments(name='joe', comment='Hi fred')
Comments(name='smith', comment='Good day')

SQLObject supplies a batch of classes for us that generate our database agnostic SQL expressions. While there isn’t too much documentation for using these classes (the mailing lists help), you can get a good idea where to start by looking at the help for them.

Using Select

If we want to take a look at the documentation for the Select class (which was imported above from sqlobject.sqlbuilder), we’ll get the following information:

Help on class Select in module sqlobject.sqlbuilder:

class Select(SQLExpression)
 |  Methods defined here:
 |  
 |  __init__(self, items, where=<class sqlobject.sqlbuilder.NoDefault>, 
groupBy=<class sqlobject.sqlbuilder.NoDefault>, 
having=<class sqlobject.sqlbuilder.NoDefault>, 
orderBy=<class sqlobject.sqlbuilder.NoDefault>, 
limit=<class sqlobject.sqlbuilder.NoDefault>)
 |  
 |  __sqlrepr__(self, db)

First, we need to pull the connection used for the class. We will then use the connections sqlrepr method to construct our SQL, and the connections query method to actually run it. Let’s take a look at getting all the names only from our Comments:

conn = Comments._connection
nameselect = conn.sqlrepr(Select(Comments.q.name))
results = conn.queryAll(nameselect)

# >>> results
# [('fred',), ('joe',), ('smith',)]

This will populate results with an array of tuples, one tuple for each result with the tuple values in the order you specified for the select (it’d be nice to have a way to get dicts instead…). Let’s take a look at a few more examples of using Select:

fields = [Comments.q.name, Comments.q.date]
namedateselect = conn.sqlrepr(Select(fields, where=(Comments.q.date < datetime.now() )))
results = conn.queryAll(namedateselect)

# >>> results
# [('fred', <DateTime object for '2005-11-01 11:14:39.00' at 843ea68>), 
# ('joe', <DateTime object for '2005-11-01 11:21:33.00' at 843ea30>), 
# ('smith', <DateTime object for '2005-11-01 11:21:33.00' at 826a3a0>)]

namedateselect = conn.sqlrepr(Select(fields, where=(Comments.q.date < datetime.now()), 
  limit=2, orderBy=Comments.q.date))
results = conn.queryAll(namedateselect)

# >>> results
# [('fred', <DateTime object for '2005-11-01 11:14:39.00' at 843ea30>), 
# ('smith', <DateTime object for '2005-11-01 11:21:33.00' at 826a3a0>)]

Joins, and additional fields can be specified using the normal Class.q notation to let SQLObject generate the proper SQL necessary in the same manner as the documentation explains.

Updating Fields

Doing a large update of a sub-set of fields is definitely something best left to a manual Update command. First, let’s take a look at what the help for the Update indicates:

Help on class Update in module sqlobject.sqlbuilder:

class Update(SQLExpression)
 |  Methods defined here:
 |  
 |  __init__(self, table, values, template=<class sqlobject.sqlbuilder.NoDefault>, 
where=<class sqlobject.sqlbuilder.NoDefault>)
 |  
 |  __sqlrepr__(self, db)

I’ll admit right now I’m not actually sure what template is for, nor have I used that keyword argument. If someone would like to chime in on the comments, that’d be appreciated greatly.

Updating the table gets a little tricky since we need to specify all of the fields in a database agnostic manner. To avoid very long statements, I’ve broken it down into sections to build the query.

Let’s look at updating all the dates of our Comments table:

datecol = Comments.q.date.fieldName
updatedates = conn.sqlrepr(Update(Comments.q, {datecol:datetime.now()}))
conn.debug = True    # So we can see the query execute
conn.query(updatedates)
conn.cache.clear()

# >>> conn.query(updatedates)
#  1/Query   :  UPDATE comments SET date='2005-11-01 11:57:31'
#  1/QueryR  :  UPDATE comments SET date='2005-11-01 11:57:31'
#  1/COMMIT  :  auto

Updating multiple fields is just as easy, merely add more key/val’s to the dict you pass in for the values variable. To update values using the original value of the field in the update, ie, adding something to the existing field we specify that using the Class.q.field format used in where clauses. Also, note that we need to clear the object cache after running the update so that SQLObject fetches the row again before using it.

updatedates = conn.sqlrepr(Update(Comments.q, {datecol:Comments.q.date + 20}))
conn.query(updatedates)
conn.cache.clear()

# >>> conn.query(updatedates)
#  1/Query   :  UPDATE comments SET date=(comments.date + 20)
#  1/QueryR  :  UPDATE comments SET date=(comments.date + 20)
#  1/COMMIT  :  auto

This adds 20 seconds to the existing dates for all the rows. Using the .q notation with the class is necessary for the key value because we need to ensures that Python doesn’t try to add 20 to a string which is what it would’ve tried if we had said {datecol:'date' + 20}.

Deleting

Issuing a Delete on the database is very similar to the update command, the class help looks like this:

class Delete(SQLExpression)
 |  To be safe, this will signal an error if there is no where clause,
 |  unless you pass in where=None to the constructor.
 |  
 |  Methods defined here:
 |  
 |  __init__(self, table, where=<class sqlobject.sqlbuilder.NoDefault>)

By now, the signature for the init method should be fairly familiar as well as what input’s its expecting. Here’s a quick example:

delquery = conn.sqlrepr(Delete(Comments.q, where=(Comments.q.name == 'smith')))
conn.query(delquery)

# >>> conn.query(delquery) 
#  2/Query   :  DELETE FROM comments WHERE (comments.name = 'smith')
#  2/QueryR  :  DELETE FROM comments WHERE (comments.name = 'smith')
#  2/COMMIT  :  auto

That’s pretty much all there is to deleting, probably the easiest operation to do with a SQLExpression class.

Transactions, Notes, and Gotchas

When using these techniques in large programs, it can be tricky to ensure that the object cache is cleared out and up to date. If you’re going to use a lot of these commands extensively it might be prudent to turn cacheValues off, or wrap the commands in a function that calls the cache.clear() command.

Using transactions (not all databases support it) can still be done if you want to wrap a batch of these manual expressions into a single transaction. You just need to start the transaction and commit it when done:

trans = conn.transaction()
delquery = conn.sqlrepr(Delete(Comments.q, where=(Comments.q.name == 'smith')))
conn.query(delquery)
updatedates = conn.sqlrepr(Update(Comments.q, {datecol:Comments.q.date + 20}))
conn.query(updatedates)
trans.commit()

If you’d like to use database functions (bottom of the SQLBuilder docs), this is easy to pass in as well but since they’re more database specific you begin to lose portability.

Please feel free to contribute any experiences or further examples of working with SQLObject expressions in a manual fashion as I’ve described here.

Ruby is a fairly interesting programming language, from the “expressive” syntax to some of the absolutely bizarre documentation. For a Python programmer, the lack of predictability and almost excessively concise syntax (when just one more line would really make things a lot clearer) can be a bit of a downer. Overall though, I’m rather enjoying my experiences with Ruby but not enough that I’d want to use it exclusively.

The “Wacky” bit I cite, comes from some of the strange directions the language seems to go and wacky documentation and books available. Now, the creator of Ruby is completely aware about some of the ways in which Ruby sucks as he put it, and 3 of those are of particular importance to Python programmers since we enjoy a concise (and not complex), predictable, and consistent language. Ruby is working to address these issues in the upcoming 2.0 release which I’m hoping will make it more pleasant to work with.

It is also somewhat wacky the way the Ruby community has compared themselves to other programming language communities to which this blogger has a fairly friendly reply. Sometimes these little jabs run into the printed documentation regarding Ruby as well, which is a big turn-off for me, especially since I really disagree with the reasons that are cited for insulting other languages.

Beyond Java?

Take the Beyond Java book for example. About half-way in it becomes very clear that the author’s view of what is Beyond Java…. is Ruby, and for web programming, Ruby on Rails. The author then goes on to smash Perl, Python, and PHP (ok, I agree with him on PHP :). He puts Ruby in the mix as well, but the only bad thing he says about it is that its lacking commercial backing (except for later on where a new reason emerges).

His comments regarding Python are truly wacky though, as he jumps back to the very old “white-space reliance sucks” argument that hardly ever comes up in the real world. I’ve been using Python for over a year now, and have worked on quite a few collaborative projects, and have yet to see a single error related to mismatched white-space. This is probably because coding standards are well known and Python programmers actually follow them for the most part. It’s hard to express how wonderful this has made it when I’ve jumped into code written by other people, and have been able to easily scan it and add functionality after just a few minutes of looking it over.

When I’ve jumped into other people’s Ruby code looking to make a quick-fix, the syntax quickly became a massive chore to decipher as Perl’s motto of TMTOWTDI holds very true in Ruby as well.

The Beyond Java book also cites Python’s lack of a “killer app” when it comes to web programming and specifically references Ian Bicking’s article on web programming frameworks. Of course, since that article Ian has put out Python Paste which solves a host of problems he mentioned there, and the Python web community’s move to WSGI is helping to standardize methods of running Python web applications.

Even more wacky, later in the Beyond Java book, in yet another comparison of whats for and against the languages, a different reason pops up for Ruby not doing so good. What is it this time?

The biggest strike against Ruby right now is the lack of a strong project that lets Ruby run on the JVM. – Page 163

He goes on to cite how much support Ruby would get if Microsoft was able to woo the Ruby founders over to .NET’s CLR. On the very next page (165) another for and against argument comes out for Python. Since the author just mentioned the JVM and .Net CLR as major drawbacks to Ruby, I was actually expecting him to mention Jython, or even IronPython which is actually being developed by a programmer now at Microsoft. Amazingly enough, neither of these projects is mentioned here, though Jython was mentioned back near the beginning as being too slow (which IronPython apparently solves).

It gets even more wacky a few more pages in, as his reasons against Perl come out.

Perl does have a downside. When you look at overall productivity of a language, you’ve also got to take things like maintenance and readability into account. Perl tends to rate very poorly among experts on a readability scale. - Page 174

Who these experts are is never mentioned, and I’ve seen “experts” for and against the Perl syntax. While Ruby is easier to read than Perl in my personal opinion, its nowhere near as easy to read as Python.

Programming Ruby, The Pragmatic Programmers Guide

I’ve been reading this book for awhile now, and the author’s decision to do something different is admirable but has really been a bane to reading the book. I’ve also read the Agile Web Development with Rails book, which I think was done in a most excellent manner (written by Dave Thomas, the author of Programming Ruby). So this isn’t an attack on Dave, as I really enjoy his writing, I just believe this approach didn’t work so well.

The approach taken in Programming Ruby is to breeze over high-level uses of the language without actually explaining much about why things acted as they do. This quickly drove me nuts, and I stopped reading the entire intro as seeing syntax for no reason wasn’t helping me learn anything. If you want to learn Ruby in the way most programming books teach a language, by carefully and completely going over all the parts then doing more advanced things; skip to page 317 in the Ruby Crystallized Part.

Once I started reading here, everything fell into place very nicely and the language really started to make sense. If the sections were reversed as most programming books have it, I’d consider this book pretty much perfect for a programming book. The thing I think Dave might have missed here, is that most programming books follow this convention because it works. Being different, just to be different, isn’t very good unless there’s a real and practical reason for doing it.

The wackiness doesn’t end though, and I have yet to complete the entire book so I can’t say how many more examples of this are there. Here’s the latest gem though, which actually prompted this entry (though I’ve been thinking these thoughts for awhile).

On Page 330, going over the details of Variables and Constants, I came across this:

Ruby, unlike less flexible languages, lets you alter the value of a constant, although this will generate a warning message.

HUH? Errr, then why the heck do they call it a Constant?? Seriously, maybe its because I’m picky on language terms used but I think they should’ve called it a semi-Constant, or a mostly-Constant Constant. For me, this goes against the entire notion of what a Constant is. Then, on top of that, it insults other “less flexible languages” that (gasp) don’t let you change constants.

It’s a Wacky World

There’s many more examples of the wackiness present in the Ruby world. Perhaps its because the language is from Japan, home to so many wacky things by Western standards. Though the writers I mentioned are all non-Japanese, so this explanation doesn’t really cut it. To be fair, the Beyond Java book is well written and the reasons cited in many of the comparisons are valid to an extent.

Ruby in Rails also has its share of wackiness, though it seems so abundant I’ll have to save that for another post entirely. If you’re wondering after all this, why I’m still using Ruby… well, it’s a wacky world, and I do kind of like wacky (I think I’ve used up all allowed uses of the word ‘wacky’ by now). Ruby 2.0 looks to be quite appealing and it’ll be interesting to see how Rails adapts to so many breakage’s that 2.0 appears to introduce over 1.8.

Python isn’t perfect either, and I’m not going to claim it is. There’s plenty of people in both the Python world and the Ruby world who are very forthcoming about failures and successes of the language, so the views expressed by the authors in these books should not be taken to represent the whole. They are some of the most visible speakers though, so I hope that they can someday be as forthcoming as Matz has been.

TextMate is a rather slick little text editor, with a bunch of cool automation stuff thats great for programmers. You’ve probably already seen it if you’ve watched any of those demo movies for some of the latest web frameworks.

Anyways, as I mainly use Myghty, I needed a syntax highlighting mode for its format. So I made a Myghty bundle for TextMate. This should also be useful for those using Mason as only a few minor adjustments regarding the underlying language highlighter need to be tweaked (Change source.python -> source.perl). This also has a few automation snippets to speed things up.

To install:

• Unzip
• Drag into ~/Library/Application Support/TextMate/Bundles/
• Restart TextMate (Not absolutely sure this is needed though)

Enjoy!

I’ve almost got a 1.0 ready of Routes. After reading Kevin Dangoor’s post on the mysterious 1.0 I’ve come to the conclusion that Routes is 1.0 ready. In case you aren’t familiar with Routes, I’d suggest taking a look at one of my earlier posts about it.

So where is it? It’s in the latest svn for now, because I’d like to actually have a nicer site with more full fledged documentation before the release. For example, some docs on how framework creators should go about integrating it, as well as more detailed and thorough documentation on usage. I also have to finish up one little change that will make Routes fully compatible with the WSGI spec when the path/script info is split-up (which happens when a WSGI app is put under a URL-space).

Rails routes suffers from a lack of documentation as well, and the most complete source of information is buried in the test units for it. There’s also good docs in the Agile Web Dev for Rails book, but that’s not exactly a free public resource. So I’ll likely be “porting” my docs back to Rails, especially since Nicholas Seckar has been a great help while working on this project.

Whats New?

Short answer, not a lot. The main thing was actually very trivial to implement, named routes. These act essentially as a short-cut for when you want to pull some possibly long pre-defined route defaults. Short-cuts are good of course, as they save you a bit of typing and in this case also make your URL’s more flexible should you decide to change how to get to a “named” route.

Here’s what a fairly basic Route setup looks like:

m.connect(':controller/:action/:id')
m.connect('', controller='home', action='splash')

To implement Named Routes I added the ability to specify an additional string before the keywords. Here’s an example:

m.connect(':controller/:action/:id')
m.connect('home', '', controller='home',action='splash')

Now take a look at using it inside a template:

# Without named routes
url_for(controller='home',action='splash')
url_for(controller='home',action='splash', id=4)

# With named routes
url_for('home')
url_for('home', id=4)

This is slightly different from the Rails approach as I was mainly interested in keeping it “Pythonic”. So there’s no extra symbols or functions created when using a named route.

As you can see, it can save a bit of typing as using a named route is sort of like having a set of keywords inserted for you.

A 1.0 Release

Kevin’s article gave me a lot of food for thought regarding whether I should keep incrementing the Routes version. I can’t see any reason not to just go 1.0 as my version of Routes will be feature equivalent (and then some) with the Rails version, is heavily unit-tested, and is used in a production environment.

I’m confident in the reliability of the code and its being used by quite a few people in production environments (myself included). Being 1.0 doesn’t mean its done, it just means its hit a point functionality wise where I’ve accomplished everything I wanted the “finished” product to have. Even 1.0 software has bugs, and when I think of more features I’d like to add, I’ll start on my way to 1.1 or maybe even 2.0.

Hopefully I’ll have the docs ready to go sometime before November at which point a fully redesigned web-page will be put up. Until then, if any of this has you interested, head over to the Routes site or take a look at the unit tests to get a feel for using it. If you want to see it in action, try out the latest version of Myghty which contains an easy-to-use project template using Routes integration (currently with the 0.2 Routes which is lacking Named Routes).

I’ve been programming web sites for many years, and have yet to come across a templating language as appealing as Mason / Myghty. To avoid confusion I’m going to talk about Myghty, but since its a direct port of Mason (plus some MVC stuff) all of my comments apply to Mason as well (unless otherwise noted). So if you find yourself stuck using Perl (or you prefer Perl) and something here sounds appealing, by all means use Mason as I did.

Despite Myghty only having come into existence approximately 14 months ago, the code-base is stable, very quick, and has been running in production environments for over 8 months. This is mainly because it started as a very direct port of Mason to Python, it then grew a few additional features that made it great for MVC use. The methodologies present in Mason (thus Myghty as well) are known to scale to very large and complex sites, as this list of Mason-powered sites shows. But it’s the little things added up that really make Myghty my template language of choice.

This is a rather lengthy post as I highlight and explain some core concepts of Myghty, please bear with me… also, if you’d like to follow along and try the examples out, its really easy to get started using Myghty with Paste.