i18n translation hybrid in the context of web server

[SavinaRoja]

Hi there, looking for some guidance on this subject, as I am trying to implement a feature to enable translation of database values in a pyramid web application. The demonstration of translation via postgresql hstore in i18n.py here is intriguing and I was hoping to draw some inspiration.

In the context of my application, I cannot make any assumptions about the locales to be used, so my models themselves must be locale-agnostic. Consider the following model code:

from sqlalchemy.dialects.postgresql import HSTORE

from .meta import Base, translation_hybridize

class Animal(Base):
    __tablename__ = 'animal'
    id = Column(Integer, primary_key=True)
    name_translations = Column(HSTORE)

    # The following produces a callable that accepts a locale string which one might
    # use like so: animal.name('es_MX')
    # The result of this call produces a sqlalchemy hybrid_property with a
    # getter, setter, and expression that uses the supplied locale string
    # As a bonus, the getter and expression support generic fallback, so if
    # 'es_MX' does not exist, but 'es' does, then you will get 'es'
    # At this time, a final fallback to 'en' is used
    name = translation_hybridize(name_translations)

Since this is a web application, the selection of the locale is quite dynamic: checking the accept-language header, cookie, request param, etc. Details aside, the locale is set as a property on a request object which is passed to a request handler. So the possible view code for this might look like:

from app.models import Animal

@view_config(
    route_name='list_animals',
    renderer='../templates/list-animals.jinja2',
)
def list_animals(request):
    # Get a list of animal names, using locale for the present request sender
    animal_names = (
        request.dbsession.query(
            Animal.name(request.locale)
        ).all()
    )
    # this dict gets passed to rendering template
    return {'animal_names': animal_names}

I have been trying to prototype this and have some limited success. Here is the present code to implement translation_hybridize:

from sqlalchemy.ext.hybrid import hybrid_property

def translation_hybridize(column):

    def getter():
        generic = None
        if '_' in getter.locale:
            generic, _region = getter.locale.split('_')

        try:
            locale_val = getattr(getter.obj, column.key)[getter.locale]
        except KeyError:
            pass
        else:
            return locale_val

        if generic is not None:
            try:
                generic_val = getattr(getter.obj, column.key)[generic]
            except KeyError:
                pass
            else:
                return generic_val

        try:
            fallback_val = getattr(getter.obj, column.key)['en']
        except KeyError:
            return None
        else:
            return fallback_val

    def setter(value):

        if getattr(setter.obj, column.key) is None:
            setattr(setter.obj, column.key, {})
        getattr(setter.obj, column.key)[setter.locale] = value

    def expr():
        cls_column = getattr(expr.cls, column.key)
        generic = None
        if '_' in expr.locale:
            generic, _region = expr.locale.split('_')

        if generic is None:
            return func.coalesce(
                cls_column[expr.locale],
                cls_column['en'],
            )
        else:
            return func.coalesce(
                cls_column[expr.locale],
                cls_column[generic],
                cls_column['en'],
            )

    def wrapper(obj, locale):
        getter.locale = locale
        getter.obj = obj
        setter.locale = locale
        setter.obj = obj
        expr.locale = locale
        expr.cls = obj
        return hybrid_property(
            fget=getter,
            fset=setter,
            expr=expr,
        )

    return wrapper

One problem with the code at this time is that I must use explicit calls to fget, expr, and fset. Consider the following script:

from app.utils import get_session
from app.models import Animal

s = get_session()  # This is a session with the postgres database

# Create a few animal records with some translations
cat = Animal()
cat.name_translations = {'en': 'cat', 'es': 'gato', 'fr':' chat'}
dog = Animal()
dog.name_translations = {'en': 'dog', 'es': 'perro', 'fr':'chien'}
snake = Animal()
snake.name_translations = {'en': 'snake', 'es': 'serpiente', 'fr':'serpent'}
s.add(cat)
s.add(dog)
s.add(snake)
s.commit()

# One must use explicit fget
animals = s.query(Animal).all()
for animal in animals:
    # prints like <sqlalchemy.ext.hybrid.hybrid_property object at 0x7f4820140220>
    print(animal.name('es_MX'))
    # prints like 'gato', 'perro', 'serpiente'
    print(animal.name('es_MX').fget())

# One must use explicit expr
cat_lookup = (
    s.query(Animal).filter(Animal.name(Animal, 'es_MX') == 'gato').one()
)
if cat_lookup is not None:
    print(cat_lookup.id)  # if this was first initialization, it will print '1'

I am also presently still troubleshooting why the setter does not appear to work.

# This does not seem to work
cat.name('fi').fset('kissa')
s.add(cat)
s.commit()

Any guidance on the overall formulation and syntax of this would be great to avoid the explicit calls would be great. I would be happy to contribute anything further to the project if it might be helpful to others.

[SavinaRoja]

Figured out the issue with the setter, SQLAlchemy was not picking up the changes without this alteration to the model:

from sqlalchemy.ext.mutable import MutableDict

...
class Animal(Base):
...
    # name_translations = Column(HSTORE)  # not this
    name_translations = Column(MutableDict.as_mutable(HSTORE))  # instead this
...