If you ever make my mistakes!

Recently, I created a quick and dirty webapplication using Turbogears and SQLObject. During the initial phase, I had some problems with unicode strings, but figured out that I could use UnicodeCol in SQLObject, and append '?sqlobject_encoding=latin1' to the dburi setting for the turbogears project.

Later on, a new requirement for the project came up. Someone had to be able to download a file with infomation in an excel file. From before I had created excel files from mysql data in PHP and Perl using the SpreadSheet::Writer module (and the PHP equivalent). I just assumed this to be a walk in the park, recreating the same functionallity in my turbogears site. As you might expect (or even know), I was wrong. To this date, I can not find the same or a similar well-working module for Python. As an old (not that old really) Perl hacker, I have at least some amounts of the right lazyness. I can of course just reuse my old PHP solution, using some suitable rewrite rules in the apache2 config for the turbogears site. And this is where my problems begin. Getting things up and running is easy enough, we are only talking reading mysql data here, and figuring out the tables in mysql that the SQLObject based model is using is simple. But, and this was the hard part, I figured out that I had done something stupied. The mysql-tables is actually latin1, as I have told SQLObject in the dburi setting. But the texts inside these latin1 tables is encoded by Python in some unicode form, using latin1 characters. Which mean that when I read mysql data in PHP and feed into an excel file, I don't get the right thing. Using default values, I get garbage. Trying to set either latin1, iso-8859-1, or utf-8 as character set for the excel file also yields garbage.

Somehow, I have to translate the encoded unicode strings (by Python) into something that at least works in PHP, which in the next step can be put into an excel file and still be readable.

And then, by accident, I stumbled upon the solution. There is a function in PHP called utf8_decode. It's purpose is to convert a iso-8859-1 encoded unicode-string into single-byte iso-8859-1. Which happens to be exactly what I was looking for.

It took me a couple of hours of googling and experimenting before I ended up there. Now that I know the solution the whole thing is of course obvious. But if you make the same mistakes as me, you may like to read this, and save a few hours.

The right solution, of course, is to use utf-8 all the way. If the unicode data have been stored in unicode mysql tables as unicode, the whole thing should have worked right out of the box. So doing that may save you from the whole problem. Good luck!

On SQLObject

I have recently used SQLObject in a few projects. My first encounter of SQLObject was as a component of TurboGears, a framework for creating web sites. But I soon realized that SQLObject can be quite handy on its own.

What is SQLObject then, really. I don't want to do a full tutorial on SQLObject, so please head over to the SQLObject website to familiarize yourself with it. In short, it is a object oriented wrapper to SQL databases, for Python. You can freely choose among several different backends, like SQLite, Mysql, Postgres, and so forth, and most of the details of the particular backend you use will be hidden within SQLObject. If you want to create a table with SQLObject you simply say

class MyTable(SQLObject):
name = UnicodeCol()
date = DateCol()

(assuming you have done from SQLObject import *, which you of course have not). SQLObject takes care of all the SQL woodoo, and you can go ahead saying things like

mt = MyTable(name="someday",date="2007-03-28")

To get rid of the entry again, you just say

mt.destroySelf()

In addition to get rid of the object within your Python code, this also removes the corresponding row in the table on the sql server.

So far all good, and quite stright forward. Next, you want one-to-many relations, or maybe many-to-many relations. No problem, SQLObject can handle those as well, using MultipleJoin or RelatedJoin, together with ForeignKey. Assume that you wants a many-to-many relation between the MyTable above, and some other table:

class FooTable(SQLObject):
 name = UnicodeCol(alternateID=True, length=50)
 mytables = RelatedJoin('MyTable')

To get symmetric behaviour, you also wants

  foos = RelatedJoin('FooTable')

inside MyTable. With this setting, you can still use destroySelf(), and SQLObject figures out the right thing. So, that was many-to-many relations.

Now, I had a one-to-many relation in my code, like this:

class Foo(SQLObject):
 name = UnicodeCol(alternateID=True, length=30)
 mybar = ForeignKey('Bar')

class Bar(SQLObject):
 name = UnicodeCol(alternateID=True, length=30)
 foos = MultipleJoin('Foo')

So, this is a one-to-many relation, as Bar may have many Foo's, but Foo only link to one Bar. Then I did something like:

b = Bar.byName('mybar')
b.destroySelf()

Only to discover that I got errors next time I tried to do anything with the Foo's. Reason was that there was Foo's still linked to the Bar I destroyed. Thinking about it, this make perfectly sense. It also shows that SQLObject is not perfect in some sense. It does what you tell it to do, even when that is evil. This can be both good and bad, depending on how you look at it. Fortunately, the fix is quite easy:

if len(b.foos) > 0:
 print "There are still Foo's linked to this Bar"

Then of course more lines, if you want to deal with it in code - like relinking the Foos to other Bars, or just leave the user with that message - probably followed by some raise to signal an exception.

If you go out there search the blogs, you will find people telling you that SQLObject is not good. They are probably right. But to me, it felt just right from the very beginning. When I dig deeper into it, I will probably complain too. But so far I am happy. Nevertheless, I see that something called SQLAlchemy is mentioned together with turbogears now, and I guess SQLAlchemy is a replacement for SQLObject. I have to check that out, maybe it is even better. Until then, I stick with SQLObject, at least for non-turbogears apps, because I think it does its jobb very well.

Take care folks, and keep that Python code flowing from your hands.

Python, flickr, and Unicode

I found a script the other day, for generating a wallpaper based on images from flickr. In its original form, the script expected one or more tags as argument and get a number of random pictures from flickr that have these tags. I wanted to make it a bit more interesting, so I looked at API at flickr to see what is available, and discovered the getHotList method. I added this method to the python wrapper of the flickr api I am using (one of those listed under Python at the site), and changed the logic slightly to fetch the 3 most popular tags during the last day if no tags are given as argument.

And now comes the interesting thing. The getHotList gives you tags in unicode, looking like u'mus\xe9edelelys\xe9e' (one of the popular tags today). But when this tag is fed into flickr.photos.search to retrive urls for some images, an exception is thrown, as the method can not use this tag format. More or less obviously, it needs to be on an urlencoded form, as we are communicating through http. Reading documentation here and there, I figured out that unicode was expected, but more on an ascii form. The tag mentioned here should look like 'mus%C3%A9edelelys%C3%A9e'. After quite a few minutes with google, I figured out a possible solution:

tag = urllib.quote(tag.encode('utf-8'))

Looks kind of funny to me, but it works.

Lesson learned: Unicode is not unicode is not unicode.

Do I want the iPhone?

I will not add much to all the buzz about Apple's iphone, there is already way
to much said about it as it's still months out in the future. For us living in
Norway, I guess it will take almost a year before we can get our hands on it,
unfortunately.

What I will say is: First, I want one! Second, as it is clear now that the
operating system is MacOSX, I really hope that Apple is clever enough to add a
terminal, a bash shell and a ssh client as optional install items on a dvd that
will follow the phone. That will make it so much more interesting! If we even
can get a python interpreter and some GUI toolkit to do programming right on
the phone, we will be happy!