concatenating columns at query level in sqlalchemy

Question

Is it possible to combine two columns when querying in sqlalchemy?

For example, I have following table:

table 1                      table 2
col1   col2   col3           col1   col2  col3
A       123     1             ABC    A1B2
B       456     2             DEF    C3D4
C       789     3             GHI    E5F6

I would like my query to look something like this:

 session.query(table.col3, (table1.col2 + table2.col2)).join(table2)

And would like the following results:

(1, 123A1B2)
(2, 456C3D4)
(3, 789E5F6)

How would I go about doing that?

Antti Haapala · Accepted Answer · 2015-10-09 07:33:42Z

9

It of course depends on db, and types; if the table1.col2 is a number, then you might want to try:

from sqlalchemy.sql import expression, functions
from sqlalchemy import types

... functions.concat(
    expressions.cast(table1.col2, types.Unicode), table2.col2) ...

Normally, + operator should work for 2 text columns.

edited Oct 9, 2015 at 7:33

answered Jun 24, 2013 at 5:13

Antti Haapala

135k23 gold badges297 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JoeMicro Over a year ago

i am using sqlite, table1.col2 is an int, table2.col2 is actually a date, but i want it to be a string, i just wanted to use it as a relationship unique id, in my query. but actually i realized that's not the way to go with what i am trying to do.. but never the less i was wondering about how to accomplish that, because in raw SQL it wouldn't be difficult. i would just do tbl1.col2 & tbl.col2 but in sqlalcehmy it returns TRUE. not sure why.

Antti Haapala Over a year ago

it returns true because & in sqlalchemy is the boolean and operator

Franco GIL Over a year ago

Where is the documentation from this answer?

Collectives™ on Stack Overflow

concatenating columns at query level in sqlalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related