Can you declaratively concatenate two columns

[Royce]

Hi does anyone know if is possible to declaratively concatenate two
columns together which you can later do query's on.

E.g. if I wanted to compute a new column course_name made up of
CONCAT(course_code,course_name)

Base = declarative_base()
class Course(Base):
__tablename__ = 'Course'

course_code = Column(VARCHAR(length=4), nullable=False)
course_num = Column(INTEGER(), nullable=False)

course_name = func.CONCAT(course_code,course_num) # only an
example, this doesn't actually work


So later you could do queries on the Course table like

course_data =
session.query(Course).filter( Course.course_name.op('regexp')
('^A.*4') )).first()
print course_data.course_name


It is possible to do a query to generate the data outside the Course
class as below, but how can you
make it as a normal mapped column in the Course class ?

query = session.query( func.CONCAT(Course.course_code,
Course.course_num) )
query = query.filter( func.CONCAT(Course.course_code,
Course.course_num).op('regexp')('^A.*4') )

Cheers
Royce

[Lenza McElrath]

Hey Royce,

This sounds like a job for composite columns: http://www.sqlalchemy.org/docs/orm/mapper_config.html#composite-column-types

One gotcha that I ran into here is that you cannot have both the component columns and the composite column mapped at the same time, like you do in your example.  So depending on what you are trying to do, you might need to make a comparable property instead: http://www.sqlalchemy.org/docs/orm/mapper_config.html#custom-comparators

Let me know if you need any more help.

  -Lenza

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Jan 25, 2011, at 1:17 AM, Lenza McElrath wrote:

Hey Royce,

This sounds like a job for composite columns: http://www.sqlalchemy.org/docs/orm/mapper_config.html#composite-column-types

One gotcha that I ran into here is that you cannot have both the component columns and the composite column mapped at the same time, like you do in your example.  So depending on what you are trying to do, you might need to make a comparable property instead: http://www.sqlalchemy.org/docs/orm/mapper_config.html#custom-comparators

Let me know if you need any more help.

The original example with "course_name = func.CONCAT" was almost correct - its just that declarative needs more of a hint than that.

To map a SQL expression you want to use column_property():

http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes

http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#declarative-sql-expressions

composite() is not quite what you want here, though in 0.7 it will no longer conceal the underlying columns it uses.     A major reason not to use composite() before 0.7 though is that it will flip the "mutable" flag on the parent object, which pretty much kills performance for large numbers of objects.  We're doing away with this method of handling in-place mutability in 0.7.

[Royce]

Ok, Thanks for all your help ,I have found a solution as shown below

class Course(Base):
__tablename__ = 'Course'

course_id = Column(INTEGER())
course_code = Column(VARCHAR(length=4))

The line below works.
course_name =
column_property(select([func.CONCAT(course_code,course_num)]))

How ever this next line doesn't work even thought the docs seem to
imply that it should work.
course_name = column_property(course_code + course_num)

Cheers
Royce

[Michael Bayer]

column_property(cast(course_code, String) + course_num) would work, or just column_property(func.concat(...)). the select() shouldn't be needed.