SQLAlchemy Hybrid Attributes used for Internationalization


Notice: Trying to access array offset on value of type bool in /var/www/clients/client14/web43/web/wp-content/plugins/wp-word-count/public/class-wpwc-public.php on line 123

Recently I had to solve a problem of translation strings storing. You probably know this kind of job very well… The customer needs a feauture which provides a content language switching. The question is how can we solve this problem with and without SQLAlchemy?

Solution without SQLAlchemy

Consider the example database schema (PostgreSQL) below.

CREATE TABLE articles (
  id_article SERIAL PRIMARY KEY,
  published  BOOLEAN NOT NULL DEFAULT FALSE,
  author     VARCHAR(50) NOT NULL DEFAULT ''
);
  
CREATE TABLE articles_translations (
  id_article INTEGER REFERENCES articles ON DELETE CASCADE,
  language   CHAR(2) DEFAULT 'cs',
  content    VARCHAR NOT NULL DEFAULT '',
  PRIMARY KEY(id_article, language)
);

If we would like to get a translation of certain article ID, we need to execute a query somewhere in our code like this:

result = cur.execute('''
    SELECT a.*, a_t.content FROM articles a 
    LEFT JOIN articles_translations a_t 
        ON a_t.id_article=a.id_article AND a_t.language=%s 
    WHERE a.id_article=%s 
    ''', (user.locale, 69)
)

That’s it. It’s simple, fast and it works quite fine… for the time being. But what about update queries? What about ORM? Sooner or later (probably sooner than later), we’ll find out that it isn’t a perspective solution at all. Let’s have a look at another one – SQLAlchemy style solution!

Solution with SQLAlchemy

Ok, so how can we approach to the problem a good result? We’ll just use the hybrid properties and declarative base. That’s all. Look at this code:

from sqlalchemy import create_engine, MetaData Column, \
    Integer, String, Boolean, CHAR, ForeignKey
from sqlalchemy.orm import scoped_session, sessionmaker, Session
from sqlalchemy.ext.declarative import declarative_base
 
# Some kind of user object which provides locale attribute
from somepackage import user
 
engine = create_engine('postgresql://...')
metadata = MetaData(bind=engine)
session = scoped_session(sessionmaker(bind=self.engine)) 
Base = declarative_base(metadata=self.metadata)
  
class Articles(Base):
    __tablename__ = 'articles'
      
    id_article = Column(Integer, primary_key=True, autoincrement=True)
    published = Column(Boolean, nullable=False, default=False)
    author = Column(String(50), nullable=False, default='')
      
    @hybrid_property
    def content(self):
        # you can use sqlalchemy query instead this pure sql also
        return engine.execute(
            'SELECT content FROM articles_translations WHERE id_article=%s AND language=%s',
            self.id_article, user.locale
        ).scalar()
  
    @content.setter
    def content(self, value):
        engine.execute(
            'UPDATE articles_translations SET content=%s WHERE id_article=%s AND language=%s',
            value, self.id_article, user.locale
        )
   
    @content.expression
    def content(self):
        return Session().query(ArticlesTranslation.content).filter(
                ArticlesTranslation.id_article=self.id_article,
                ArticlesTranslation.language=user.locale).as_scalar()
  
  
class ArticlesTranslation(Base):
    __tablename__ = 'articles_translations'
      
    id_article = Column(Integer, ForeignKey(Articles.id_article), primary_key=True)
    language = Column(CHAR(2), nullable=False, default='cs', primary_key=True)
    content = Column(String, nullable=False, default='')
  
article = session.query(Article).get(69) 
# now we can easily access the content of this article (through @hybrid_property)
print article.content 
# modification of content:
article.content = 'New content!'
session.commit() # commit uses content.setter decorator

Wait, I can’t see a reason for implementation of expression decorator. So, why have we declared it? There’s an explanation:

articles_by_dj = Articles.query.filter_by(author='DJ').\
    order_by(Articles.content).all() 
# ...

It’s pretty neat, isn’t it? 🙂 SQLAlchemy does a lot of dirty job for us. In this case it’ll make a subquery according to the expression that we made in the decorated function. Finally, we’ll get all the articles ordered by their content. It’s time for a coffee…

Sdílet na sociálních sítích