Joining two KeyedTuple objects in SQLAlchemy

I have the following two queries:

res=session.query(t_marketing_mailing_stats_tbl).filter(
    t_marketing_mailing_stats_tbl.c.mailing_id==mid)

res_t=session.query(t_marketing_time_stat_tbl).filter(
    t_marketing_time_stat_tbl.c.mailing_id==mid)

try:
    return res.one() + res_t.one()
except NoResultFound as e:
    return None

res and res_t are both of type sqlalchemy.util._collections.KeyedTuple which support addition, however, the resulting object is a simple tuple, without keys.

I would like to have a combined result, so I did the following:

rv = {}
res=session.query(t_marketing_mailing_stats_tbl).filter(
    t_marketing_mailing_stats_tbl.c.mailing_id==mid)

res_t=session.query(t_marketing_time_stat_tbl).filter(
    t_marketing_time_stat_tbl.c.mailing_id==mid)

try:
    res =res.one()
    res_t = res_t.one()
    for k in res.keys():
        rv[k] = getattr(res, k)
    for k in res_t.keys():
        rv[k] = getattr(res_t, k)
except NoResultFound as e:
    return None

While this works, I suspect there is a built-in way, or a better way of achieving this with SQLAlchemy.

Your basic problem is you want to combine...erm, join... similar data from your database. There are a couple different angles we can take to solve this problem:

• We could do as you are, but a more simple way.

• We can let the database do the work.

The second way is the preferred solution. Databases are designed to solve this problem and they solve them very efficiently. They are meant to be a powerful tool to store, update, and eventually relate data.

If you are set on the first solution, this post describes several ways to combine two dicts. Combine any one of those solutions with the KeyedTuple._asdict function can simplify your code:

try:
    # In Python 3, you cannot use the + operator. You can use the | operator instead.
    joined_result = dict(res.one()._asdict().items() + res_t.one()._asdict().items())
except NoResultFound:
    return None

Now, the preferred solution would be to use a JOIN to combine the two queries into one. SQLAlchemy's join documentation has a quite a few examples of how join can be used. The basic example should suffice for your purposes:

joined_result = session.query(t_marketing_mailing_stats_tbl)\
                       .join(t_marketing_time_stat_tbl)\
                       .filter(t_marketing_mailing_stats_tbl.c.mailing_id==mid)

try:
    return joined_result.one()
except NoResultFound:
    return None

To wrap up, I have a few style comments:

• Be consistent with your spacing. For the most part you have a single space before and after operators. This is good. However, in a few locations, you have one or both of the spaces missing.

-
Don't truncate words in variable names (unless they are very long). Typical Python convention tries to be more verbose than brief. Thus the variable name res would better named as result.

Also, make sure that your names are unambiguous: what does res_t do different than res? What does the prefix t_ mean in your table names mean? Given the context, t_ could easily mean 'table'; however, wouldn't the suffix _tbl then be redundant?

StackExchange Code Review Q#55769, answer score: 2