On 7/12/15 6:35 AM, Lele Gaifax wrote:
Hi all,
I'm writing a new application against an existing DB (too bad, it's MySQL :-[)
that may contain information in multiple languages. The original author
structured the tables like the following:
CREATE TABLE products (
ID integer,
CODE varchar(20),
/* "short" description */
IT varchar(100),
EN varchar(100),
FR varchar(100),
...
/* "long" description */
IT_NOTE varchar(500),
EN_NOTE varchar(500),
FR_NOTE varchar(500),
...
)
The user of the application has a "preferred language", that can come either
from his own "profile" or from an external source (for example, the browser
locale setting when the user is anonymous).
I would like to be able to express my SA queries with that in mind, so for
example end up with
SELECT id, code, it as description, it_note as summary
FROM products
when the "preferred language" is "IT" and with
SELECT id, code, en as description, en_note as summary
FROM products
when it is set to "EN".
For now, I settled to build my query using an helper function similar to the
following:
def localized_field(cols, field_pattern="{lang}"):
known_langs = ('it', 'en', ...)
lparam = sa.bindparam('lang')
return sa.case([(lparam == lang.upper(),
cols[field_pattern.format(lang=lang)])
for lang in known_langs],
else_='Unsupported user lang: '+lparam)
that lets me write
pc = products.c
query = sqlalchemy.select([pc.id,
localized_field(pc).label('description'),
localized_field(pc,
'{lang}_note').label('summary)])
Although it works, it is obviously suboptimal, in particular when I'm going to
further refine the query, for example adding a filter on one of those
fields...
So I wonder if there is any clever trick that SA could support to avoid that
"long" CASE statement for each field. Assuming for example I could "mark" in
some way those columns, I would have a function that, traversing the query
structure replaces all instances of marked fields with the right "LANG_xxx"
one.
So yes, to make this better you'd need to decide on a system to
transform the statement as given.
Additionally, you'd need to decide on the hook to use in order to send
the transformation instruction into the query and how that would
communicate with your outer application. I'm not sure if you're using
the ORM, there are some neat new hooks in 1.0 that could be used for
this, but let's assume you're doing pure Core.
In this particular case there are a lot of ways to go even if we're just
on straight Core. The custom SQL construct approach would possibly be
a good way to do this in fact, the construct at @compiles time would
swap in the correct column for the render.
Though the approach of traversing the query structure has the advantage
that your select() object would now be set up with the correct columns
ahead of time and also the "hook" is just a simple "run this function on
the statement", rather than adding clues to the execution context.
Since you asked for that, let's assume your hook is just a straight
transform function you're going to call on your select() before it is
executed. You might be aware that we have the whole "traversal"
system. While this system tends to be oriented towards the internals
of SQLAlchemy, where in the ORM it is used ubiquitously, it is pretty
simple to use in this case, where we can set up a "marker" expression
object and use that to indicate where the replacement should occur.
We can just use the '.en' field as the "marker" here, if that works for
you. Below I make use of column.shares_lineage() so that we can also
accommodate aliases of the products table.
from sqlalchemy import Table, Column, Integer, String, MetaData, select
from sqlalchemy import sql
m = MetaData()
products = Table(
'products',
m,
Column('id', Integer),
Column('en', String),
Column('it', String),
Column('fr', String),
Column('en_note', String),
Column('it_note', String),
Column('fr_note', String),
)
def replace_lang(stmt, lang):
def replace(obj):
if isinstance(obj, Column):
if obj.shares_lineage(products.c.en):
return obj.table.c[lang]
elif obj.shares_lineage(products.c.en_note):
return obj.table.c['%s_note' % lang]
return None
return sql.visitors.replacement_traverse(stmt, {}, replace)
pc = products.c
stmt = select([
pc.id, pc.en.label('description'), pc.en_note.label('summary')
])
print replace_lang(stmt, 'it')
pca = products.alias().c
stmt = select([
pca.en.label('description'), pca.en_note.label('summary')
])
print replace_lang(stmt, 'it')
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
