So I finally got some time on the internet (stupid ISP, bad luck etc..) and one of the tasks to do was to add a feed column to the website table in my fishing directory. Since I am using Sqlite 2.8 (?) no ALTER TABLE construct is available (added with Sqlite 3) and SQLObject doesn’t make it any easier.

I did the same change while changing the rest of the code for this (so it should be a quick task once I got online and had access to the updated database) but it was a little complicated. It involved running two simultaneous SQLObject databases with one containing the extra column for feed urls and writing a special function for copying between the databases. It was in fact so complicated that i didn’t remember exactly how i did it and SQLObject started complaining about classes that were already in the class registry. And that class was part of a module I wasn’t importing this time. (Insert extremely frustrated smiley here).

This lead me to explore how this could be done with Sqlite. The recipe is quite simple. The sqlite database is in the olddatabase.db file and I will create a file named newdatabase.db for the new database.


C:\>sqlite olddatabase.db
sqlite> .output sqlitedump.sql
sqlite> .dump
sqlite> .quit


Now I had a dump of the freshest database without the feed column in sqlitedump.sql. Adding the feed column was as easy as opening sqlitedump.sql and inserting a new column into the SQL table definition. The second step was to search-and-replace ); with ,”); inside the sql for inserting the websites to account for the extra column and


C:\>sqlite newdatabase.db < sqlitedump.sql


The only things left were to add the feed column to the SQLObjectified Website class definition and point the database connection to the new database. Very simple compared to the SQLObject approach I had used before.

I recently replaced the simple (PHP-based) backend for my Good Web Hosting Info site with something written in Python that at least deserves the CMS tag.

I decided to use CGI for this because it’s easiest to find web hosting for that. And if I want to start more websites I can just plug the CMS into a budget hosting account. If I later want to use a better framework (like CherryPy)I can easily port it because it is in fact structured like I have learned to structure CherryPy applications. The major difference is that I needed to write my own url-to-function code.

What it does

From the web interface I can add, edit and delete categories and articles, adjust the ordering of the categories and set a few configuration options, including page caching. The CMS also produces RSS feeds for all categories and writes them to static files.

How I built it

I started with a template manager and a view class that I have developed for CherryPy applications but they can just as well be used with any other framework. These two classes take all the stress out of building the interface and all that is left is to place the templates in the correct files. They are adapted to Cheetah but they can easily be modified to use most other template modules. I got a page up with PXTL within a few hours. Not bad considering that multiple templates are used for one page and this was my first time using PXTL.

With the view component figured out of the way I could dive straight into the database module. I started with the SnakeSQL pure-Python database and it worked well for what I told it to do even if it is not of production quality yet. What stopped me from progressing with SnakeSQL is that it can’t store longer strings than 256 characters.

Since hosting availability was an important factor the obvious database choice was MySQL. It doesn’t have all the features of Postgres and Firebird but this is a simple CMS with only two database tables so hosting availability is a far more important factor than stored procedures etc..

I have written my own module for form handling. It’s called hbform and represents each form with a Python class. It helps with validating user input, filling and manipulating forms, and generates xhtml code for the form controls. It can generate code for the whole form, or work together with a templating solution like Cheetah. I plan to release it some day…

CGI and efficiency

Besides the fact that the Python interpreter needs to be started for every request there are other factors that slow down CGI-based programs. First, each and every module needs to be reloaded. And Cheetah templates are compiled to Python classes so it’s smart to cache the compiled templates in a way. This turned out to be very hard with CGI.

With a persistent application my template manager stores the templates in an in-memory dictionary. The obvious CGI alternative was to pickle the dictionary with the templates but that didn’t work, I don’t remember the exact error but it was something with a function type used by Cheetah that is not picklable. Bummer!

Generating a page from scratch takes around 0.5 seconds and a little inaccurate profiling revealed that about 90% of this was used for template parsing. So I thought caching the templates was the key to speed. After struggling with this I managed to write the generated Python classes for the templates to the file structure and import them almost like regular Python modules. This resulted in a small speed increase (~0.1 second). I also tried to use non-compiled templates in the form of PXTL but that was even slower.

I was disappointed by this so I did more profiling and it turned out that ~0.1 second was originally used for template parsing and most of this was eliminated by template caching. But loading the Cheetah module took ~0.15 seconds. Strange, but I realized that caching the generated pages was a better approach. A few lines of code for pickling pages and the time to deliver a cached page is ~0.01 second. Problem solved :-)

Hosting environment compatibility

Good web Hosting Info is hosted by Site5 and they have Python 2.2.2 and the MySQLdb module installed. I run 2.4.1 at home so a few compatibility problems were to be expected. I experienced 3, involving the DateTime module that was introduced with 2.3, MySQLdb’s executemany function and a warning about different versions of the C interface used for the NameMapper module in Cheetah. Could have been worse.

I did a user-level install of Cheetah. Maybe Site5 would have installed it for me but I didn’t ask. I just copied the src directory of the Cheetah download and placed it in the base directory for my CMS. I removed namemapper.pyd (C code) to avoid warnings in the error log about different versions of the C interface. There is a fallback for the C component written in Python so this only leads to a little less speed. This installation procedure is not described in the Cheetah documentation but it has worked so far.

Conclusion

With the help of some decent modules, developing my own simplistic, efficient and usable CMS in Python, suitable for a low-cost hosting environment wasn’t too hard. I get exactly the functionality I want and if I need something else it’s easy to add since I know the code in and out. It’s also pleasing to run my own website on my own CMS.

Even if the Python support in budget hosting accounts normally is far from ideal it’s often sufficient for typical web applications. In my case, Python 2.2.2 and MySQLdb was all that was supplied from the web host but that is just what I needed. If you develop with a fresh Python version (2.4.1) the incompatibilities doesn’t have to be a problem.

Ps!

If you just want better Python support in a shared hosting environment, try a more specialized hosting provider like Python-hosting.com or GrokThis.net.

If you have the need to delete an object with SQLObject you will probably like to delete a many-to-many relation some time too. First, I have to establish the relation. It’s explained in the SQLObject documentation so no details on that in this blog post. But it doesn’t say anything about how you can delete this relation. After searching the SQLObject mailing list and Wiki I finally found a clue in the source code and it’s really simple.

I am working on a website directory so I’ll just create an SQLObjectified website and an SQLObjectified category to put the website in. Categories can have many websites and websites can be placed in many categories so obviously I need a many-to-many relation. items is the module where I keep my SQLObject classes.
website = items.Website(url="http://www. .......", more="params")
category = items.Category(title="Cool category",more="params")
website.addCategory(category)
This establishes a website, a category and a relation between them. So if I want to move the website from that category I need to delete the relation between them. The magic code is
website.removeCategory(category)
That’s inserting 2 objects into a database and then creating and deleting (or should I say removing?) a relation between them in 4 simple lines of code. SQLObject surely isn’t perfect and the documentation could have been more extensive but this example demonstrates that you can do things with SQLObject in a fraction of the lines you need if you’re working with SQL.

Update : The SQLObject documentation actually says that a removeCategory method is created for this, it’s just hidden away in the reference section, under RelatedJoin: Many-to-Many.

A while back I discovered that the SQLObject documentation didn’t say anything about deleting entrys from your database. And just now I saw that Michelle who is maintaining the PyWebOff Blog ran into the same problem. She found the solution but didn’t give it away, so here it is :
items.Category.delete(cid)
In this case, items is the module where I have collected all my SQLObject classes, Category is the name of an SQLObjectified class, cid is the id of an entry in the Category table and delete is the method that deletes the entry :-)

Merry christmas everyone!