a self contained version of "before" is attached, seems to work as is
(works in 0.8 too). Modify it to show me it not working.
output:
SELECT t_ordered.pkg_name AS t_ordered_pkg_name, t_ordered.version AS
t_ordered_version, t_ordered.revision AS t_ordered_revision,
t_ordered."appType" AS "t_ordered_appType", t_ordered.environment_id AS
t_ordered_environment_id, t_ordered.realized AS t_ordered_realized,
t_ordered."user" AS t_ordered_user, t_ordered.status AS t_ordered_status
FROM (SELECT packages.pkg_name AS pkg_name, packages.version AS version,
packages.revision AS revision, app_definitions."appType" AS "appType",
app_deployments.environment_id AS environment_id, (SELECT
environments.environment
FROM environments
WHERE environments."environmentID" = app_deployments.environment_id) AS
anon_1, app_deployments.realized AS realized, app_deployments."user" AS
"user", app_deployments.status AS status
FROM packages JOIN deployments ON packages.package_id =
deployments.package_id JOIN app_deployments ON
deployments."DeploymentID" = app_deployments."DeploymentID" JOIN
app_definitions ON app_definitions."AppID" = app_deployments."AppID"
WHERE app_deployments.status != :status_1 AND (SELECT
environments.environment
FROM environments
WHERE environments."environmentID" = app_deployments.environment_id) =
:param_1 ORDER BY app_deployments.realized DESC) AS t_ordered GROUP BY
t_ordered."appType", t_ordered.environment_id, t_ordered.pkg_name
HAVING t_ordered.status LIKE :status_2 AND
unix_timestamp(t_ordered.realized) < unix_timestamp(now()) -
:unix_timestamp_1
On 6/26/14, 7:41 PM, Ken Lareau wrote:
> On Thu, Jun 26, 2014 at 1:45 PM, Mike Bayer <[email protected]
> <mailto:[email protected]>> wrote:
>
> minimal mapping + the query against that mapping.
>
> Okay, for the other method I needed to change, I think I've put together
> complete enough examples and have attached the files. The 'before'
> file is what we have currently (and doesn't quite do the right thing as
> it's only using the environment_id), and the 'after' file is what
> seemingly
> worked after my coworker and I tried a different route to avoid having
> to use a subquery. One thing to note is there's a reciprocating rela-
> tionship added on the Deployment class to the Package class which
> initially caused a lovely stack overflow in Python until the joinedload
> options were added; this may be a bad idea, but it was the only way
> we knew how to get things working the way we needed. :)
>
> I tried to add all the imports as well in case there were any questions
> that might come from the code itself; hopefully I didn't miss any, but
> if there's still confusion, let me know.
>
> - Ken
>
>
>
>
> On 6/26/14, 4:11 PM, Ken Lareau wrote:
>> On Thu, Jun 26, 2014 at 5:57 AM, Mike Bayer
>> <[email protected] <mailto:[email protected]>> wrote:
>>
>>
>> On 6/25/14, 8:06 PM, Ken Lareau wrote:
>>> On Wed, Jun 25, 2014 at 6:28 AM, Mike Bayer
>>> <[email protected] <mailto:[email protected]>>
>>> wrote:
>>>
>>>
>>> On 6/25/14, 2:26 AM, Ken Lareau wrote:
>>>> On Tue, Jun 24, 2014 at 3:35 PM, Mike Bayer
>>>> <[email protected]
>>>> <mailto:[email protected]>> wrote:
>>>>
>>>>
>>>> On 6/23/14, 8:09 PM, Ken Lareau wrote:
>>>> >
>>>> > 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? :)
>>>> nothing is obviously wrong, assuming you are
>>>> calling "subquery()" on
>>>> subq at the end. It will be a select() construct
>>>> which will have a
>>>> column called ".environment" on the .c. collection
>>>> because
>>>> AppDeployment.environment is present. Can't say
>>>> why this attribute is
>>>> not here without full details.
>>>>
>>>>
>>>> Oops there were some additional filters I left out, but
>>>> this is added
>>>> before the final query:
>>>>
>>>> subq = (subq.order_by(AppDeployment.realized.desc())
>>>> .subquery(name='t_ordered'))
>>>>
>>>> Not sure why it isn't working, but today my coworker an
>>>> I massively
>>>> rewrote one of the other methods to avoid this same
>>>> issue, so maybe
>>>> we should try the same for this one... though it would
>>>> be nice to know
>>>> what I goofed here, but not sure what additional
>>>> information I can
>>>> give that would help offhand...
>>>
>>> what are the names that are actually present on subq.c
>>> ? "print list(subq.c)" should give an indication.
>>>
>>>
>>> Adding that in to the method gave this:
>>>
>>> >>> deploy.find_deployed_version('tdstest', 'development',
>>> version='9', apptier=True)
>>> [Column('pkg_name', String(length=255), table=<t_ordered>,
>>> nullable=False), Column('version', String(length=63),
>>> table=<t_ordered>, nullable=False), Column('revision',
>>> String(length=63), table=<t_ordered>, nullable=False),
>>> Column('appType', String(length=100), table=<t_ordered>,
>>> nullable=False), <sqlalchemy.sql.elements.ColumnClause at
>>> 0x1d20e10; %(30521360 anon)s>]
>>> Traceback (most recent call last):
>>> File "<stdin>", line 1, in <module>
>>> File
>>> "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py", line
>>> 237, 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
>> you need to put .label('environment') on that column before
>> it finds its way into subq. I dont have the mappings here to
>> review.
>>
>> for the next go around please just provide a one-file example
>> of the mapping plus the query, thanks.
>>
>> Do you desire the example to be fully functional, or just have
>> the relevant
>> mapping and query? We ran into the same issue with another query and
>> ended up completely reworking the code to avoid the subquery, but
>> it in-
>> volves quite a bit of various mappings and I'm uncertain if it's
>> the proper
>> way to move forward. I could try to post the before/after here,
>> but if it
>> needs to be able to to run standalone it may take me a bit of
>> time to get
>> it into a fully workable state (given how extensive it is).
>>
>>
>> --
>> - 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 [email protected]
>> <mailto:[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
>
> --
> - 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 [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.dialects.mysql import INTEGER, SMALLINT, TIMESTAMP
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
Base = declarative_base()
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
app_type = Column(u'appType', String(length=100), nullable=False)
status = Column(
Enum('active', 'inactive'),
nullable=False,
server_default='active'
)
app_deployments = relationship('AppDeployment')
class AppDeployment(Base):
__tablename__ = 'app_deployments'
id = Column(u'AppDeploymentID', INTEGER(), primary_key=True)
deployment_id = Column(
u'DeploymentID',
INTEGER(),
ForeignKey('deployments.DeploymentID', ondelete='cascade'),
nullable=False
)
app_id = Column(
u'AppID',
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
nullable=False
)
user = Column(String(length=32), nullable=False)
status = Column(
Enum(
'complete',
'incomplete',
'inprogress',
'invalidated',
'validated',
),
nullable=False
)
environment_id = Column(
u'environment_id',
INTEGER(),
ForeignKey('environments.environmentID', ondelete='cascade'),
nullable=False
)
realized = Column(
TIMESTAMP(),
nullable=False,
server_default=func.current_timestamp()
)
application = relationship('AppDefinition')
deployment = relationship('Deployment')
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()
class Deployment(Base):
__tablename__ = 'deployments'
id = Column(u'DeploymentID', INTEGER(), primary_key=True)
package_id = Column(
INTEGER(),
ForeignKey('packages.package_id', ondelete='cascade'),
nullable=False
)
app_deployments = relationship('AppDeployment')
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
class Package(Base):
__tablename__ = 'packages'
id = Column(u'package_id', INTEGER(), primary_key=True)
pkg_name = Column(String(length=255), nullable=False)
version = Column(String(length=63), nullable=False)
revision = Column(String(length=63), nullable=False)
deployments = relationship('Deployment')
Session = scoped_session(sessionmaker())
subq = (Session.query(Package.pkg_name, Package.version,
Package.revision, AppDefinition.app_type,
AppDeployment.environment_id,
AppDeployment.environment,
AppDeployment.realized, AppDeployment.user,
AppDeployment.status)
.join(Deployment)
.join(AppDeployment)
.join(AppDefinition)
.filter(AppDeployment.status != 'invalidated')
.filter(AppDeployment.environment == "some environment")
.order_by(AppDeployment.realized.desc())
.subquery(name='t_ordered'))
q = (Session.query(subq.c.pkg_name, subq.c.version, subq.c.revision,
subq.c.appType, subq.c.environment_id,
subq.c.realized, subq.c.user, subq.c.status)
.group_by(subq.c.appType, subq.c.environment_id,
subq.c.pkg_name)
.having(and_(subq.c.status.like('%complete'),
func.unix_timestamp(subq.c.realized) <
func.unix_timestamp(func.now()) -
datetime.timedelta(seconds=60)))
)
print q
