Anyone? This has me dead in the water and nothing in the documentation
has been helping.
- Ken
On Mon, Jun 23, 2014 at 5:09 PM, Ken Lareau <klar...@tagged.com> wrote:
> 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
>
>
--
- 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.
