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:
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...
> 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.
--
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.
