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…