info: interesting hack, sort in IS_IN_DB lists with regard to current locale

[Mirek Zvolský]

... or to any other special order.

For me was creazy that I was not able to sort items (names) in IS_IN_DB lists alphabetically.

IS_IN_DB has lot of undocumented parameters but I think nothing can help.

Of course when running from Postgres, you will receive records with proper locale order.

However with SQLite you need (maybe) a hacked and special compiled SQLite to have correct order.

Otherwise you start with uppercase ascii, then follow lowercase ascii, and at the end are accented (non-ascii) characters. Crazy.

Now I use this hack in my model:

import locale
locale.setlocale(locale.LC_ALL, 'cs_CZ.UTF-8')   # in multi-languages environment use PyICU instead (thx Niphlod)


class IS_IN_DB_(IS_IN_DB):
    def build_set(self):
        super(IS_IN_DB_, self).build_set()
        records = [(lbl, self.theset[pos]) for pos, lbl in enumerate(self.labels)]
        records.sort(key=lambda x: locale.strxfrm(x[0]))
        self.labels = [rec[0] for rec in records]
        self.theset = [rec[1] for rec in records]


db.define_table('payment',
    Field('idauth_user', 'reference auth_user', requires=IS_EMPTY_OR(IS_IN_DB_(db, db.auth_user.id, '%(nick)s'))),
    ...)

Not optimal, but works !

[Anthony]

Does IS_IN_DB(sort=True) not do what you want? If not, it might be a bit simpler to instead override the options() method of IS_IN_DB and just provide an alternative function in place of options_sorter() (or, if you want an alternative options_sorter in all cases, you could just monkey patch it in gluon.validators).

Anthony

On Friday, February 10, 2017 at 12:08:58 PM UTC-5, Mirek Zvolský wrote:

... or to any other special order.

For me was creazy that I was not able to sort items (names) in IS_IN_DB lists alphabetically.

IS_IN_DB has lot of undocumented parameters but I think nothing can help.

Of course when running from Postgres, you will receive records with proper locale order.

However with SQLite you need (maybe) a hacked and special compiled SQLite to have correct order.

Otherwise you start with uppercase ascii, then follow lowercase ascii, and at the end are accented (non-ascii) characters. Crazy.

Now I use this hack in my model:

import locale
locale.setlocale(locale.LC_ALL, 'cs_CZ.UTF-8')

class IS_IN_DB_(IS_IN_DB):
    def build_set(self):
        super(IS_IN_DB_, self).build_set()
        records = [(lbl, self.theset[pos]) for pos, lbl in enumerate(self.labels)]
        records.sort(key=lambda x: locale.strxfrm(x[0]))
        self.labels = [rec[0] for rec in records]
        self.theset = [rec[1] for rec in records]


db.define_table('payment',
    Field('idauth_user', 'reference auth_user', requires=IS_EMPTY_OR(IS_IN_DB_(db, db.auth_user.id, '%(nick)s'))),
    ...)

[Niphlod]

BTW: setlocale is not threadsafe.

[Mirek Zvolský]

Thanks, Hiphlod.

Does this mean that

1) I cannot use locale module at all,

2) I cannot switch more different locales/languages and I have to import and set locales at some module "root" level,

3) I cannot switch more different locales/languages, but I can set them in model (like I do) ?

I think 3) is True. Or 2) ?

On Monday, 13 February 2017 08:24:57 UTC+1, Niphlod wrote:

BTW: setlocale is not threadsafe.

[Mirek Zvolský]

Thanks, Anthony.

When I have some time maybe I will do more experiments.

With sort=True I was (earlier) not sucessful. Because I think I need a lambda to set the order, boolean is not enough.

Inside options() I had (earlier) the set_trace point but I wasn't successful to stop the execution there. Not sure if it is called in form preparing/rendering.

[Mirek Zvolský]

Thread-safe...

In doc I read following:

Applications typically start with a call of

import locale
locale.setlocale(locale.LC_ALL, '')

This sets the locale for all categories to the user’s default setting (typically specified in the LANG environment variable). If the locale is not changed thereafter, using multithreading should not cause problems.

So I think it is good for me in small application where I don't need switch languages.

And for enterprise application PyICU is the correct solution (which uses locale internal tables/data). I have improved the original post with the comment about PyICU.

[Mirek Zvolský]

# http://stackoverflow.com/questions/11121636/sorting-list-of-string-with-specific-locale-in-python

import icu   # PyICU

def sorted_strings(strings, locale=None):

    if locale is None:

       return sorted(strings)

    collator = icu.Collator.createInstance(icu.Locale(locale))

    return sorted(strings, key=collator.getSortKey)

[Anthony]

On Tuesday, February 14, 2017 at 4:47:49 AM UTC-5, Mirek Zvolský wrote:

Thanks, Anthony.

When I have some time maybe I will do more experiments.

With sort=True I was (earlier) not sucessful. Because I think I need a lambda to set the order, boolean is not enough.

Inside options() I had (earlier) the set_trace point but I wasn't successful to stop the execution there. Not sure if it is called in form preparing/rendering.

Yes, when SQLFORM generates widgets, ultimately the .options() method of the IS_IN_DB validator gets called, and that method then calls the .build_set() method (I don't think there is any code that directly calls .build_set()). So, you should be able to override .options() instead of .build_set().

Anthony