Thursday, December 27, 2007

Django and MySql character set tip

A couple of tips. The first one is from Leah Culver via her blog:

Python lesson I learned today - use “is None” when you want to check if something exists.
[...]

Here’s where it hurt:

def get_notes(qs=None):
if not qs:
qs = self.notes.all()
[...]

The correct condition is “if qs is None” like so:

def get_notes(qs=None):
if qs is None: # no predefined queryset
qs = self.notes.all()



Next one is all mine:

Our MySql implementation was set to default to character set latin1 and collation latin1_swedish_ci. No big deal, says I, I'll change that globally to utf8 and utf8_general_ci so we can handle non-latin charsets like cyrillic and greek.

But no go - when non-latin charactersets were used, the inserts to the database would fail. What the heck? Well.. the problem was that I created the tables before I changed the global default values from latin1 to utf8. The tables still had the old default character sets. Easy enough, just alter the table to use default charset utf8.

Nope. So even though the tables' properties would show ENGINE=InnoDB DEFAULT CHARSET=utf8 some of the text columns would still had a default charset of latin1.

Eventually, I did this and now it all works fine:

ALTER TABLE tablename DEFAULT CHARSET=utf8;
ALTER TABLE tablename CHANGE col1 col1 varchar(128) character set utf8 NOT NULL;
ALTER TABLE tablename CHANGE col2 col2 varchar(1024) character set utf8 NOT NULL;


I'm sure I'M DOING IT ALL WRONG, so make a comment if you just can't hold back.

1 comment:

  1. actually, imho, mysql can't be done right :)
    nice trick you pulled there ^^

    greetings from rainy vienna

    ReplyDelete