On Fri, Jun 20, 2014 at 3:46 PM, Ken Lareau <klar...@tagged.com> wrote:
> 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!
>
D'oh, seems like I spoke too soon; there's a specific instance where
this fails due to some subquery stuff I'm doing:
if apptier:
subq = (
Session.query(
Package.pkg_name,
Package.version,
Package.revision,
AppDefinition.app_type,
AppDeployment.environment
).join(Deployment)
.join(AppDeployment)
.join(AppDefinition)
.filter(Package.pkg_name == package_name)
.filter(AppDeployment.environment == env)
.filter(AppDeployment.status != 'invalidated'))
[...]
# The actual column name must be used in the subquery
# usage below; DB itself should be corrected
versions = (Session.query(subq.c.appType,
subq.c.version,
subq.c.revision)
.group_by(subq.c.appType, subq.c.environment)
.all())
The parts in the subquery work fine, but the access of the 'column'
in the final query leads to this:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py", line
234, in find_deployed_version
.group_by(subq.c.appType, subq.c.environment)
File
"/home/klareau/.virtualenvs/tds/lib/python2.6/site-packages/sqlalchemy/util/_collections.py",
line 174, in __getattr__
raise AttributeError(key)
AttributeError: environment
This is not completely surprising, but I'm uncertain as the best way
to fix this... 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.
