Re: [sqlalchemy] Can you declaratively concatenate two columns
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.
>
> -Lenza
>
> On Mon, Jan 24, 2011 at 7:35 PM, Royce wrote:
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Can you declaratively concatenate two columns
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
On Mon, Jan 24, 2011 at 7:35 PM, Royce wrote:
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Can you declaratively concatenate two columns
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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
