On Fri, Jun 20, 2014 at 1:23 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
> On 6/20/14, 3:38 PM, Ken Lareau wrote:
>
> So in the ongoing improvement of one of our internal databases, we
> created
> a new table named 'environments' whose SQLA code looks something like
> this:
>
> class Environment(Base):
> __tablename__ = 'environments'
>
> id = Column(u'environmentID', INTEGER(), primary_key=True)
> environment = Column(String(length=15), nullable=False, unique=True)
> env = Column(String(length=12), nullable=False, unique=True)
> domain = Column(String(length=32), nullable=False, unique=True)
> prefix = Column(String(length=1), nullable=False)
>
> Two of our tables recently needed conversion to stop using their own
> local
> 'environment' column to using this table. The first part's been put in
> place
> (a new foreign key for 'environment_id'), but to prevent large swaths of
> code
> from needing changes, a thought of using a hybrid property might allow
> the
> change to be hidden (until the code using it had been rewritten at
> least).
>
> My naive attempt was the following (just the relevant snippet):
>
> environment_obj = relationship('Environment')
>
> @hybrid_property
> def environment(self):
> return self.environment_obj.environment
>
> Unfortunately (and in hindsight for obvious reasons), this code doesn't
> work,
>
> what does "doesn't work" mean here? This will work at the instance
> level. At the query level, not so much, that's true, if you truly want no
> code to change you'd need to implement an @expression here that's a little
> inefficient, as it needs to do a correlated subq:
>
>
Yes, the "doesn't work" was specifically related to an attempt to use it in
a query, which of course failed miserably. :)
> class HasEnv(Base):
> __tablename__ = 'has_env'
>
> id = Column(INTEGER, primary_key=True)
> environment_id = Column(ForeignKey('environments.environmentID'))
>
>
> environment_obj = relationship('Environment')
>
> @hybrid_property
> def environment(self):
> return self.environment_obj.environment
>
> @environment.expression
> def environment(cls):
> return select([Environment.environment]).\
> where(Environment.id ==
> cls.environment_id).correlate(cls).\
> as_scalar()
>
>
> s = Session()
>
> print s.query(HasEnv).filter(HasEnv.environment == 'some env')
>
> output:
>
> SELECT has_env.id AS has_env_id, has_env.environment_id AS
> has_env_environment_id
> FROM has_env
> WHERE (SELECT environments.environment
> FROM environments
> WHERE environments."environmentID" = has_env.environment_id) = :param_1
>
> wont perform well from a SQL perspective but will do the job...
>
>
>
> This worked perfectly, thank you! This is honestly a "stop-gap" measure
to allow much of the code to be rewritten (after which it can be removed),
and for what it's being used for at the moment, it won't be too bad
regarding
performance.
>
> but a very brief conversation with someone on the #sqlalchemy channel
> on
> Freenode indicated there was no way to do this and all the relevant code
> must be reworked. While it's only a few dozen places this occurs, I can
> see
> this coming up again in the future as further schema refactorings occur, so
> I turn to those with more expertise to find out if there is a way to
> accomplish
> what I desire, or if there's really no hope. :) Any insight would be
> greatly
> appreciated.
>
> I don't know how to fix this issue with IRC and stackoverflow that people
> constantly are getting bad information.
>
> Heh, Sometimes I think I should know better about asking for help
on IRC, but sometimes I get lucky. In this case, I decided I might
have better luck on the mailing list after the answer I got on IRC
was very unsatisfactory. :)
Thanks for the help!
--
- Ken Lareau
--
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.
